--方法一:创建表时直接指定主键和外键 if object_id('tbChild') is not null drop table tbChild GO if object_id('tbMaster') is not null drop table tbMaster GO ----创建主键表 create table tbMaster(id int identity(1,1) PRIMARY KEY, name varchar(20) default 'xyz') ----创建外键表 create table tbChild(id int identity(1,1),pid int FOREIGN KEY REFERENCES tbMaster ( id ) ON DELETE CASCADE ON UPDATE CASCADE) GO ----查看 select * from tbMaster a left join tbChild b on a.id = b.pid ----清除测试环境 drop table tbChild,tbMaster GO -------------------------------------------------------------------------------- --方法二:先创建表,后添加主键或外键 ----创建测试表 create table tbMaster(id int identity(1,1), name varchar(20) default 'xyz') create table tbChild(id int identity(1,1),pid int) GO ----添加主键 alter table tbMaster add constraint [PK_tbMaster] PRIMARY KEY(id) GO ----添加外键 alter table tbChild add constraint [FK_tbChild_tbMaster] FOREIGN KEY (pid) REFERENCES tbMaster(id) ON DELETE CASCADE ON UPDATE CASCADE GO ----查看 select * from tbMaster a left join tbChild b on a.id = b.pid----清除测试环境 drop table tbChild,tbMaster GO
if object_id('tbChild') is not null
drop table tbChild
GO
if object_id('tbMaster') is not null
drop table tbMaster
GO
----创建主键表
create table tbMaster(id int identity(1,1) PRIMARY KEY,
name varchar(20) default 'xyz')
----创建外键表
create table tbChild(id int identity(1,1),pid int FOREIGN KEY
REFERENCES tbMaster ( id )
ON DELETE CASCADE
ON UPDATE CASCADE)
GO
----查看
select * from tbMaster a left join tbChild b on a.id = b.pid
----清除测试环境
drop table tbChild,tbMaster
GO
--------------------------------------------------------------------------------
--方法二:先创建表,后添加主键或外键
----创建测试表
create table tbMaster(id int identity(1,1),
name varchar(20) default 'xyz')
create table tbChild(id int identity(1,1),pid int)
GO
----添加主键
alter table tbMaster add constraint [PK_tbMaster] PRIMARY KEY(id)
GO
----添加外键
alter table tbChild add constraint [FK_tbChild_tbMaster]
FOREIGN KEY (pid)
REFERENCES tbMaster(id)
ON DELETE CASCADE ON UPDATE CASCADE
GO
----查看
select * from tbMaster a left join tbChild b on a.id = b.pid----清除测试环境
drop table tbChild,tbMaster
GO