CODEWITHSUNDEEP
sqlsql-server
Farhad-Taran
Farhad-Taran

Reputation: 6512

how to create unique constraints in sql server?

I would like create a unique constraint which specifies that for an ID and BriefID there should be a Unique Name.

eg. the following would not be allowed because of the constraint.

ID   BriefID   Name
12    32        first
12    32        first

the table structure is as follows.

 PK ID                   int    
    Name                 nvarchar(50)   
 FK BriefID              int    
    StatusID             int    
    StartDate            datetimeoffset(7)  
    EndDate              datetime   
    FrequencyID          int    
    MaxArticleDisplay    int    
    LastDateAlertSent    datetime

Upvotes: 1

Views: 51

Answers (2)

Salman Arshad
Salman Arshad

Reputation: 272106

You can achieve this with a unique index:

CREATE UNIQUE NONCLUSTERED INDEX [UIX_TABLENAME_001] ON [TABLENAME]
(
    ID,
    BriefID,
    Name
)

Upvotes: 1

marc_s
marc_s

Reputation: 754488

How about

ALTER TABLE dbo.YourTable
ADD CONSTRAINT UC_ID_BriefID UNIQUE(ID, BriefID, Name)

With this, you could not insert these two rows you mentioned in your question. Trying to insert the second row would cause a constraint violation and would not succeed.

Upvotes: 3

Related Questions