Search code examples
sqlssmssql-functioncheck-constraints

Using Microsoft SQL Management Studio, make a constraint depend on a user-generated function

So there's a couple problems I'm running into regarding getting this to work. Here's the function:

CREATE FUNCTION RSO_Affiliation_AdminID_is_Admin_Enforcer

@Admin_ID nvarchar(50)


RETURNS INT
AS
BEGIN

DECLARE @ACCESS nvarchar(50)
SET @ACCESS = (SELECT ACCESS FROM Student WHERE USER_ID = @ADMIN_ID)

IF (@ACCESS LIKE 'Admin')
        RETURN 1;

IF (@ACCESS LIKE 'SuperAdmin')
        RETURN 1;

RETURN 0; 
END
GO

I'm pretty new to SQL and SQL server management studio, so if there's anything generally wrong with the syntax here, that'd be the primary problem, but as far as I've seen, this should work. Essentially, this should be called by a constraint for the RSO_Affiliation table with the new ADMIN_ID as a parameter, it should check it against the Student table, and return 1 if it turns out the Access level for the matching USER_ID is an admin of some kind, return 0 otherwise.

Problem 1 is that I can't seem to find this function in the Object Explorer. Despite saving it over and over, refreshing and reconnecting to the DB multiple times, its absent from all Function folders. It saved to the SQL Management Studio folder in My Documents, but as far as I know, my actual Database doesn't know it exists. I created it as a Scalar-valued Function by the way.

Problem 2 is that I don't know the syntax for what I should type into the RSO_Affiliation -> Check Constraints -> Expression dialog window. From what I've seen from other questions, 

dbo.RSO_Affiliation_AdminID_is_Admin_Enforcer(ADMIN_ID) = 1

should work, but it doesn't, probably because again, I don't think my database knows where to look for the function itself.

Any insights?

EDIT So I finally figured out that to get SMSS to add the function to the DB, you need to test it with the Execute button in the top-left hand corner of the toolbar. I was able to the use it in the constraints menu with the above code no problem, except for one:

It apparently doesn't do anything. Presumably, its called by the constraint with the pending ADMIN_ID, but I can use students with non-admin access levels to create new RSOs. Any ideas why this might be the case?

Solution

  • You can do what you want with foreign key constraints, assuming that user_id is unique in student. This saves the trouble of a user defined function, but the implementation has a couple of gotchas.

    First, you need a unique key on students to identify who is admin or super admin:

    alter table student add IsAdmin as (case when ACCESS in ('Admin', 'SuperAdmin') then 1 else 0 end);
create unique index unq_Student_IsAdmin on Student(user_id, IsAdmin);

    Then, create a foreign key constraint in the table you care about:

    alter table t add IsAdmin (1);
alter table t add constraint fk_t_student_isadmin
    foreign key (admin_id, IsAdmin) references student(user_id, IsAdmin);

    This then guarantees that that the admin_id in the table you are setting up is an admin.