Dunk

-- 資料來源:德瑞克老師的上課筆記+自行修改
USE ProductDB
GO

-- 使用 DEFAULT 「條件約束(Constraint)」
-- 從正式表格隨機選取員工編號後新增測試資料
-- 建立測試表格
IF  EXISTS
(SELECT *
 FROM
	 sys.objects
 WHERE
	 object_id = object_id(N'[dbo].[EMPLOYEE_TEST_DATA]')
	 AND type IN (N'U')) DROP TABLE [dbo].[EMPLOYEE_TEST_DATA]
GO
CREATE TABLE [dbo].[EMPLOYEE_TEST_DATA]
(
[STAFF] [nvarchar](6)PRIMARY KEY,
[NOTE][nvarchar](30)DEFAULT ('這是林大貓的測試資料'),
[CEATETIME][datetime] DEFAULT getdate()
);
GO

-- 檢查測試表格
SELECT *
FROM
	dbo.EMPLOYEE_TEST_DATA

-- 隨機選取十筆資料當作測試資料
INSERT [dbo].[EMPLOYEE_TEST_DATA] (STAFF)
SELECT TOP 10 STAFF
FROM
	dbo.EMPLOYEE
ORDER BY
	newid()
GO

-- 查詢測試表格資料
SELECT *
FROM
	dbo.EMPLOYEE_TEST_DATA

-- CHECK 條件約束限制資料行接受的值，藉此強制值域完整性。
-- 可用於partition table 	
-- 檢查條件:測試表格的備註欄位不得為李大牛
ALTER TABLE [dbo].[EMPLOYEE_TEST_DATA]  
WITH CHECK ADD  
	CONSTRAINT [CK_NAME] CHECK  (([NOTE]<>'李大牛'))
GO

-- 看條件約束是否建立
SELECT name N'物件名稱'
	 , type N'物件類型'
	 , type_desc N'物件類型描述'
	 , definition N'「CHECK」條件約束的 SQL 運算式'
FROM
	sys.check_constraints
WHERE
	parent_object_id = object_id('EMPLOYEE_TEST_DATA')
GO

-- 測試新增跟修改是否會失敗
INSERT INTO [dbo].[EMPLOYEE_TEST_DATA]
VALUES
	(123456, N'李大牛', getdate())

UPDATE [dbo].[EMPLOYEE_TEST_DATA]
SET
	NOTE = N'李大牛'
GO

-- 停用條件約束
ALTER TABLE [dbo].[EMPLOYEE_TEST_DATA] 
	NOCHECK CONSTRAINT ALL
GO

-- 檢查是否停用
SELECT name N'物件名稱'
	 , type N'物件類型'
	 , type_desc N'物件類型描述'
	 , definition N'「CHECK」條件約束的 SQL 運算式'
	 , is_disabled N'CHECK 條件約束已停用'
FROM
	sys.check_constraints
GO

-- Foreign Key 外部索引鍵
-- 主鍵表格新增固定資料
TRUNCATE TABLE [dbo].[EMPLOYEE_TEST_DATA]
GO
INSERT [dbo].[EMPLOYEE_TEST_DATA] (STAFF)
SELECT '123456'
GO

SELECT *
FROM
	dbo.EMPLOYEE_TEST_DATA

-- 建立測試表格(FK)
IF  EXISTS
(SELECT *
 FROM
	 sys.objects
 WHERE
	 object_id = object_id(N'[dbo].[EMPLOYEE_TEST_DATA_FK]')
	 AND type IN (N'U')) DROP TABLE [dbo].[EMPLOYEE_TEST_DATA_FK]
GO
CREATE TABLE [dbo].[EMPLOYEE_TEST_DATA_FK]
(
	[LOGOUTTIME][datetime] DEFAULT getdate(),
	[STAFF] [nvarchar](6)	
);
GO

-- 檢查表格是否建立
SELECT *
FROM
	dbo.EMPLOYEE_TEST_DATA_FK

