[SQLite] 테이블 제약 조건

보통 테이블 스키마를 작성한 사람과 해당 테이블의 데이터를 입력하고 사용하는 사용자가 다른 경우가 많다. 그렇기에 우리는 최초 테이블 스키마를 작성할 때의 의도와 다르게 사용하는 경우가 많이 생기며 이로 인한 오류도 많이 발생하게 된다. 이러한 오류들을 사전에 방지하기 위해 우리는 특정 컬럼에 제약 조건을 걸어 해당 컬럼이 설정된 조건을 만족하도록 강제할 수 있다. 예를 들어 NULL값이 입력되면 안되는 컬럼의 경우 데이터가 입력이 누락된 경우에는 에러를 발생시킨다 던지 입력이 누락된 컬럼에 대한 기본값을 설정할 수도 있다. 이번 시간에 그러한 테이블 제약조건에 대해서 알아 보고자 한다.

 

• NOT NULL

 

NOT NULL 은 반드시 데이터가 존재해야 만 하는 컬럼을 위한 제약 조건이다. 이 해당 제약 조건을 통해 해당 컬럼에 NULL값이 들어가게 되는 경우 ERROR를 발생시킨다. 사용 방법은 테이블 생성 시 해당 제약 조건을 설정하고자 하는 컬럼 뒤에 NOT NULL 문을 추가하면 된다.

 

예를 위해 명함 카드 정보를 저장하는 테이블을 생성 해 보도록 하자.

명함에서 없어서는 안될 정보는 아마 이름과 연락처 일 것이다. 그러므로 이 두 컬럼에 NOT NULL 제약 조건이 설정된 테이블을 생성해 보도록 하겠다.

 

sqlite> CREATE TABLE nameCard (    ...> id INTEGER PRIMARY KEY AUTOINCREMENT,    ...> name TEXT NOT NULL,    ...> mobile TEXT NOT NULL,    ...> address TEXT,    ...> company TEXT,    ...> email TEXT    ...> );

 

자동으로 증가하는 id 값과 name, mobile, address, company, email column을 가지는 nameCard를 생성하였고 이 컬럼들 중 name과 mobile은 NOT NULL 제약 조건을 설정해 보았다.

그리고 만약 NOT NULL 제약 조건이 걸린 컬럼에 NULL 데이터를 넣을려고 하면 아래와 같이 에러가 발생할 수 있음을 확인 할 수 있다.

 

sqlite> INSERT INTO nameCard(name, mobile)    ...> VALUES (NULL, '010-1234-5678');   Error: NOT NULL constraint failed: nameCard.name

 

• DEFAULT 제약조건

 

이번에 소개할 제약조건은 바로 DEFAULT 제약조건으로 만약 특정 컬럼에 아무런 값이 없는 경우 NULL 대신 기본값이 입력되도록 해주는 제약 조건입니다.

 

 사용법은 이 전 NOT NULL과 마찬가지로 TABEL 생성 시 컬럼 뒤에 DEFAULT 키워드를 추가하고 그 뒤에 내가 설정하고자 하는 기본값을 지정해 주면 된다. 이 전에 만든 nameCard Table을 삭제한 후 다시 생성할 텐데 이 번에는 name, mobile 컬럼에는 NOT NULL 제약 조건을 address 에는 "Republic of Korea"라는 기본값이 들어 가도록 DEFAULT 제약조건을 설정해 보도록 하겠다.

 

sqlite> DROP TABLE nameCard ; sqlite> CREATE TABLE nameCard (    ...> id INTEGER PRIMARY KEY AUTOINCREMENT,    ...> name TEXT NOT NULL,    ...> mobile TEXT NOT NULL,    ...> address TEXT DEFAULT 'Republic of Korea',    ...> company TEXT,    ...> email TEXT    ...> );

 

Table이 생성되었으니 이번에는 한 번 레코드를 하나 추가해 보도록 하겠다.

name은 Jmini이고 mobile 값은 010-1234-5678인 레코드를 추가할 텐데 이 때 address, company, email 컬럼에는 아무런 값도 지정해 주지 않을 것이다.

 

sqlite> INSERT INTO nameCard (name, mobile)    ...> VALUES ('Jmini', '010-1234-5678'); sqlite> SELECT * FROM nameCard;   id          name        mobile         address            company     email      ----------  ----------  -------------  -----------------  ----------  ---------- 1           Jmini       010-1234-5678  Republic of Korea  NULL        NULL

 

위 결과를 보면 company와 email의 경우 NULL 값을 가지는 반면 address의 경우는 비록 아무런 값도 지정해 주지 않았지만 Republic of Korea라는 기본값이 들어가는 것을 확인 할 수 있다.

