제약조건

SQL에서 제약조건이란 데이터의 무결성을 지키기 위한 장치라고 보면 됩니다.

데이터베이스가 엑셀이나 워드같은 프로그램과 가장 큰 차이점을 보이는 부분이라고 볼수도 있습니다.

예를 들어 쇼핑몰에서 회원가입을 할 때 주민번호를 입력하는 부분이 있는데 이 주민번호를 입력하는 부분은 중복된 데이 터가 들어가면 안되겠죠. 그래서 이 주민번호를 입력하는 컬럼에 제약조건을 만들 수 있습니다.

지금은 회원가입시 주민번호를 사용하지 않기 때문에 예가 적절치 않을 수도 있으나 주민번호가 아니더라도 닉네임이나 휴대전화번호, 메일주소등은 모두 제약조건이 필요한 컬럼입니다. 이런 컬럼들도 역시 중복된 데이터가 들어가면 안되기 때문에 테이블 생성시에 제약조건을 만들어서 관리하는 것이 필수적으로 필요한 부분이라고 할 수 있습니다.

그러면 테이블을 생성할때 제약조건을 생성하는 방법을 알아보도록 하겠습니다.

 

제약조건은 사실 한가지만 있는 게 아니라 기본키(Primary Key) 제약조건, 외래키(Foreign Key) 제약조건, 유니크 (Unique) 제약조건 체크(Check) 제약조건, 디폴트(Default) 정의등 여러가지 경우가 있습니다.

기본키(Primary Key) 제약조건

기본키 제약조건은 테이블에서 행의 데이터를 구분할 수 있는 식별자를 가리킵니다.

회원테이블의 회원 아이디, 상품테이블의 상품코드등이 기본키가 될 수 있는데, 기본키로 생성한 열에는 자동적으로 클러 스터형 인덱스가 생성됩니다. 인덱스에 대한 내용은 따로 인덱스편에서 설명드리도록 하겠습니다.

기본키를 생성하는 방법은 테이블 생성시에 Primary Key 라는 예약어를 넣어주어도 되고, ALTER TABLE을 이용해서 이 미 만들어진 테이블에 추가하는 것도 가능합니다.

그러면 테이블 생성시에 만드는 방법은 먼저 설명드리겠습니다.

아래와 같이 테이블 생성시에 컬럼에 Primary Key를 지정해 주기만 하면 됩니다.

CREATE TABLE Member2 ( UserID nvarchar(10) NOT NULL PRIMARY KEY, Name nvarchar(20) NOT NULL, HP nvarchar(13) NULL )

GO

 

위와 같은 내용을 쿼리창에서 입력후에 실행하면 Member2 테이블에 키와 인덱스가 자동적으로 생성되는데 이런식으로 입력을 하면 키와 인덱스를 SQL Sever가 알아서 설정해 주는데 이렇게 생성된 키와 인덱스는 어떤 이름으로 생성되었는 지 알아보기가 어렵습니다. 그렇기 때문에 키와 인덱스명을 개발자가 직접 지정해주는 것이 좋습니다.

 

 

SQL SERVER가 자동적으로 생성한 키와 인덱스(인덱스가 클러스형이라는 것도 확인 가능함)

그래서 기본키를 생성하면서 키의 명칭을 직접 지정하려면 아래와 같이 입력하면 됩니다.

DROP TABLE Member2

GO

CREATE TABLE Member2 ( UserID nvarchar(10) NOT NULL CONSTRAINT

PK_UserID PRIMARY KEY, Name nvarchar(20) NOT NULL, HP nvarchar(13) NULL )

GO

 

테이블을 생성하면서 기본키의 명칭 을 사용자가 직접 지정한 경우

아래의 경우는 ALTER TABLE을 이용해서 이미 생성된 테이블에 기본키 제약조건을 추가하는 방법입니다.

DROP TABLE Member2

GO

CREATE TABLE Member2 ( UserID nvarchar(10) NOT NULL, Name nvarchar(20) NOT NULL, HP nvarchar(13) NULL ) 

GO 

ALTER TABLE Member2 ADD CONSTRAINT PK_UserID -- 개발자가 직접 지정한 키 명칭 PRIMARY KEY(UserID) -- 기본키를 UserID에 지정한다.

GO

기본키 제약조건은 하나의 컬럼에만 지정해야만 하는 것은 아닙니다. 2개의 컬럼을 합쳐서 기본키로 지정해 줄수도 있습 니다.

