Use unique constraints to prevent duplicate records
In database operations, it is often necessary to ensure the uniqueness of a specific column combination. For example, consider the following table:
<code>ID | Name | Active | PersonNumber</code>
You may need to enforce a unique constraint to prevent duplicates based on the combination of PersonNumber and Active = 1. This ensures that no two rows have the same PersonNumber and Active status.
Add unique constraint to existing table
To add a unique constraint to an existing table, you can use one of the following methods:
1. ALTER TABLE using unique constraints
<code>ALTER TABLE dbo.yourtablename ADD CONSTRAINT uq_yourtablename UNIQUE(column1, column2);</code>
2. Create a unique index
<code>CREATE UNIQUE INDEX uq_yourtablename ON dbo.yourtablename(column1, column2);</code>
Use triggers to prevent repeated insertions
If you wish to prevent repeated insertions without relying on exceptions, you can use the INSTEAD OF trigger. This trigger intercepts insertion attempts and only allows rows to be inserted if they do not violate the unique constraint:
<code class="language-sql">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 '未执行任何操作。'; -- 更友好的提示信息 END END; GO</code>
Example: Enforcing uniqueness on PersonNumber and Active
The following example shows how to add a unique constraint on the combination of PersonNumber and Active in the Person table:
<code class="language-sql">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</code>
Any insertion attempt that violates the unique constraint will now fail with an error message, ensuring there are no duplicate records with the same PersonNumber and Active status. The PRINT '未执行任何操作。'
statement replaces the PRINT 'Did nothing.'
in the original text, making it easier to understand.
The above is the detailed content of How Can I Prevent Duplicate Database Records Using Unique Constraints and Triggers?. For more information, please follow other related articles on the PHP Chinese website!