<SQL Server> 09. 테이블과 뷰 - 2

테이블과 뷰 - 2

제약 조건

제약 조건이란 데이터의 무결성을 지키기 위한 제한된 조건을 의미한다. 즉, 특정 데이터를 입력할 때 무조건적으로 입력되는 것이 아닌, 어떤 조건을 만족했을 때에만 입력되도록 제약할 수 있다.

간단한 예로 인터넷 쇼핑몰에 회원 가입을 할 때, 동일한 주민등록번호로 다시 가입을 한다면 가입이 안될 것이다. 그 이유는 주민등록번호 열은 동일한 것이 들어갈 수 없는 조건이 설정되어 있기 때문이다.

SQL Server는 데이터의 무결성을 위해서 다음 6가지 제약 조건을 제공한다.

•

PRIMARY KEY 제약 조건

•

FOREIGN KEY 제약 조건

•

UNIQUE 제약 조건

•

CHECK 제약 조건

•

DEFALUT 정의

•

NULL 값 허용

기본 키 제약 조건

테이블에 있는 많은 행의 데이터를 구분할 수 있는 식별자를 ‘기본 키Primary Key’라고 부른다. 예를 들어, 회원 테이블의 회원 아이디, 학생 테이블의 학번 등이 이에 해당된다.

기본 키에 입력되는 값은 중복될 수 없으며, NULL 값이 입력될 수 없다. 인터넷 쇼핑몰에 대한 예를 들면, 대부분의 인터넷 쇼핑몰에서는 회원 테이블의 기본 키를 회원 아이디로 설정해 놓았을 것이다.

설계 방법에 따라서 회원 아이디가 기본 키가 아닐 수도 있다. 회원 아이디가 아닌 주민등록번호나 이메일 또는 휴대전화 번호로 회원을 구분하는 사이트도 많이 있다.

기본 키는 테이블에서 중요한 의미를 갖는다. 우선, 기본 키로 생성한 것은 자동으로 클러스터형 인덱스가 생성된다. 또한, 테이블에서는 기본 키를 하나 이상의 열에 설정할 수 있다. 즉, 회원 아이디처럼 하나의 열에만 기본 키를 설정할 수도 있고, 두 개의 열을 합쳐서 기본 키로 설정할 수도 있다.

대부분의 테이블은 기본 키를 가져야 한다. 물론, 기본 키가 없이도 테이블의 구성은 가능하지만, 실무적으로 대부분의 테이블에는 기본 키를 설정해 줘야 한다.

기본 키를 생성하는 방법은 CREATE TABLE 문에 PRIMARY KEY라는 예약어를 넣어주면 된다.

