[MSSQL] CREATE TABLE - 제약조건, CONSTRAINT

[MSSQL] CREATE TABLE - 제약조건, CONSTRAINT 

1. PRIMARY

CREATE TABLE TblSample ( KEY1 NVARCHAR(10), KEY2 NVARCHAR(10), KEY3 NVARCHAR(10), FK1 NVARCHAR(10), FK2 NVARCHAR(10), DESC1 NVARCHAR(1000), DESC2 NVARCHAR(1000) PRIMARY KEY CLUSTERED(KEY1 ASC, KEY2 DESC, KEY3 ASC) -- PK )

2. UNIQUE

CREATE TABLE TblSample ( KEY1 NVARCHAR(10), KEY2 NVARCHAR(10), KEY3 NVARCHAR(10), FK1 NVARCHAR(10), FK2 NVARCHAR(10), UK1 NVARCHAR(10), UK2 NVARCHAR(10), DESC1 NVARCHAR(1000), DESC2 NVARCHAR(1000) PRIMARY KEY CLUSTERED(KEY1 ASC, KEY2 DESC, KEY3 ASC), -- PK CONSTRAINT AK_UK UNIQUE(UK1,UK2) -- UNIQUE ) // ------------------- 예 ------------------- INSERT INTO TblSample(Key1,key2,key3,UK1,UK2) VALUES ('1','1','1','2','2'), ('1','1','2','2','2') // ------------------- 결과 ------------------- 메시지 2627, 수준 14, 상태 1, 줄 22 Violation of UNIQUE KEY constraint 'AK_UK'. Cannot insert duplicate key in object 'TblSample'. The duplicate key value is (2, 2).

3. FOREIGN KEY

CREATE TABLE TblFK ( FK1 NVARCHAR(10), FK2 NVARCHAR(10), DESC1 NVARCHAR(1000) PRIMARY KEY CLUSTERED(FK1 ASC, FK2 ASC) ) CREATE TABLE TblSample ( KEY1 NVARCHAR(10), KEY2 NVARCHAR(10), KEY3 NVARCHAR(10), FK1 NVARCHAR(10), FK2 NVARCHAR(10), UK1 NVARCHAR(10), UK2 NVARCHAR(10), DESC1 NVARCHAR(1000), DESC2 NVARCHAR(1000) PRIMARY KEY CLUSTERED(KEY1 ASC, KEY2 DESC, KEY3 ASC), -- PK CONSTRAINT AK_UK UNIQUE(UK1,UK2), -- UNIQUE CONSTRAINT FK_TblSample_TblFK FOREIGN KEY (FK1,FK2) REFERENCES TblFK (FK1,FK2) ON DELETE CASCADE -- 기준 테이블의 데이터가 삭제되었을 때 외래 키 테이블도 자동으로 삭제(Default : ON DELETE NO ACTION) ON UPDATE CASCADE -- 기준 테이블의 데이터가 변경되었을 때 외래 키 테이블도 자동으로 변경(Default :ON UPDATE NO ACTION )

4. CHECK ( 입력데이터 검증 )

CREATE TABLE TblSample ( KEY1 NVARCHAR(10), KEY2 NVARCHAR(10), KEY3 NVARCHAR(10), FK1 NVARCHAR(10), FK2 NVARCHAR(10), UK1 NVARCHAR(10), UK2 NVARCHAR(10), CHK1 INT, CHK2 NVARCHAR(2), DESC1 NVARCHAR(1000), DESC2 NVARCHAR(1000), CONSTRAINT CK_UK1 CHECK (CHK1 >= 1 AND CHK1 <= 10), CONSTRAINT CK_UK2 CHECK (CHK2 IN ('3','4')) ) // ------------------- 예 ------------------- INSERT INTO TblSample(Key1,key2,key3,CHK1,CHK2) VALUES ('1','1','1',11,'3') // ------------------- 결과 ------------------- 메시지 547, 수준 16, 상태 0, 줄 33 The INSERT statement conflicted with the CHECK constraint "CK_UK1". The conflict occurred in database "chsmanager", table "chsmanager.TblSample", column 'CHK1'. // ------------------- 예 ------------------- INSERT INTO TblSample(Key1,key2,key3,CHK1,CHK2) VALUES ('1','1','1',10,'6') // ------------------- 결과 ------------------- 메시지 547, 수준 16, 상태 0, 줄 33 The INSERT statement conflicted with the CHECK constraint "CK_UK2". The conflict occurred in database "chsmanager", table "chsmanager.TblSample", column 'CHK2'.

5. DEFAULT

CREATE TABLE TblSample ( KEY1 NVARCHAR(10), KEY2 NVARCHAR(10), KEY3 NVARCHAR(10), DF1 NVARCHAR(8) NOT NULL DEFAULT CONVERT(VARCHAR(8),GETDATE(),112), DF2 DATETIME NOT NULL DEFAULT GETDATE(), DF3 INT NOT NULL DEFAULT 1, DF4 INT NOT NULL DEFAULT YEAR(GETDATE()), DF5 NCHAR(2) NOT NULL DEFAULT N'Y' )