MSSQL 제약 조건(CONSTRAINT)

제약 조건(CONSTRAINT)

제약 조건(constraint)이란 데이터의 무결성을 지키기 위해, 데이터를 입력받을 때 실행되는 검사 규칙을 의미합니다.

즉, 데이터를 삽입, 수정 등을 할 때 무조건 삽입하고 수정하는 것이 아니고 제한된 조건을 만족해야지만 삽입하고 수정하여 무결성을 지키는 것입니다.

이러한 제약 조건은 CREATE 문으로 테이블을 생성할 때나 ALTER 문으로 컬럼을 추가할 때도 설정할 수도 있습니다.

제약 조건명을 명시하지 않고 추가할 경우 시스템이 제약 조건명을 자동 할당하게 되는데, 이 경우보다는 관리에 효율성을 위해 제약 조건명을 명시하는 편이 좋음. 시스템이 제약 조건명을 자동 할당했는지 아니면 생성시 설정한 제약 조건명으로 설정했는지 아래 쿼리로 확인할 수 있음.

- is_system_named 컬럼 값이 1이면 시스템 자동 할당, 0이면 설정한 제약 조건명

SELECT OBJECT_SCHEMA_NAME(object_id) AS schema_name, OBJECT_NAME(parent_object_id) AS table_name, [name], is_system_named FROM sys.key_constraints WHERE [name] = '제약조건명'

(1) NOT NULL

- NULL 비허용

- 중복값 허용

NOT NULL 제약 조건은 CREATE 문으로 테이블을 생성할 때나, 나중에 ALTER 문으로 추가할 수도 있습니다.

CREATE TABLE [스키마명].[테이블명] ( ..., [컬럼명] 데이터타입 NOT NULL, ..., ) ALTER TABLE [스키마명].[테이블명] ADD [컬럼명] 데이터타입 NOT NULL -- 새로운 NOT NULL 지정한 컬럼 추가 ALTER TABLE [스키마명].[테이블명] ALTER COLUMN [컬럼명] 데이터타입 NOT NULL -- 기존 컬럼을 NOT NULL로 수정

(2) DEFAULT

- DEFAULT 제약 조건은 해당 컬럼의 기본값을 설정할 수 있게 해줍니다.

만약 레코드를 입력할 때 해당 컬럼 값을 전달하지 않으면, 자동으로 설정된 기본값을 저장합니다.

제약 조건명을 명시하지 않고 추가하면 시스템이 제약 조건명을 자동 할당하고, 제약 조건명을 명시하면 명시한 해당 제약 조건명으로 설정할 수 있습니다.

CREATE TABLE [스키마명].[테이블명] ( ..., [컬럼명] 데이터타입 <NULL|NOT NULL> DEFAULT 값, ..., ) CREATE TABLE [스키마명].[테이블명] ( ..., [컬럼명] 데이터타입 <NULL|NOT NULL> CONSTRAINT [제약조건명] DEFAULT 값, ..., ) ALTER TABLE [스키마명].[테이블명] ADD [컬럼명] 데이터타입 <NULL|NOT NULL> DEFAULT 값 ALTER TABLE [스키마명].[테이블명] ADD CONSTRAINT [제약조건명] DEFAULT 값 FOR [컬럼명] -- 기본값 삭제 ALTER TABLE [스키마명].[테이블명] DROP CONSTRAINT [제약조건명] -- 기본값 변경 -- 기존 default 값을 변경하고자 한다면, 기존 default 제약 조건을 삭제하고, 새로운 제약 조건을 추가 -- 기본 제약 조건 확인 SELECT DB_NAME() AS db_name, const.parent_object_id AS object_id, OBJECT_SCHEMA_NAME(const.parent_object_id) AS schema_name, const.table_name AS table_name, const.name AS default_const_name, COL_NAME(const.parent_object_id, const.parent_column_id) AS column_name, const.definition AS default_value FROM ( SELECT a.*, b.name AS table_name FROM sys.default_constraints AS a INNER JOIN sys.tables AS b ON a.parent_object_id = b.object_id WHERE b.type = 'U' AND b.name = '테이블명' ) AS const ORDER BY const.table_name ASC, const.name ASC