이처럼 레코드 INSERT 시 값을 생략한 경우 Table 생성에 적용한 Default 제약조건에 의해 기본값이 해당 컬럼값으로 들어가게 된다. 그러나 생략이 아닌 명시적인 NULL값을 해당 컬럼값으로 지정하는 경우에는 기본값이 아닌 NULL이 입력되므로 주의할 필요가 있다.

 

 

sqlite> INSERT INTO nameCard (name, mobile, address)    ...> VALUES ('Micky', '010-1111-2222', NULL); sqlite> SELECT * FROM nameCard; id          name        mobile         address            company     email      ----------  ----------  -------------  -----------------  ----------  ---------- 1           Jmini       010-1234-5678  Republic of Korea  NULL        NULL       2           Micky       010-1111-2222  NULL               NULL        NULL

 

• UNIQUE 제약조건

 

테이블의 특정 컬럼에 입력되는 데이터가 유일한 값이 되어야 하는 경우 우리는 UNIQUE 제약 조건을 사용할 수 있다. 만약 UNIQUE 제약조건이 설정되어 있는 컬럼에 중복 데이터가 입력되는 경우 에러를 발생시켜 중복값이 입력되지 못하도록 하는 것이다. 보통 UNIQUE 제약조건은 NOT NULL 제약조건과 함께 사용 가능하니 필요시 이 두개를 함께 쓸 수 있다.

(SQLite3에서는 NULL 값의 경우 UNIQUE 제약조건에 영향을 받지 않고 중복해서 입력 가능)

 

사용법은 Table 생성 시 해당 제약조건을 설정하고자 하는 컬럼 뒤에 UNIQUE 키워드를 추가하는 것이다. 예제를 통해 한번 살펴보도록 하자.

 

sqlite> DROP TABLE nameCard; sqlite> CREATE TABLE nameCard(    ...> id INTEGER PRIMARY KEY AUTOINCREMENT,    ...> name TEXT NOT NULL,    ...> mobile TEXT NOT NULL,    ...> address TEXT DEFAULT 'Republic of Korea',    ...> company TEXT,    ...> email TEXT NOT NULL UNIQUE    ...> );

 

이번에는 email 컬럼에 NOT NULL 제약조건을 추가하였고 그와 함께 UNIQUE 제약조건도 함께 넣었다. 다른 사람의 email이 서로 같은 경우는 없을테니 말이다.

 

이제 한번 값을 입력해 보도록 하자.

 

sqlite> INSERT INTO nameCard (name, mobile, email)    ...> VALUES ('Mini', '010-1234-5678', 'mini@test.com'); sqlite> SELECT * FROM nameCard;   id          name        mobile         address            company     email         ----------  ----------  -------------  -----------------  ----------  ------------- 1           Mini        010-1234-5678  Republic of Korea  NULL        mini@test.com   sqlite> INSERT INTO nameCard (name, mobile, email)    ...> VALUES ('Mickey', '010-1224-5338', 'mini@test.com');   Error: UNIQUE constraint failed: nameCard.email

 

Mini라는 이름 정보의 레코드를 입력하였고 email 값으로 'mini@test.com'을 넣었다. 그 이후에 Mickey라는 이름 정보를 가지는 레코드를 입력하였는데 이 때 email 값으로 이전에 넣은 'mini@test.com' 값을 동일하게 넣었다. 그리고 우리는 그 결과로써 UNIQUE constraint fialed Error가 발생함을 확인 할 수 있다.

 

추가로 UNIQUE 제약조건을 설정하는 방법 중 다른 하나는 Table 생성 시 모든 컬럼을 정의한 후 마지막에 UNIQUE 제약조건을 별도로 설정해 주는 것이다.

 

sqlite> DROP TABLE nameCard; sqlite> CREATE TABLE nameCard(    ...> id INTEGER PRIMARY KEY AUTOINCREMENT,    ...> name TEXT NOT NULL,    ...> mobile TEXT NOT NULL,    ...> address TEXT DEFAULT 'Republic of Korea',    ...> company TEXT,    ...> email TEXT NOT NULL,    ...> UNIQUE(email)    ...> );

 

또한 CONSTRAINT 키워드를 사용해서 별도의 이름을 가지는 UNIQUE 제약조건을 설정할 수 있다.

 

sqlite> DROP TABLE nameCard; sqlite> CREATE TABLE nameCard(    ...> id INTEGER PRIMARY KEY AUTOINCREMENT,    ...> name TEXT NOT NULL,    ...> mobile TEXT NOT NULL,    ...> address TEXT DEFAULT 'Republic of Korea',    ...> company TEXT,    ...> email TEXT NOT NULL,    ...> CONSTRAINT unique_email UNIQUE(email)    ...> );

 

