資料完整性設定(PRIMARY KEY與 FOREIGN KEY)

這是書上的例子，忘記是哪本了，參考看看，把以下的陳述式copy過去，一段一段執行，應該會對資料完整性的設定有初步的認識喔

/*
SQL 2000 只有NO ACTION CASCADE
SQL 2005 有NO ACTION CASCADE SET NULL SET DEFAULT
本文不介紹SET DEFAULT，個人覺得不實用囉
*/

--刪除資料表
drop table Scores
drop table Students
--
create table Students
( stuID int NOT NULL primary key, --學生代號
stuName nvarchar(20) NOT NULL, --學生姓名
SID varchar(50) not null unique, --保險號碼
birthdate datetime check (birthdate < getdate()), --生日
Address nvarchar(50) null, --地址
stuLeader int references Students(stuID) --組長代號
)
GO
/*NO ACTION，建立外部索引鍵時沒有特別宣告的話，就是使用此選項囉，如同一般的外部
　索引鍵的建立，被參閱資料刪除或是修改都會引發錯誤訊息的*/
create table Scores
( stuID int not null
foreign key references Students(stuID), --學生代號
Period varchar(30) not null, --學期
stuScore numeric(5,2) default 0, --成績
keyInUSR nvarchar(50) default suser_sname() --建檔人員
primary key (stuID,Period)
)
GO

--新增三筆學生資料
insert into Students values(1, N'lewis', '009988', '1997/7/1', 'Taipei', null)
insert into Students values(2, N'ada', '006688', '1998/5/1', 'Taipei', null)
insert into Students values(3, N'julla', '005588', '1995/7/1', 'Taipei', null)
go
--新增兩筆學生成績資料
insert into Scores values(1, '200601', 90, default)
insert into Scores values(2, '200601', 80, default)
go
--刪除學生資料
delete from Students where stuID = 1
--結果
/*訊息 547，層級 16，狀態 0，行 1
DELETE 陳述式與 REFERENCE 條件約束 "FK__Scores__stuID__0425A276" 衝突。衝突發生在資料庫 "myDB"，資料表 "dbo.Scores", column 'stuID'。
陳述式已經結束。*/

/******************************************/

drop table Scores
truncate table Students
/*ON UPDATEDELETE CASCADE，宣告被參閱資料刪除或是修改時，一併
修改參閱的資料喔。如果學生更換代號時，系統會一併更換成績資料表的學
生代號。學生資料刪除時，成績資料一併刪除。很方便，我都不知道有這功能*/
--在Scores資料表中宣告ON UPDATE CASCADE ON DELETE CASCADE
create table scores
( stuID int not null
foreign key references Students(stuID)
ON UPDATE CASCADE ON DELETE CASCADE, --學生代號
Period varchar(30) not null, --學期
stuScore numeric(5,2) default 0, --成績
keyInUSR nvarchar(50) default suser_sname() --建檔人員
primary key (stuID,Period)
)
--新增三筆學生資料
insert into Students values(1, N'lewis', '009988', '1997/7/1', 'Taipei', null)
insert into Students values(2, N'ada', '006688', '1998/5/1', 'Taipei', null)
insert into Students values(3, N'julla', '005588', '1995/7/1', 'Taipei', null)
go
--新增兩筆學生成績資料
insert into Scores values(1, '200601', 90, default)
insert into Scores values(2, '200601', 80, default)
go
--查詢一下資料
select * from Students
--1 lewis 009988 1997-07-01 00:00:00.000 Taipei NULL
--2 ada 006688 1998-05-01 00:00:00.000 Taipei NULL
--3 julla 005588 1995-07-01 00:00:00.000 Taipei NULL
select * from Scores
--1 200601 90.00 shaq
--2 200601 80.00 shaq

--刪除1號學生
delete from Students where stuID = 1
--結果
/*(1 個資料列受到影響)*/
--竟然沒有錯誤耶，再查詢一下資料，發現雖然你只刪除Students資料表的資料，
--居然Scores資料表的資料也一併刪除了...神奇啊
select * from Students
--2 ada 006688 1998-05-01 00:00:00.000 Taipei NULL
--3 julla 005588 1995-07-01 00:00:00.000 Taipei NULL
select * from Scores
--2 200601 80.00 shaq

--更改2號學生編號
update Students set stuID = 200 where stuID = 2
--結果
--(1 個資料列受到影響)
--再查詢一下Scores資料表，發現Scores資料也跟著異動了喔
select * from Students
--3 julla 005588 1995-07-01 00:00:00.000 Taipei NULL
--200 ada 006688 1998-05-01 00:00:00.000 Taipei NULL
select * from Scores
--200 200601 80.00 shaq

/******************************************/

drop table Scores
truncate table Students
/*ON UPDATEDELETE SET NULL，就是被參閱資料刪除或是修改時，參
閱的資料就更改成null值了。例如有學生被刪除或是更換學號時，原成績
的學生代號，統一更改為null。取代刪除或隨之更改的動作*/
--在Scores資料表中宣告ON UPDATE SET NULL ON DELETE SET NULL
create table scores
( stuID int
foreign key references Students(stuID)
ON UPDATE SET NULL ON DELETE SET NULL, --學生代號
Period varchar(30) not null, --學期
stuScore numeric(5,2) default 0, --成績
keyInUSR nvarchar(50) default suser_sname() --建檔人員
)
--新增三筆學生資料
insert into Students values(1, N'lewis', '009988', '1997/7/1', 'Taipei', null)
insert into Students values(2, N'ada', '006688', '1998/5/1', 'Taipei', null)
insert into Students values(3, N'julla', '005588', '1995/7/1', 'Taipei', null)
go
--新增兩筆學生成績資料
insert into Scores values(1, '200601', 90, default)
insert into Scores values(2, '200601', 80, default)
go
--查詢一下資料
select * from Students
--1 lewis 009988 1997-07-01 00:00:00.000 Taipei NULL
--2 ada 006688 1998-05-01 00:00:00.000 Taipei NULL
--3 julla 005588 1995-07-01 00:00:00.000 Taipei NULL
select * from Scores
--1 200601 90.00 shaq
--2 200601 80.00 shaq

--刪除1號學生
delete from Students where stuID = 1
--結果
/*(1 個資料列受到影響)*/
--查詢一下資料，發現Scores原stuID為1的值已變為NULL囉
select * from Students
--2 ada 006688 1998-05-01 00:00:00.000 Taipei NULL
--3 julla 005588 1995-07-01 00:00:00.000 Taipei NULL
select * from Scores
--NULL 200601 90.00 shaq
--2 200601 80.00 shaq

--更改2號學生資料
update Students set stuID = 200 where stuID = 2
--結果
/*(1 個資料列受到影響)*/
--查詢一下資料，發現Students的stuID變為200，Scores原stuID值都變為NULL囉
select * from Students
--3 julla 005588 1995-07-01 00:00:00.000 Taipei NULL
--200 ada 006688 1998-05-01 00:00:00.000 Taipei NULL
select * from Scores
--NULL 200601 90.00 shaq
--NULL 200601 80.00 shaq