CODEWITHSUNDEEP
sqlsql-serverconstraints
Thunder
Thunder

Reputation: 10986

How to place unique contraint on multiple column

EmpID DeptID

1     1
1     2
2     1
3     2
4     5
5     2
1     1   
2     1

I would like to have a constraint that will make sure that the pair of field is always unique ,such data as last two shown in the example should not be insert-able into the table .in the above table please note that last two rows are duplicates ,I would like to prevent such data from occuring . How do I achieve this in sqlserver 2005.Thanks

Upvotes: 20

Views: 17409

Answers (4)

Martin Smith
Martin Smith

Reputation: 452978

ALTER TABLE <YourTable, sysname, Emp> 
ADD CONSTRAINT <YourConstraintName, sysname, uix> 
UNIQUE NONCLUSTERED (EmpID,DeptID)

(Paste into SSMS and use (CTRL + Shift + M))

Or to do this at table creation and as it sounds as though there is no alternative key use.

CREATE TABLE EMPLOYEE_DEPARTMENT(
    EmpID int NOT NULL REFERENCES EMPLOYEE(EmpID),
    DeptID int NOT NULL REFERENCES DEPARTMENT(DeptID),
 CONSTRAINT PK_EMPLOYEE_DEPARTMENT PRIMARY KEY CLUSTERED (EmpID ASC,DeptID ASC)
)

Upvotes: 22

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239636

After you've gone through and removed the duplicates, run the following (substituting appropriate names)

ALTER TABLE table ADD CONSTRAINT UQ_EmpID_DeptID UNIQUE (EmpID,DeptID)

Or when creating your table:

CREATE TABLE T1 (
    EmpID int not null,
    DeptID int not null,
    /* Other Columns */
    constraint PK_T1 PRIMARY KEY (EmpID,DeptID)
)

(May as well make it the primary key, unless you've got another one in the table)

Upvotes: 10

Johann Blais
Johann Blais

Reputation: 9469

ALTER TABLE dbo.YOURTABLE ADD CONSTRAINT IX_YOURTABLE UNIQUE NONCLUSTERED (EmpID, DeptID)

Upvotes: 4

Sachin Shanbhag
Sachin Shanbhag

Reputation: 55489

select empID, deptID from table
group by empID, deptID

EDIT:

If you are saying this data must be unique in table itself, i.e. The insertion of duplicates should not be allowed, then you need to define a composite key (empID, deptID) on this table.

alter table <tablename> add constraint <compositekeyname> primary key (empID, deptID)

Upvotes: 1

Related Questions