마지막으로 두 개 이상의 컬럼을 묶어 UNIQUE 제약조건을 설정하는 방법에 대해서 살펴보도록 하자. 사실 name의 경우는 동명이인이 있을 수 있으니 중복값이 들어갈 수 있다. 그러나 mobile과 name이 같은 경우는 없으니 (만약 있다면 이는 동명이인이 아닌 동일 인물) 해당 두 컬럼을 묶어 하나의 UNIQUE 제약조건을 설정하는 것이다. 방법은 간단하다. UNIQUE 키워드 뒤 ()괄호 안에 두 컬럼을 모두 명시하는 것이다.

 

sqlite> INSERT INTO nameCard (name, mobile, email)    ...> VALUES ('Mini', '010-1234-1234', 'Mini2@test.com');   sqlite> INSERT INTO nameCard (name, mobile, email)    ...> VALUES ('Mini', '010-1234-5678', 'Mini3@test.com');   sqlite> INSERT INTO nameCard (name, mobile, email)    ...> VALUES ('Mini', '010-1234-5678', 'Mini4@test.com');   Error: UNIQUE constraint failed: nameCard.name, nameCard.mobile

 

위 결과를 보면 알 수 있듯이 name만 같은 경우에 데이터 입력에 전혀 문제가 없음을 알 수 있다. 그러나 name과 mobile 값이 모두 같은 경우에는 UNIQUE constraint error가 발생함을 알 수 있다.

 

 

• CHECK 제약조건

 

CHECK 제약 조건은 특정 조건이 참인 경우에만 테이블에 레코드를 입력/수정 할 수 있도록 해주는 제약 조건이다. 예를 들어 email 컬럼값에는 항상 "@"가 들어가 있어야 한다고 가정해 보자. 실제로 많은 email을 계정으로 이용하는 웹서비스의 경우는 @의 유무를 체크하기도 한다. 우리는 email 값을 넣을 때 '@'가 포함되어 있지 않으면 에러를 발생시키도록 할 것이다. CHECK 키워드를 테이블 생성 시 컬럼 정의 하는 곳 뒤에 위치하여 해당 제약 조건을 설정해 보도록 하겠다.

 

sqlite> DROP TABLE nameCard ; sqlite> CREATE TABLE nameCard(    ...> id INTEGER PRIMARY KEY AUTOINCREMENT,    ...> name TEXT NOT NULL,    ...> mobile TEXT NOT NULL,    ...> address TEXT DEFAULT 'Republic of Korea',    ...> company TEXT,    ...> email TEXT CHECK (email like '%@%'),    ...> CONSTRAINT unique_email UNIQUE(email)    ...> );

 

위와 같이 WILE CARD를 이용하여 "@"을 포함하는 email 값만 입력 및 수정이 되도록 하는 CHECK 제약 조건을 설정해 주었다. 그리고 이렇게 생성된 테이블과 컬럼에 이 제약 조건이 잘 적용되었는 지 확인해 보도록 하자.

 

sqlite> INSERT INTO nameCard (name, mobile, email)    ...> VALUES ("Mini", "010-1234-5678", "eMini");   Error: CHECK constraint failed: nameCard   sqlite> INSERT INTO nameCard (name, mobile, email)    ...> VALUES ("Mini", "010-1234-5678", "eMini@test.com"); sqlite> SELECT * FROM nameCard;   id          name        mobile         address            company     email          ----------  ----------  -------------  -----------------  ----------  -------------- 1           Mini        010-1234-5678  Republic of Korea  NULL        eMini@test

 

예상한 대로 "@"이 포함되지 않은 경우에는 CHECK constraint failed가 발생하는 것을 알 수 있다.

이는 INSERT 뿐 아니라 UPDATE시에도 동일하게 적용되는 제약조건임을 명시 하자.

그리고 위와 같은 CHECK 제약조건 설정 대신 우리는 UNIQUE 제약조건과 동일하게 해당 CHECK 절을 따로 분리하여 독립적으로 사용할 수 있다.

 

sqlite> DROP TABLE nameCard ; sqlite> CREATE TABLE nameCard(    ...> id INTEGER PRIMARY KEY AUTOINCREMENT,    ...> name TEXT NOT NULL,    ...> mobile TEXT NOT NULL,    ...> address TEXT DEFAULT 'Republic of Korea',    ...> company TEXT,    ...> email TEXT,    ...> CONSTRAINT unique_email UNIQUE(email),    ...> CHECK (email like '%@%')    ...> );