CREATE TABLE userTbl ( userID char(8) NOT NULL PRIMARY KEY, name nvarchar(10) NOT NULL, -- 중간 생략 --

SQL

이렇게 설정함으로 회원 아이디(userID)는 회원 테이블(userTbl)의 기본 키가 되었으며, 앞으로 입력되는 회원 아이디는 당연히 중복될 수도 없고, 비어(NULL)있을 수도 없다.

모든 제약 조건은 이름을 가지게 되는데, 이렇게 CREATE TABLE 구문 안에서 기본 키를 지정하게 되면 제약 조건의 이름은 SQL Server가 알아서 설정해주게 된다.

아마도 PK__userTbl__CB9A1CDFE820F8B5 이런 식으로 이름이 주어지게 된다. 이름만으로도 이것이 어떤 것인지 파악이 가능하다. 즉, ‘userTbl 테이블에 기본 키로 지정된 것’ 정도로 파악이 충분히 가능할 것이다. 그런데 이름만으로는 어느 열에 기본 키가 설정 되어있는지를 파악하기가 어렵다.

테이블의 정보를 보기 위해서는 sp_help 프로시저를 사용하면 된다.

EXEC sp_help userTbl;

SQL

SQL Server가 제약 조건 이름을 자동으로 붙여줌.

제일 마지막에서 제약 조건의 타입과 제약 조건 이름, 그리고 제약 조건이 설정된 열도 확인된다. 이름이 조금 어려우므로, 직접 PK_userTbl_userID와 같은 이름을 붙여주는 게 더 낫다. 즉, 이름만으로도 ‘PK가 userTbl 테이블의 userID 열에 지정됨’을 읽을 수 있다. 그러기 위해서는 아래와 같이 사용하면 된다.

CREATE TABLE userTbl ( userID char(8) NOT NULL CONSTRAINT PK_userTbl_userID PRIMARY KEY , name nvarchar(10) NOT NULL, --- 중간 생략---

SQL

다른 방법

1.

맨 마지막에 지정해줌

CREATE TABLE userTbl -- 회원 테이블 ( userID CHAR(8) NOT NULL, -- 사용자 아이디 name NVARCHAR(10) NOT NULL, -- 이름 birthYear INT NOT NULL, -- 출생년도 addr NCHAR(2), -- 지역(경기, 서울, 경남 식으로 2글자만 입력) mobile1 CHAR(3), -- 휴대폰의 국번(011, 016, 017, 018, 019, 010 등) mobile2 CHAR(8), -- 휴대폰의 나머지 전화번호(하이픈제외) height SMALLINT, -- 키 mDate DATE, -- 회원 가입일 CONSTRAINT PK_userTbl_userID PRIMARY KEY (userID) );

SQL

2.

테이블을 만든 뒤 ALTER TABLE 문으로 추가 지정

CREATE TABLE userTbl -- 회원 테이블 ( userID CHAR(8) NOT NULL, -- 사용자 아이디 name NVARCHAR(10) NOT NULL, -- 이름 birthYear INT NOT NULL, -- 출생년도 addr NCHAR(2), -- 지역(경기, 서울, 경남 식으로 2글자만 입력) mobile1 CHAR(3), -- 휴대폰의 국번(011, 016, 017, 018, 019, 010 등) mobile2 CHAR(8), -- 휴대폰의 나머지 전화번호(하이픈제외) height SMALLINT, -- 키 mDate DATE, -- 회원 가입일 ); GO ALTER TABLE userTbl ADD CONSTRAINT PK_userTbl_userID PRIMARY KEY (userID) GO

SQL

CREATE TABLE 안에 PRIMARY KEY 문으로 설정한 것과 동일한 결과다.

쉽게 알 수 있겠지만 다음과 같은 정도로 해석할 수 있다.

•

ALTER TABLE userTbl : userTbl을 변경하자.

•

ADD CONSTRAINT PK_userID : 제약 조건을 추가하자. 추가할 제약 조건 이름은 ‘PK_userTbl_userID’ 이다.

•

PRIMARY KEY (userID) : 추가할 제약 조건은 기본 키 제약 조건이다. 그리고 제약 조건을 설정할 열은 userID 열이다.

기본 키는 각 테이블 별로 하나만 존재해야 하지만, 기본 키를 하나의 열로만 구성해야 하는 것은 아니다. 필요에 따라서 두 개 또는 그 이상의 열을 합쳐서 하나의 기본 키로 설정하는 경우도 종종 있다.

아래와 같은 간단한 ‘제품 테이블’을 생각해보자.

만약 제품코드가 AAA가 냉장고, BBB가 세탁기, CCC가 TV라고 가정한다면 현재 제품코드만으로는 중복될 수밖에 없으므로, 기본키로 설정할 수가 없다. 또한, 제품일련번호도 마찬가지로 각 제품별로 0001번부터 부여하는 체계라서 기본 키로 사용할 수 없다.

이러한 경우에는 ‘제품코드 + 제품일련번호’를 합친다면 유일한 값이 될 수 있으므로 기본 키로 사용할 수 있다.

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) ; GO

SQL

또는 CREATE TABLE 구문 안에 직접 사용할 수도 있다.

DROP TABLE prodTbl; GO CREATE TABLE prodTbl ( prodCode NCHAR(3) NOT NULL, prodID NCHAR(4) NOT NULL, prodDate SMALLDATETIME NOT NULL, prodCur NCHAR(10) NULL, CONSTRAINT PK_prodTbl_proCode_prodID PRIMARY KEY (prodCode, prodID) );

SQL

마지막 열 이후에 콤마(,)로 분리하고 제약 조건을 직접 지정할 수 있다.

EXEC sp_help prodTbl로 테이블의 정보를 확인하면 두 열이 합쳐져서 하나의 기본 키 제약 조건을 설정하고 있음이 확인된다.

또한, 개체 탐색기에서 해당 테이블을 선택한 후, 마우스 오른쪽 버튼을 클릭하고 [디자인]을 선택해서 확인하면 열쇠 모양이 두 개의 열에 표시되어 있다.

외래 키 제약 조건

외래 키Foreign Key 제약 조건은 두 테이블 사이의 관계를 선언함으로써, 데이터의 무결성을 보장해주는 역할을 한다. 외래 키 관계를 설정하면 하나의 테이블이 다른 테이블을 의존하게 된다.

외래 키를 생성하는 방법은 CREATE TABLE 내부에 FOREIGN KEY 키워드로 설정하는 방법이 있다.

DROP TABLE buyTbl, userTbl; GO CREATE TABLE userTbl ( userID char(8) NOT NULL PRIMARY KEY, name nvarchar(10) NOT NULL, --- 중간 생략 --- ); GO CREATE TABLE buyTbl ( num int IDENTITY(1,1) NOT NULL PRIMARY KEY , userid char(8) NOT NULL FOREIGN KEY REFERENCES userTbl(userID), prodName nchar(6) NOT NULL, --- 중간 생략 ---

SQL

만약, 기준 테이블에 PRIMARY KEY 또는 UNIQUE가 아니라면 외래 키 관계는 설정되지 않는다.

마찬가지로 직접 외래 키의 이름을 지정하기 위해서는 아래와 같이 사용하면 된다.

DROP TABLE buyTbl; GO CREATE TABLE buyTbl ( num int IDENTITY(1,1) NOT NULL PRIMARY KEY , userid char(8) NOT NULL CONSTRAINT FK_userTbl_buyTbl FOREIGN KEY REFERENCES userTbl(userID), prodName nchar(6) NOT NULL, --- 중간 생략 ---

SQL

외래 키 역시 제일 아래에 정의할 수도 있다.

DROP TABLE buyTbl; GO CREATE TABLE buyTbl ( num int IDENTITY(1,1) NOT NULL PRIMARY KEY , userid char(8) NOT NULL , prodName nchar(6) NOT NULL , groupName nchar(4) , price int NOT NULL, amount smallint NOT NULL, CONSTRAINT FK_userTbl_buyTbl FOREIGN KEY REFERENCES userTbl(userID) );

SQL

만약 외래 키의 이름을 지정할 필요가 없다면 제일 마지막 행에 간단히

‘FOREIGN KEY(userID) REFERENCES userTbl(userID)’만 써줘도 된다.

참고로, 이 예에서는 기준 테이블의 열 이름(userID)과 외래 키 테이블의 열 이름(userID)이 동일하지만 반드시 그래야 하는 것은 아니며 달라도 관계는 없다. 즉, buyTbl의 userID 열 이름이 myID 등으로 기준 테이블의 userID와 달라도 상관없다.

열 이름은 대소문자를 안가린다. 즉 userID와 USERid는 같은 이름이다.

또, 다른 방법으로는 ALTER TABLE 구문을 이용하는 것이다.

DROP TABLE buyTbl, userTbl; GO CREATE TABLE userTbl ( userID char(8) NOT NULL PRIMARY KEY, name nvarchar(10) NOT NULL, --- 중간 생략 --- ); GO CREATE TABLE buyTbl ( num int IDENTITY(1,1) NOT NULL PRIMARY KEY, userid char(8) NOT NULL , prodName nchar(6) NOT NULL, --- 중간 생략 --- GO ALTER TABLE buyTbl ADD CONSTRAINT FK_userTbl_buyTbl FOREIGN KEY (userID) REFERENCES userTbl (userID);

SQL

외래 키의 옵션 중에 ON DELETE CASCADE 또는 ON UPDATE CASCADE 옵션이 있는데, 이 옵션은 기준 테이블의 데이터가 변경되었을 때 외래 키 테이블도 자동으로 적용되도록 설정해준다.

예로, ON UPDATE CASCADE로 설정하면 회원 테이블의 김범수의 ID인 KBS가 Kim으로 변경될 경우에, 구매 테이블의 KBS도 Kim으로 자동 변경된다.

ALTER TABLE buyTbl ADD CONSTRAINT FK_userTbl_buyTbl FOREIGN KEY (userID) REFERENCES userTbl (userID) ON UPDATE CASCADE;

SQL

별도로 지정하지 않으면 ‘ON UPDATE NO ACTION’ 및 ‘ON DELETE NO ACTION’을 지정한 것과 동일하다. 즉, 회원 테이블의 회원 아이디가 변경되어도 아무런 일이 일어나지 않는다는 의미다.

Made with