CODEWITHSUNDEEP
sqlsql-server
m4gik
m4gik

Reputation: 450

Is a SQL Constraint On Multiple Columns and Values Possible?

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.

Upvotes: 1

Views: 79

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

You can use a filtered unique index:

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

Upvotes: 2

Related Questions