Is a SQL Constraint On Multiple Columns and Values Possible?

Question

I'm using SQL Server and have a table ProjectAccess which I'm hoping to use as a way to "lock" access to Projects. Currently the table looks like this:

CREATE TABLE [dbo].[ProjectAccess] 
(
    [ProjectAccessID] INT IDENTITY (1, 1) NOT NULL,
    [ProjectID] INT NOT NULL,
    [UserSessionID] INT NOT NULL,
    [Locked] BIT NOT NULL,

    CONSTRAINT [uq_UserSessionID] 
        UNIQUE NONCLUSTERED ([UserSessionID]),
    CONSTRAINT [fk_ProjectAccessToProject] 
        FOREIGN KEY ([ProjectID]) REFERENCES [dbo].[Project] ([ProjectID]) 
                ON DELETE CASCADE,
    CONSTRAINT [fk_ProjectAccessToUserSession] 
        FOREIGN KEY ([UserSessionID]) REFERENCES [dbo].[UserSession] ([UserSessionID]) 
                ON DELETE CASCADE, 
    CONSTRAINT [PK_ProjectAccess] 
        PRIMARY KEY ([ProjectAccessID])
);

The idea was to only allow for multiple of the same project ID's into this table, but only one could have the Locked column value as true. For example you could have two records with ProjectID 1 in the table at the same time as long as both did not have the Locked value set to true. So is there a way to do this? I see that there is a check constraint but that seems to check all values in a column satisfy a certain condition which is a little different than what I'm hoping for. Thank you in advance and let me know if anything isn't clear enough.

Whoever downvoted please explain why instead of being useless. — m4gik
– m4gik, Commented Dec 8, 2017 at 5:44

Gordon Linoff · Accepted Answer · 2017-12-08 01:33:59Z

2

You can use a filtered unique index:

create unique index unq_projectaccess_projectid_locked
    on projectaccess(projectid)
    where locked = 1;

answered Dec 8, 2017 at 1:33

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Is a SQL Constraint On Multiple Columns and Values Possible?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related