(3) CHECK

- 컬럼이나 컬럼들에 들어갈 수 있는 값을 제한

- 부호, 숫자 비교, 문자 비교 등을 비교할 수 있음

- 입력되는 값이 CHECK 조건과 맞지 않으면 에러가 발생

CREATE TABLE [스키마명].[테이블명] ( ..., [컬럼명] 데이터타입 <NULL|NOT NULL> CHECK (컬럼 제약조건 정의값), ..., ) CREATE TABLE [스키마명].[테이블명] ( ..., [컬럼명] 데이터타입 <NULL|NOT NULL> CONSTRAINT [제약조건명] CHECK (컬럼 제약조건 정의값), ..., ) CREATE TABLE [스키마명].[테이블명] ( ..., [컬럼명] 데이터타입 <NULL|NOT NULL> CONSTRAINT [제약조건명] DEFAULT 값 CONSTRAINT [제약조건명] CHECK (컬럼 제약조건 정의값), ..., ) CREATE TABLE [스키마명].[테이블명] ( ..., [컬럼명] 데이터타입 <NULL|NOT NULL>, ..., CONSTRAINT [제약조건명] CHECK (컬럼 제약조건 정의값), ..., ) ALTER TABLE [스키마명].[테이블명] ADD CHECK (컬럼 제약조건 정의값) ALTER TABLE [스키마명].[테이블명] ADD CONSTRAINT [제약조건명] CHECK (컬럼 제약조건 정의값) -- 체크 제약 조건 삭제 ALTER TABLE [스키마명].[테이블명] DROP CONSTRAINT [제약조건명] -- 체크 제약 조건 확인 SELECT DB_NAME() AS db_name, const.parent_object_id AS object_id, OBJECT_SCHEMA_NAME(const.parent_object_id) AS schema_name, const.table_name AS table_name, const.name AS check_const_name, const.definition AS check_value FROM ( SELECT a.*, b.name AS table_name FROM sys.check_constraints AS a INNER JOIN sys.tables AS b ON a.parent_object_id = b.object_id WHERE b.type = 'U' AND b.name = '테이블명' ) AS const ORDER BY const.table_name ASC, const.name ASC

(4) UNIQUE

- NULL 허용

- 중복값 비허용

CREATE TABLE [스키마명].[테이블명] ( ..., [컬럼명] 데이터타입 <NULL|NOT NULL> UNIQUE <CLUSTERED|NONCLUSTERED>, ..., ) CREATE TABLE [스키마명].[테이블명] ( ..., [컬럼명] 데이터타입 <NULL|NOT NULL> CONSTRAINT [제약조건명] UNIQUE <CLUSTERED|NONCLUSTERED>, ..., ) CREATE TABLE [스키마명].[테이블명] ( ..., [컬럼명] 데이터타입 <NULL|NOT NULL>, ..., CONSTRAINT [제약조건명] UNIQUE <CLUSTERED|NONCLUSTERED> ([컬럼명] 정렬방식), ..., ) ALTER TABLE [스키마명].[테이블명] ADD CONSTRAINT [제약조건명] UNIQUE <CLUSTERED|NONCLUSTERED> ([컬럼명] 정렬방식) -- 유니크 삭제 ALTER TABLE [스키마명].[테이블명] DROP CONSTRAINT [제약조건명] -- 유니크키 확인 SELECT DB_NAME() AS db_name, i.object_id AS object_id, OBJECT_SCHEMA_NAME(i.object_id) AS schema_name, i.table_name AS table_name, i.name AS index_name, i.index_id, i.type AS index_kind, 0 AS index_type, i.is_primary_key, i.is_unique, kc.is_system_named, ic.index_column_id AS column_seq, COL_NAME(ic.object_id, ic.column_id) AS column_name, (CASE is_descending_key WHEN 1 THEN 2 ELSE 1 END) AS column_sort FROM ( SELECT a.*, b.name AS table_name FROM sys.indexes AS a INNER JOIN sys.tables AS b ON a.object_id = b.object_id WHERE b.type = 'U' AND b.name = '테이블명' AND a.is_unique = 1 ) AS i INNER JOIN sys.index_columns AS ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id LEFT OUTER JOIN sys.key_constraints AS kc ON i.object_id = kc.parent_object_id AND i.name = kc.name WHERE i.type > 0 ORDER BY i.table_name ASC, i.index_id ASC, ic.index_column_id ASC