이렇게 1개 이상의 컬럼을 기본키로 지정해주는 경우는 그리 많지는 않지만 다국어버전의 데이터베이스인 경우 국가코드 와 상품코드를 합쳐서 기본키로 지정해 줄수 있습니다. 이외에도 한 프로그램을 여러 사용자가 개별적으로 이용할 경우 아 이디와 상품코드를 묶어서 기본키로 지정해주는 경우도 있습니다.

2개의 컬럼을 합쳐서 기본키로 지정해주려면 아래와 같이 입력합니다.

CREATE TABLE Product ( ProID nvarchar(5) NOT NULL, ProCode nvarchar(10) NOT NULL, ProDate smalldatetime NOT NULL, CONSTRAINT PK_ProID_ProCode PRIMARY KEY (ProID, ProCode) )

 외래키(Foreign Key) 제약조건

외래키 제약조건은 두 테이블간의 관계를 선언해서 데이터의 무결성을 유지해주는 역할을 합니다.

외래키를 선언한 컬럼은 다른 테이블의 Primary Key로 설정된 컬럼이나, Unique 제약조건이 지정된 컬럼만 선언할 수 있 습니다. 간혹 외래키를 문법에 맞게 설정했는데도 오류가 난다면 이 부분 때문에 오류가 난다고 알아두시면 됩니다.

 

외래키를 만드는 이유는 데이터의 무결성을 유지하기 위해서라고 간단히 설명했지만, 좀 더 자세히 설명하자면 예를 들어 고객테이블과 주문 테이블이 있다고 가정했을 경우, 고객의 주소는 고객테이블에 있고 주문 테이블에는 상품, 주문수량, 금액등이 있을 수 있습니다. 이럴 경우 고객 테이블의 고유한 컬럼으로 사용할 수 있는 고객의 ID만 있으면 고객의 주소는 주문테이블에 따로 입력할 필요가 없이 고객의 ID만 외래키로 참조를 하면 되는 것입니다.

만약 외래키를 설정하지 않았다면 주문이 있을 때마다 매번 새로 고객의 주소를 반복해서 입력해야 하는 고생을 해야 합니 다. 그리고 데이터의 무결성도 보장하기 어렵게 됩니다.

외래키가 있는 테이블과 Primary 키가 있는 테이블은 조인(Join)이라는 방법으로 서로 연결할 수 있는데, 이 부분은 데이 터의 선택부분에서 다시 설명하도록 하겠습니다.

그럼 고객테이블과 주문테이블을 만들어 보면서 외래키 제약조건을 생성해 보도록 하겠습니다.

CREATE TABLE tblUser ( ID nvarchar(10) NOT NULL PRIMARY KEY, Addr nvarchar(100) NOT NULL )

GO

CREATE TABLE tblOrder ( Num INT IDENTITY(10001,1) NOT NULL, ID nvarchar(10) NOT NULL CONSTRAINT

FK_tblUser_tblOrder FOREIGN KEY REFERENCES tblUser(ID), OrderName nvarchar(20) NOT NULL )

GO 

 

tblOder 테이블의 ID 가 외래키 제약조건이 설정된 컬럼이고 키명칭은 FK_tblUser_tblOrder 이고 CONSTRAINT, FOREIGN KEY,

REFERENCES 는 모두 예약어이고 tblUser(ID) 가 외래키가 참조하는 테이블과 컬럼명을 가리킵니다.

키명칭은 꼭 예제와 같이 만들 필요는 없고 나중에 참고할때 쉽게 알아볼 수 있도록 사용자만의 원칙으로 만들면 관계 없 습니다.

외래키를 만들고 나면 아래와 같이 SSMS에서도 확인해 볼 수 있습니다.

 

 

외래키 제약조건은 테이블 생성시에만 만들 수 있는 것이 아니라 ALTER TABLE을 이용해서도 생성할 수 있습니다.

ALTER TABLE tblOrder ADD CONSTRAINT FK_tblUser_tblOrder FOREIGN KEY (ID) REFERENCES tblUser(ID)

GO

유니크(Unique) 제약조건

UNIQUE 제약조건은 중복되지 않는 유일한 값을 입력해야 하는 것은 Primary Key 제약조건과 같지만 다른 점은 NULL 값 을 허용한다는 점입니다. NULL 값도 한번만 허용되고, NULL 값을 두번 이상 입력하면 에러가 나면서 입력이 안되는 점에 유의해야 합니다.

