how to create unique constraints in sql server?

Question

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

marc_s · Accepted Answer · 2014-01-07 13:13:07Z

3

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.

answered Jan 7, 2014 at 13:13

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Salman Arshad · Accepted Answer · 2014-01-07 13:18:16Z

1

You can achieve this with a unique index:

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

answered Jan 7, 2014 at 13:18

Salman Arshad

274k85 gold badges450 silver badges540 bronze badges

Collectives™ on Stack Overflow

how to create unique constraints in sql server?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related