Add unique constraint to combination of two columns

Question

I have a table and  somehow  the same person got into my Person table twice  Right now  the primary key is just an autonumber but there are two other fields that exist that I want to force to be unique   For example  the fields are   ID   Name   Active   PersonNumber     I only want 1 record with a unique PersonNumber and Active   1   So the combination of the two fields needs to be unique   What is the best way on an existing table in SQL server I can make it so if anyone else does an insert with the same value as an existing value  it fails so I don t have to worry about this in my application code

User · Answer

In my case  I needed to allow many inactives and only one combination of two keys active  like this   UUL USR IDF  UUL UND IDF    UUL ATUAL 137          18             0 137          19             0 137          20             1 137          21             0   This seems to work   CREATE UNIQUE NONCLUSTERED INDEX UQ USR UND UUL USR IDF UUL ATUAL ON USER UND UUL USR IDF  UUL ATUAL  WHERE UUL ATUAL   1    Here are my test cases   SELECT   FROM USER UND WHERE UUL USR IDF   137  insert into USER UND values  137  22  1  --I CAN NOT   gt  Cannot insert duplicate key row in object  dbo USER UND  with unique index  UQ USR UND UUL USR IDF UUL ATUAL   The duplicate key value is  137  1   insert into USER UND values  137  23  0  --I CAN insert into USER UND values  137  24  0  --I CAN  DELETE FROM USER UND WHERE UUL USR ID   137  insert into USER UND values  137  22  1  --I CAN insert into USER UND values  137  27  1  --I CAN NOT   gt  Cannot insert duplicate key row in object  dbo USER UND  with unique index  UQ USR UND UUL USR IDF UUL ATUAL   The duplicate key value is  137  1   insert into USER UND values  137  28  0  --I CAN insert into USER UND values  137  29  0  --I CAN

User · Answer

Once you have removed your duplicate s    ALTER TABLE dbo yourtablename   ADD CONSTRAINT uq yourtablename UNIQUE column1  column2     or  CREATE UNIQUE INDEX uq yourtablename   ON dbo yourtablename column1  column2     Of course  it can often be better to check for this violation first  before just letting SQL Server try to insert the row and returning an exception  exceptions are expensive    http   www sqlperformance com 2012 08 t-sql-queries error-handling  http   www mssqltips com sqlservertip 2632 checking-for-potential-constraint-violations-before-entering-sql-server-try-and-catch-logic   If you want to prevent exceptions from bubbling up to the application  without making changes to the application  you can use an INSTEAD OF trigger   CREATE TRIGGER dbo BlockDuplicatesYourTable  ON dbo YourTable  INSTEAD OF INSERT AS BEGIN   SET NOCOUNT ON     IF NOT EXISTS  SELECT 1 FROM inserted AS i      INNER JOIN dbo YourTable AS t     ON i column1   t column1     AND i column2   t column2       BEGIN     INSERT dbo YourTable column1  column2             SELECT column1  column2      FROM inserted    END   ELSE   BEGIN     PRINT  Did nothing      END END GO   But if you don t tell the user they didn t perform the insert  they re going to wonder why the data isn t there and no exception was reported     EDIT here is an example that does exactly what you re asking for  even using the same names as your question  and proves it  You should try it out before assuming the above ideas only treat one column or the other as opposed to the combination     USE tempdb  GO  CREATE TABLE dbo Person     ID INT IDENTITY 1 1  PRIMARY KEY    Name NVARCHAR 32     Active BIT    PersonNumber INT    GO  ALTER TABLE dbo Person    ADD CONSTRAINT uq Person UNIQUE PersonNumber  Active   GO  -- succeeds  INSERT dbo Person Name  Active  PersonNumber    VALUES N foo   1  22   GO  -- succeeds  INSERT dbo Person Name  Active  PersonNumber    VALUES N foo   0  22   GO  -- fails  INSERT dbo Person Name  Active  PersonNumber    VALUES N foo   1  22   GO   Data in the table after all of this   ID   Name   Active PersonNumber ---- ------ ------ ------------ 1    foo    1      22 2    foo    0      22   Error message on the last insert      Msg 2627  Level 14  State 1  Line 3   Violation of UNIQUE KEY constraint  uq Person   Cannot insert duplicate key in object  dbo Person     The statement has been terminated

User · Answer

And if you have lot insert queries but not wanna ger a ERROR message everytime   you can do it    CREATE UNIQUE NONCLUSTERED INDEX SK01 ON dbo Person ID Name Active PersonNumber   WITH IGNORE DUP KEY   ON

User · Answer

This can also be done in the GUI    Under the table  Person   right click Indexes Click hover New Index Click Non-Clustered Index         A default Index name will be given but you may want to change it   Check Unique checkbox  Click Add    button      Check the columns you want included      Click OK in each window

[sql] Add unique constraint to combination of two columns

Examples related to sql

Examples related to sql-server