UNIQUE 제약조건은 보통 기본키로 사용되지 않는 컬럼이면서 중복된 값을 입력하지 않아야 하는 곳에 사용할 수 있습니 다.

UNIQUE 제약조건의 사용 예를 들어보자면 데이터베이스마다 한명의 관리자가 있고, 이 한명의 관리자는 사용자가 아니 라면 이 관리자에게는 NULL 값을 설정할 수 있도록 해서 데이터 입력에 제한을 두지 않는 설정을 하는 경우를 들 수 있습니다.

그러면 UNIQUE 제약조건을 사용하는 방법을 알아보도록 하겠습니다.

CREATE TABLE member3 ( UserID nvarchar(8) NOT NULL PRIMARY KEY, Phone nvarchar(30) NULL CONSTRAINT Uni_Phone UNIQUE -- 제약조건과 키 명칭(Uni_Phone), UNIQUE(예약어) )

 

위와 같이 Phone 컬럼에 NULL 을 허용하면서 UNIQUE 제약조건을 사용해서 지정할 수 있습니다.

체크(Check) 제약조건

CHECK 제약조건은 입력되는 데이터에 제한을 거는 기능을 수행합니다. 예를 들어 쇼핑몰에서 옷의 규격을 입력하는 데 입력할 수 있는 범위를 지정하거나, 휴대전화의 처음 세자리를 지정하거나, 사람의 출생년도를 입력하는 곳에 입력할 수 있는 연도의 범위를 지정하는 등 여러가지 경우에 사용될 수 있습니다.

아래에 예제를 보여드리도록 하겠습니다.

CREATE TABLE Mobile ( hp1 nvarchar(3) NOT NULL ) 

GO -- 휴대전화의 국번을 CHECK 하는 제약조건 

ALTER TABLE Mobile ADD CONSTRAINT CK_hp1 CHECK (hp1 IN ('010','011','016','017','018','019'))

 

한가지 더 예제를 보여드리도록 하겠습니다.

-- 금액은 0원 이상이어야 한다. 

ALTER TABLE Sales ADD CONSTRAINT CK_Price CHECK (Price >= 0) 디폴트(Default) 정의

 

DEFAULT는 데이터를 입력하지 않았을 경우 자동적으로 입력되는 기본값을 정의할 때 사용합니다. 상당히 유용한 기능으 로 데이터를 입력할때 수고를 덜어주기도 하고, 예기치 않은 오류가 발생하는 것을 막아주기도 하는 굉장히 좋은 기능입니 다.

예를 들어 재고관리 프로그램을 운영하는데 사용자가 재고등록을 할 경우, 재고 등록날짜를 입력해야 하는데 깜박 잊고 재 고 등록날짜를 빼먹고 입력을 하고, 이 재고 등록날짜 컬럼에 Default 기능이 없었다면 NULL 값이 들어가 있을 테고, 재 고 등록날짜 컬럼이 날짜 형식의 자료형으로 설정되어 있었다면 차후에 통계를 내는 데 심각한 오류가 발생할 수도 있습니 다.

그리고 오류가 발생하는 부분과는 별개로 기본적으로 가장 많이 사용되는 값을 사용자에게 보여주면서, 그 값이 맞다면 굳 이 다시 입력할 필요가 없이 다른 부분만 입력을 하면 되기 때문에 입력의 편의성을 높여주기도 합니다.

하나 더 예를 들자면, 치킨공장을 운영하는 사용자가 있는데 자신의 치킨공장은 대전과 공주에 있고, 대전에서 80%, 공 주에서 20%를 생산하고, 배송지는 CJ용인지사에 80%, CJ성남지사 20% 납품을 한다고 하면, 대전과 CJ 용인지사를 Default 값으로 설정하는 것이 좋은 방법이겠죠.

아래에 예제를 보여드리도록 하겠습니다.

-- Default 정의 사용 CREATE TABLE Setup ( SetupID INT IDENTITY(10001, 1) PRIMARY KEY, Place NVARCHAR(100) DEFAULT ('대전'), DTInsert smalldatetime default(getdate()), )

 

위의 예제에서 getdate() 는 오늘 날짜를 자동적으로 입력하게 해주는 SQL 함수입니다. Default 정의는 ALTER TABLE을 이용할 수도 있는데 이때는 FOR 문을 사용해야 합니다.

ALTER TABLE Setup ADD CONSTRAINT DT_Date DEFAULT getdate() FOR DTInsert