外键约束和参绍约束的实例脚本

/*
外键约束和参绍约束
pubs数据库的CarIfo表创建外键约束，参绍CarFactory表的主键ID.
*/
USE Pubs
GO

/*不显示行数*/
SET NOCOUNT ON
GO
/*创建示例表*/
IF EXISTS (SELECT * FROM dbo.sysobjects 
WHERE id = object_id(N’CarInfo’) 
AND OBJECTPROPERTY(id, N’IsUserTable’) = 1)
DROP TABLE CarInfo
GO

CREATE TABLE CarInfo(
Car_ID CHAR (10) ,
Name CHAR (10) ,
Type CHAR (20),
Driver_Type CHAR (15)
)

IF EXISTS (SELECT * FROM dbo.sysobjects 
WHERE id = object_id(N’CarFactory’) 
AND OBJECTPROPERTY(id, N’IsUserTable’) = 1)
DROP TABLE CarFactory
GO

CREATE TABLE CarFactory(
ID CHAR (10) NOT NULL,
Name CHAR (20) ,
PostCode CHAR (6),
Telphone CHAR (15),
Address CHAR (30),
)
GO

ALTER TABLE CarFactory
ADD CONSTRAINT PK_Car_ID 
PRIMARY KEY NONCLUSTERED (ID)
GO

ALTER TABLE CarInfo
WITH NOCHECK
ADD CONSTRAINT Car_id_link 
FOREIGN KEY (Car_ID) REFERENCES CarFactory(ID)
GO

USE master
EXEC sp_dboption Pubs, ’dbo use only’, TRUE
GO

USE Pubs
SET NOCOUNT ON
GO