SQL Server 学习笔记（主键和外键）

1.主键约束

常用的单主键表创建主键约束   

CREATE TABLE Persons
(
Id_P int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

需要为主键约束命名，并且需要定义多个列为主键约束

CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT uc_PersonID PRIMARY KEY (Id_P,LastName)
)

向已经存在的表中添加主键约束

ALTER TABLE Persons
ADD PRIMARY KEY (Id_P)

或

ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (Id_P,LastName)

撤销主键约束

ALTER TABLE Persons
DROP CONSTRAINT pk_PersonID

2.外键约束

创建表时创建外键约束

CREATE TABLE Orders
(
O_Id int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
Id_P int FOREIGN KEY REFERENCES Persons(Id_P)
)

需要命名外键约束或需要定义多列的外键约束

CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
PRIMARY KEY (O_Id),
CONSTRAINT fk_PerOrders FOREIGN KEY (Id_P)
REFERENCES Persons(Id_P)
)

在已经存在表中创建外键约束

ALTER TABLE Orders
ADD FOREIGN KEY (Id_P)
REFERENCES Persons(Id_P)

或

ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (Id_P)
REFERENCES Persons(Id_P)

撤销外键约束

ALTER TABLE Orders
DROP CONSTRAINT fk_PerOrders

其中在创建和撤销主外键时，MySQL和以上语法略有不同。整理自：http://www.w3school.com.cn/sql/sql_foreignkey.asp