如何在多列上放置独特的约束 [英] 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   

一个约束将确保字段对始终是唯一的，例如示例中最后两个数据的数据不应该可插入到表中。在上表
中，请注意最后两行是重复的，我想防止这种数据发生。
如何在sqlserver 2005中实现此功能。

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

推荐答案

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

（粘贴到SSMS并使用+ M））

(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)
)

这篇关于如何在多列上放置独特的约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！