CONSTRAINT(條件約束)

◎ 將已存在的欄位加入 CONSTRAINT (條件約束)

--將已存在的欄位加入「預設值(DF)」的 CONSTRAINT (條件約束)
ALTER TABLE [Gamer]
ADD CONSTRAINT [DF_Gamer_GenderId]
DEFAULT ((3)) FOR [GenderId]
GO
ALTER TABLE [Gamer]
ADD CONSTRAINT [DF_Gamer_CreatedDateTime]
DEFAULT (GETUTCDATE()) FOR [CreatedDateTime]
GO

--將已存在的欄位加入「檢查(CK)」的 CONSTRAINT (條件約束)
ALTER TABLE [Gamer]
ADD CONSTRAINT [CK_Gamer_Age]
CHECK ([Age] > 0 AND [Age] < 150)
GO

--將已存在的欄位加入「唯一值(UK)」的 CONSTRAINT (條件約束)
--備註：SSMS同時會自動新增一個非叢集索引(NonClustered Index),若刪除了UNIQUE CONSTRAINT, 非叢集索引也會被刪除
ALTER TABLE [Gamer]
ADD CONSTRAINT [UK_Gamer_Email]
UNIQUE ([Email])
GO

◎ 新增欄位時候同時加入 CONSTRAINT (條件約束)

ALTER TABLE [Gamer]
ADD [GenderId2] INT NOT NULL 
CONSTRAINT [DF_Gamer_GenderId2]
DEFAULT ((3))
GO

◎ 移除CONSTRAINT(條件約束)

--如果要移除某個欄位
--但是這個欄位有設定任何 CONSTRAINT (條件約束)
--必須先把 CONSTRAINT 移除之後才能移除欄位
IF ( EXISTS ( SELECT *
              FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
              WHERE CONSTRAINT_NAME = 'DF_Gamer_GenderId2' ) )
BEGIN
              ALTER TABLE [Gamer]
              DROP CONSTRAINT [DF_Gamer_GenderId2]
END
GO

ALTER TABLE [Gamer]
DROP COLUMN [GenderId2]
GO

◎ 撈出所有的 CONSTRAINT (條件約束)

--方法一，所需權限較高
SELECT *
FROM SYS.objects
WHERE type_desc LIKE '%CONSTRAINT%' AND OBJECT_NAME(object_id) = 'DF_Orders_Freight'

--方法二
SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_NAME = 'CK_Gamer_Age'