(5) PRIMARY KEY

- NULL 비허용

- 중복값 비허용

- 테이블당 하나

CREATE TABLE [스키마명].[테이블명] ( [컬럼명] 데이터타입 NOT NULL PRIMARY KEY, ..., ) CREATE TABLE [스키마명].[테이블명] ( [컬럼명] 데이터타입 NOT NULL CONSTRAINT [제약조건명] PRIMARY KEY <CLUSTERED|NONCLUSTERED>, ..., ) CREATE TABLE [스키마명].[테이블명] ( [컬럼명] 데이터타입 NOT NULL, ..., CONSTRAINT [제약조건명] PRIMARY KEY <CLUSTERED|NONCLUSTERED> ([컬럼명] 정렬방식), ..., ) ALTER TABLE [스키마명].[테이블명] ADD CONSTRAINT [제약조건명] <PRIMARY KEY|UNIQUE> <CLUSTERED|NONCLUSTERED> ([컬럼명] [정렬방식]) -- 기본키 삭제 ALTER TABLE [스키마명].[테이블명] DROP CONSTRAINT [제약조건명] -- 기본키 확인 SELECT DB_NAME() AS db_name, i.object_id AS object_id, OBJECT_SCHEMA_NAME(i.object_id) AS schema_name, i.table_name AS table_name, i.name AS index_name, i.index_id, i.type AS index_kind, 0 AS index_type, i.is_primary_key, i.is_unique, kc.is_system_named, ic.index_column_id AS column_seq, COL_NAME(ic.object_id, ic.column_id) AS column_name, (CASE is_descending_key WHEN 1 THEN 2 ELSE 1 END) AS column_sort FROM ( SELECT a.*, b.name AS table_name FROM sys.indexes AS a INNER JOIN sys.tables AS b ON a.object_id = b.object_id WHERE b.type = 'U' AND b.name = '테이블명' AND a.is_primary_key = 1 ) AS i INNER JOIN sys.index_columns AS ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id LEFT OUTER JOIN sys.key_constraints AS kc ON i.object_id = kc.parent_object_id AND i.name = kc.name WHERE i.type > 0 ORDER BY i.table_name ASC, i.index_id ASC, ic.index_column_id ASC

(6) FOREIGN KEY

- FOREIGN KEY 제약 조건을 설정한 컬럼은 외래 키라고 부르며,

- 한 테이블을 다른 테이블과 연결해주는 역할을 합니다.

FOREIGN KEY 제약 조건을 설정할 때 참조되는 테이블의 컬럼은 반드시 PRIMARY KEY(기본키)나 UNIQUE 제약 조건이 설정되어 있어야 합니다. 설정할 컬럼은 기본키나 유니크가 아니면 생성이 제약됩니다.

아래 문법을 사용하면 해당 컬럼에 FOREIGN KEY 제약 조건을 설정합니다.

이때 참조되는 테이블의 이름은 REFERENCES 키워드 다음에 명시됩니다.