-- 輸入測試資料
INSERT dbo.EMPLOYEE_TEST_DATA_FK (STAFF)
SELECT STAFF
FROM
	dbo.EMPLOYEE_TEST_DATA
GO
-- 檢查資料是否匯入
SELECT *
FROM
	dbo.EMPLOYEE_TEST_DATA_FK		
	
-- 建立Foreign Key
-- NO ACTION，違反時，此動作產生錯誤，並復原(Rollback)。
ALTER TABLE dbo.EMPLOYEE_TEST_DATA_FK ADD CONSTRAINT
	FK_EMPLOYEE_TEST_DATA FOREIGN KEY
	(
	STAFF
	) REFERENCES dbo.EMPLOYEE_TEST_DATA
	(
	STAFF
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION
GO

-- 檢查Foreign Key是否建立
SELECT name N'物件名稱'
	 , type_desc N'物件描述'
	 , object_name(referenced_object_id) N'主索引資料表'
	 , object_name(parent_object_id) N'外部索引資料表'
	 , delete_referential_action_desc N'在進行刪除時，宣告的參考動作之描述'
	 , update_referential_action_desc N'在進行更新時，宣告的參考動作之描述'
FROM
	sys.foreign_keys
GO

-- 測試新增跟修改外鍵表格資料是否會失敗
-- (999999為不存在的員工編號)
INSERT INTO [dbo].[EMPLOYEE_TEST_DATA_FK] (STAFF)
VALUES
	('999999')

UPDATE [dbo].[EMPLOYEE_TEST_DATA_FK]
SET
	STAFF = '999999'
GO

-- 檢查資料
SELECT *
FROM
	dbo.EMPLOYEE_TEST_DATA
SELECT *
FROM
	dbo.EMPLOYEE_TEST_DATA_FK

-- 建立Foreign Key
-- CASCADE，自動刪除或更新「外部索引鍵資料表」上所對應的資料。
ALTER TABLE dbo.EMPLOYEE_TEST_DATA_FK
	DROP CONSTRAINT FK_EMPLOYEE_TEST_DATA
ALTER TABLE dbo.EMPLOYEE_TEST_DATA_FK ADD CONSTRAINT
	FK_EMPLOYEE_TEST_DATA FOREIGN KEY
	(
	STAFF
	) REFERENCES dbo.EMPLOYEE_TEST_DATA
	(
	STAFF
	) ON UPDATE  CASCADE 
	 ON DELETE  CASCADE
GO

-- 檢查Foreign Key是否建立
SELECT name N'物件名稱'
	 , type_desc N'物件描述'
	 , object_name(referenced_object_id) N'主索引資料表'
	 , object_name(parent_object_id) N'外部索引資料表'
	 , delete_referential_action_desc N'在進行刪除時，宣告的參考動作之描述'
	 , update_referential_action_desc N'在進行更新時，宣告的參考動作之描述'
FROM
	sys.foreign_keys
GO

-- 測試修改跟跟刪除主鍵表格資料是否會將外鍵表格的資料異動
-- (999999為不存在的員工編號)
UPDATE [dbo].[EMPLOYEE_TEST_DATA]
SET
	STAFF = '999999'
WHERE
	STAFF = '123456'

DELETE
FROM
	[dbo].[EMPLOYEE_TEST_DATA]
WHERE
	STAFF = '999999'
GO

-- 檢查資料
SELECT *
FROM
	dbo.EMPLOYEE_TEST_DATA
SELECT *
FROM
	dbo.EMPLOYEE_TEST_DATA_FK

-- 刪除測試資料
ALTER TABLE dbo.EMPLOYEE_TEST_DATA_FK
	DROP CONSTRAINT FK_EMPLOYEE_TEST_DATA
DROP TABLE dbo.EMPLOYEE_TEST_DATA_FK
DROP TABLE dbo.EMPLOYEE_TEST_DATA
GO