제약조건(Constraint)란 데이터의 무결성을 지키려고 제한된 조건을 의미한다. 즉 특정 데이터를 입력할 때 무조건적으로 입력되는 것이 아닌, 어떠한 조건을 만족했을 때만 입력되게 제약을 걸 수 있다.
SQL Server는 데이터의 무결성을 위해 6개의 제약 조건을 제공한다.
1. PRIMARY KEY 제약 조건
2. FOREIGN KEY 제약 조건
3. UNIQUE 제약 조건
4. CHECK 제약 조건
5. DEFAULT 정의
6. NULL 값 허용
1. 기본 키(Primary Key) 제약 조건
테이블에 있는 많은 행의 데이터를 구분할 수 있는 식별자를 기본 키(Primary Key) 라고 한다. 예를 들어, 회원 테이블의 회원 아이디, 학생 테이블의 학번 등이 이에 해당한다.
기본 키에 입력되는 값은 중복될 수 없으며, NULL이 입력될 수 없다.
기본 키는 테이블에서 중요한 의미를 갖는다. 우선 기본 키로 생성한 것은 자동으로 클로스터형 인덱스가 생성된다. 또한 테이블에서는 기본 키를 하나 이상 열에 설정할 수 있다. 두 개의 열을 합쳐서 기본 키로 설정할 수도 있다.
사진처럼 CREATE TABLE 시 컬럼에 PRIMARY KEY 예약어를 넣어주면 된다.
기본 키로 설정한 userID 컬럼은 중복이 될 수도, NULL이 될 수도 없다.
EXEC sp_help userTbl -- 테이블 정보 확인하는 프로시저
sp_help 프로시저를 사용하면 테이블 정보를 확인할 수 있다.
기본 키의 이름을 알 수 있다. 기본 키의 이름이 복잡하기 때문에 이름을 ‘PK_userTbl_userID’와 같이 명시해서 생성할 수도 있다.
테이블이 생성된 이후 ALTER TABLE을 구문을 사용하면 기본 키를 수정할 수 있다.
ALTER TABLE userTbl -- 테이블 수정 ADD CONTRAINT PK_userTbl_userID -- 제약 조건 추가, 제약조건 이름은 PK_userTbl_userID PRIMAKRY KEY (userID) -- userID를 기본키로 설정
기본 키는 각 테이블 별로 하나만 존재해야 한다. 하지만 기본 키를 하나의 컬럼으로만 구성해야하는 것은 아니다. 필요에 따라서 두 개 또는 그 이상의 컬럼을 합쳐서 하나의 기본 키로 설정할 수도 있다. 이를 복합 키라고 한다.
복합 키의 예를 위해 간단한 제품 테이블을 생각해보자.
제품 코드 | 제품 일련 번호 | 제조일자 | 현 상태 |
AAA | 0001 | 2013.10.10 | 판매완료 |
AAA | 0002 | 2013.10.11 | 매장진열 |
BBB | 0001 | 2013.10.12 | 재고창고 |
CCC | 0001 | 2013.10.13 | 판매완료 |
CCC | 0002 | 2013.10.14 | 매장진열 |
제품코드 AAA가 냉장고, BBB가 세탁기, CCC가 TV라고 가정한다면, 제품 코드는 중복이 될 수 밖에 없기 때문에 제품 코드를 기본 키로 설정할 수가 없다. 또 일련 번호도 마찬가지로 각 각 제품별로 순서대로 부여되는 번호이기 때문에 기본 키로 설정할 수 없다.
이런 경우 ‘제품 코드 + 제품 일련 번호’ 를 합친다면 유일한 값이 될 수 있으므로 기본 키로 설정한다.
CREATE TABLE prodTbl( prodCode NCHAR(3) NOT NULL, prodID NCHAR(4) NOT NULL, prodDate SMALLDATETIME NOT NULL, prodCur NCHAR(10) NULL ); GO ALTER TABLE prodTbl ADD CONSTRAINT PK_prodTbl_prodCode_prodID PRIMARY KEY (prodCode,prodID)
혹은
CREATE TABLE prodTbl( prodCode NCHAR(3) NOT NULL, prodID NCHAR(4) NOT NULL, prodDate SMALLDATETIME NOT NULL, prodCur NCHAR(10) NULL, CONSTRAINT PK_prodTbl_prodCode_prodID PRIMARY KEY(prodCode,prodID) );
위의 쿼리로 복합 키를 설정할 수 있다.
EXEC sp_help prodTbl
SP_HELP 프로시저를 통해 생성된 복합키의 정보를 알 수 있다.
2. 외래 키 제약조건
외래 키(Foreign Key) 제약 조건은 두 테이블 사이의 관계를 선언함으로써, 데이터의 무결성을 보장해주는 역할을 한다. 외래 키 관계를 설정하게 되면 하나의 테이블이 다른 테이블에 의존하게 된다.
FOREIGN KEY REFERENCE [기준 테이블명] (컬럼명)
사진과 같이 FOREIGN KEY 키워드를 사용해 외래 키를 설정할 수 있다.
외래 키 테이블의 컬럼이 참조하는 기준 테이블의 컬럼은 PRIMARY KEY 혹은 UNIQUE 설정이 되어있어야 한다.
외래 키의 이름을 직접 지정하려면 CONSTRAINT 를 사용하면 된다.
이미 만들어져 있다면 ALTER 문을 사용해 수정할 수 있다.
ALTER TABLE buyTbl ADD CONSTRAINT FK_userTbl_buyTbl FOREIGN KEY (userID) REFERENCES userTbl (userID)
외래 키의 옵션 중 ON DELETE CASCADE 또는 ON UPDATE CASCADE 옵션이 있는데, 이는 기준 테이블의 데이터가 변경되었을 때 외래 키 테이블도 자동으로 적용되게 설정해 준다.
ON UPDATE CASADE로 설정하면 회원 테이블의 회원ID가 변경될 경우, 구매 테이블의 회원ID도 같이 변경된다.
ALTER TABLE buyTbl ADD CONSTRAINT FK_userTbl_buyTbl FOREIGN KEY (userID) REFERENCES userTbl (userID) ON UPDATE CASCADE;
별도로 지정하지 않으면 ON UPDATE NO ACTION 및 ON DELETE NO ACTION을 설정한 것과 같다.
3. UNIQUE 제약 조건
UNIQUE 제약 조건은 중복되지 않은 유일한 값을 입력해야 하는 조건이다. UNIQUE는 PRIMARY KEY와 비슷해보이지만 NULL을 허용한다는 차이가 있다. 물론 NULL도 1개의 값만 허용한다.
일반적으로 회원 테이블의 이메일의 경우 UNIQUE로 설정하는 경우가 많다.
CREATE TABLE userTbl( .... email CHAR(30) NULL UNIQUE ); CREATE TABLE userTbl( .... email CHAR(30) NULL UNIQUE CONSTRAINT AK_email UNIQUE ); CREATE TABLE userTbl( .... email CHAR(30) NULL UNIQUE, ... CONSTRAINT AK_email UNIQUE(EMAIL) );
위와 같은 방법으로 UNIQUE 제약을 부여할 수 있다.
4. CHECK 제약 조건
CHECK 제약 조건은 입력되는 데이터를 점검하는 기능을 한다. 키에 마이너스 값이 들어올 수 없게 한다든지, 출생년도가 1900년 이후이고 현재 시점 이전이어야 한다는지 등의 조건을 지정할 수 있다.
-- 출생년이 1900년 이후이고 현재 시점 이전 ALTER TABLE userTbl ADD CONSTRAINT CK_birthYear CHECK (birthYear >= 1900 AND birthYear <= YEAR(GETDATE)); -- 휴대폰 국번 체크 ALTER TABLE userTbl ADD CONSTRAINT CK_mobile1 CHECK (mobile1 IN ('010','011','016','017','018','019')); -- 키가 0 이상이어야 함 ALTER TABLE userTbl ADD CONSTRAINT CK_height CHECK (height >= 0);
CHECK 제약 조건을 설정한 이후에는 제약 조건에 위배되는 값은 입력되지 않는다.
또한, ALTER TABLE 옵션 중 WITH CHECK와 WITH NOCHECK 옵션이 있는데, 이는 기존에 입력된 ㄷ ㅔ이터가 CHECK 제약 조건에 맞지 않을 경우에 어떻게 할 지를 결정해준다.
5. DEFAULT 정의
DEFAULT는 값을 입력하지 않았을 때, 자동으로 입력되는 기본 값을 정의하는 방법이다. 예를 들어, 출생년도를 입력하지 않으면 그냥 현재의 연도를 입력하고, 주소를 특별히 입력하지 않았다면 서울이 입력되며, 키를 입력하지 않았을 때 170이라고 입력되게 하고 싶다면 다음과 같이 정의할 수 있다.
use tempdb CREATE TABLE userTbl( userID CHAR(8) NOT NULL PRIMARY KEY, name NVARCHAR(10) NOT NULL, birthYear INT NOT NULL DEFAULT YEAR(getdate()), addr NCHAR(2) NOT NULL DEFAULT N'서울', mobile1 CHAR(3) NULL, mobile2 CHAR(8) NULL, height SMALLINT NULL DEFAULT 170, mDate DATE NULL UNIQUE );
또는 ALTER TABLE을 사용할 때 열을 지정하려면 FOR 문을 사용한다.
use tempdb CREATE TABLE userTbl( userID CHAR(8) NOT NULL PRIMARY KEY, name NVARCHAR(10) NOT NULL, birthYear INT NOT NULL, addr NCHAR(2) NOT NULL, mobile1 CHAR(3) NULL, mobile2 CHAR(8) NULL, height SMALLINT NULL, mDate DATE NULL UNIQUE ); ALTER TABLE userTbl ADD CONSTRAINT CD_birthYear DEFAULT YEAR(getdate()) FOR birthYear; ALTER TABLE userTbl ADD CONSTRAINT CD_addr DEFAULT N'서울' FOR addr; ALTER TABLE userTbl ADD CONSTRAINT CD_height DEFAULT 170 FOR height;
DEFAULT가 설정된 컬럼은 다음과 같은 방법으로 데이터를 입력할 수 있다.
-- default문은 DEFAULT로 설정된 값을 자동 입력한다. INSERT INTO userTbl VALUES('SYJ',N'손연재',default,default,'010','1234567',default,'2015.12.12'); -- 컬럼명이 명시되지 않으면 DEFAULT로 설정된 값을 자동 입력한다. INSERT INTO userTbl(userID,name) VALUES('JHJ',N'장혜진'); -- 값이 직접 명시되면 DEFAULT로 설정된 값은 무시된다. INSERT INTO userTbl VALUES('WB',N'원빈',1982,N'대전','019','9876543',176,'2014.5.5');
6. NULL 값 허용
NULL 값을 허용하려면 NULL문을, 허용하지 않으려면 NOT NULL을 사용하면 된다. 하지만 PRIMARY KEY가 설정된 컬럼에는 NULL 값이 있을 수 없으므로, 생략하면 자동으로 NOT NULL로 인식된다.
NULL 값은 ‘아무 것도 없다’ 라는 의미이다. 즉 공백이나 0 같은 값과는 다르다.
또 주의할 점은 NULL 혹은 NOT NULL을 표시하지 않았을 때이다. 데이터베이스의 옵션 중 ‘ANSI_NULL_DEFAULT(ANSI NULL 기본값)’ 옵션이 있는데 이것이 OFF(혹은 FALSE) 로 설정되어 있으면 NULL을, 아무 것도 붙이지 않으면 NULL을 허용하지 않는 NOT NULL을 붙인 것과 같다.
USE tempdb; CREATE DATABASE nullDB; ALTER DATABASE nullDB SET ANSI_NULL_DEFAULT OFF; -- 설정하지 않으면 기본값은 OFF GO USE nullDB; CREATE TABLE t1 (id int); GO INSERT INTO t1 VALUES(NULL);
위의 쿼리를 실행했을 때 NULL값이 입력된다.
데이터베이스는 아무 것도 지정하지 않으면 NOT NULL이기 때문이다. 그런데도 NULL값이 입력되었다.
EXEC sp_help t1
테이블 정보를 확인했을 때 Nullable 이 yes로 되어있다. 이유는 현재 세션(현재 쿼리창)의 옵션 중에 ANSI_NULL_DFLT_ON 옵션이 있는데, 이 옵션이 ON으로 설정되어 있기 때문이다. 이 세션 옵션이 데이터베이스 옵션보다 더 먼저 적용되기 때문에 아무것도 붙이지 않으면 NULL을 붙인 것과 같은 효과를 주는 것이다.
USE tempdb; GO CREATE DATABASE nullDB; GO ALTER DATABASE nullDB SET ANSI_NULL_DEFAULT OFF; -- 설정하지 않으면 기본값은 OFF GO SET ANSI_NULL_DFLT_ON OFF; USE nullDB; CREATE TABLE t1 (id int); GO INSERT INTO t1 VALUES(NULL); SELECT * FROM t1
ANSI_NULL_DFLT_ON 설정을 OFF로 했을 때 NULL허용 여부가 NO로 되어 NULL 값이 입력되지 않는다.
Share article