CREATE TABLE [스키마명].[테이블명] ( ..., [컬럼명] [int] NOT NULL FOREIGN KEY REFERENCES [부모테이블명] ([PK컬럼명]) [ON DELETE | UPDATE CASCADE], ..., ) CREATE TABLE [스키마명].[테이블명] ( ..., [컬럼명] 데이터타입 <NULL|NOT NULL>, ..., CONSTRAINT [제약조건명] FOREIGN KEY ([컬럼명]) REFERENCES [부모테이블명] ([PK컬럼명]) [ON DELETE | UPDATE CASCADE], ..., ) ALTER TABLE [스키마명].[테이블명] ADD CONSTRAINT [제약조건명] FOREIGN KEY (컬럼명) REFERENCES [부모테이블명] ([PK컬럼명]) [ON DELETE | UPDATE CASCADE] -- 외래키 삭제 ALTER TABLE [스키마명].[테이블명] DROP CONSTRAINT [제약조건명] -- 외래키 확인 SELECT DB_NAME() AS db_name, fk.parent_object_id AS object_id, OBJECT_SCHEMA_NAME(fk.parent_object_id) AS schema_name, fk.table_name AS table_name, fk.name AS foreignkey_name, fk.is_disabled, fk.is_not_trusted, OBJECT_NAME(fkcol.parent_object_id) AS foreignkey_table_name, COL_NAME(fkcol.parent_object_id, fkcol.parent_column_id) AS foreignkey_column_name, OBJECT_SCHEMA_NAME(fkcol.referenced_object_id) AS referencekey_schema_name, OBJECT_NAME(fkcol.referenced_object_id) AS referencekey_table_name, COL_NAME(fkcol.referenced_object_id, fkcol.referenced_column_id) AS referencekey_column_name, REPLACE(fk.update_referential_action_desc, '_', ' ') AS update_rule, REPLACE(fk.delete_referential_action_desc, '_', ' ') AS delete_rule FROM ( SELECT a.*, b.name AS table_name FROM sys.foreign_keys AS a INNER JOIN sys.tables AS b ON a.parent_object_id = b.object_id WHERE b.type = 'U' AND b.name = '테이블명' ) AS fk INNER JOIN sys.foreign_key_columns AS fkcol ON fk.object_id = fkcol.constraint_object_id ORDER BY fk.table_name ASC, fk.name ASC, fkcol.constraint_column_id ASC, fkcol.referenced_column_id ASC

ON DELETE / ON UPDATE

- FOREIGN KEY 제약 조건에 의해 참조되는 테이블에서 데이터의 수정이나 삭제가 발생하면, 참조하고 있는 테이블의 데이터도 같이 영향을 받습니다.

이때 참조하고 있는 테이블의 동작은 다음 키워드를 사용하여 FOREIGN KEY 제약 조건에서 미리 설정할 수 있습니다.

ON DELETE + 동작
ON UPDATE + 동작

참조되는 테이블의 값이 삭제될 경우의 동작은 ON DELETE 구문으로 설정할 수 있습니다.

또한, 참조되는 테이블의 값이 수정될 경우의 동작은 ON UPDATE 구문으로 설정할 수 있습니다.

이때 설정할 수 있는 동작은 다음과 같습니다.

CASCADE : 참조되는 테이블에서 데이터를 삭제하거나 수정하면, 참조하는 테이블에서도 삭제와 수정이 같이 이루어집니다.

SET NULL : 참조되는 테이블에서 데이터를 삭제하거나 수정하면, 참조하는 테이블의 데이터는 NULL로 변경됩니다.

NO ACTION : 참조되는 테이블에서 데이터를 삭제하거나 수정해도, 참조하는 테이블의 데이터는 변경되지 않습니다.

SET DEFAULT : 참조되는 테이블에서 데이터를 삭제하거나 수정하면, 참조하는 테이블의 데이터는 컬럼의 기본값으로 설정됩니다.

RESTRICT : 참조하는 테이블에 데이터가 남아 있으면, 참조되는 테이블의 데이터를 삭제하거나 수정할 수 없습니다.

티스토리툴바