I have a table and somehow the same person got into my
Once you have removed your duplicate(s):
ALTER TABLE dbo.yourtablename ADD CONSTRAINT uq_yourtablename UNIQUE(column1, column2);
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).
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.