Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

like image 633
Thunder Avatar asked Feb 11 '11 11:02

Thunder


People also ask

Can unique key have multiple columns?

Unique key is a constraint in SQL Server. This constraint ensures that we cannot enter duplicate values into the columns. It means the data stored in the column is unique among the rows in the table. We can create a unique key on both single and multiple columns.

How do I create a unique constraint on multiple columns in MySQL?

The syntax for creating a unique constraint using an ALTER TABLE statement in MySQL is: ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (column1, column2, ... column_n); table_name.

How many columns can hold unique constraints?

You can create a UNIQUE constraint on one or more columns of a table.


1 Answers

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)
)
like image 196
Martin Smith Avatar answered Oct 03 '22 05:10

Martin Smith