[ SQL ] 무결성 제약조건 (기본키, 고유키, 외래키, NOT NULL, CHECK)

하루입니다.

 

 

---

 

 

오늘 공부할 것은 무결성 제약조건과 데이터 모델링.

 

무결성 제약조건

• 무결성의 뜻 : 데이터의 정확성
• 무결성 제약조건의 뜻 : 데이터의 정확성을 위해 테이블에 유효하지 않은 데이터가 입력되는 것을 방지하는 것. 

 

 

 

 

 

제약조건의 정의

제약조건의 정의는 두 가지 방식으로 할 수 있다.

 

1. 컬럼 레벨 제약조건 정의

• 굳이 별칭을 붙이는 것은 별칭을 붙이지 않으면 제약조건 이름이 SYS U3359444 이런 식으로 뜬다. 찾기 어려움.

CREATE TABLE 테이블명 (
    컬럼명 데이터타입 [CONSTRAINT 제약조건별칭] 제약조건,
    컬럼명 데이터타입 [CONSTRAINT 제약조건별칭] 제약조건,
    ...
);

 

 

2. 테이블 레벨 제약조건 정의

• 하나 이상의 컬럼을 조합하여 제약조건을 정의할 수 있다.
• NOT NULL 제약조건을 제외한 모든 제약조건을 정의할 수 있다.
• 보통 NOT NULL은 컬럼레벨에서, 나머지는 테이블 레벨에서 한다.

  CREATE TABLE 테이블명 (
    컬럼명 데이터타입,
    컬럼명 데이터타입,
    ...,
    [CONSTRAINT 제약조건별칭] 제약조건 (컬럼명, 컬럼명, ... )
  );

 

종류

• NOT NULL

해당 컬럼에 NULL값이 입력되지 못하게 한다.

예를 들어 고객 아이디, 비밀번호, 상품이름, 상품번호, 가격 등.

 

제약조건 정의 방식

  CREATE TABLE users (
    user_name varchar2(30) NOT NULL,
    user_tel varchar2(20) CONSTRAINT user_tel_nn NOT NULL,
    ...
  );

 

 

• UNIQUE (고유키)

 고유한 값. 즉 중복되지 않는 값. 하지만 NULL이 들어가는 건 가능하지. 왜냐? NULL은 값이 결정되지 않은 거고, 서로 비교도 되지 않는 값이니까.

 한 테이블 안에 여러 개 지정 가능하다.

 예를 들어 유저 이메일 같은 거.

CREATE TABLE USER (
    user_name varchar2(30),
    user_tel  varchar2(20) CONSTRAINT user_tel_nn NOT NULL,
    user_email varchar2(200) CONSTRAINT user_email_uk UNIQUE,
    ... ,
    CONSTRAINT user_tel_uk UNIQUE(user_tel)
);

코드 설명

 user_tel의 NN은 컬럼 레벨에서, UK는 테이블 레벨에서 정의했다 

 user_email의 UK는 컬럼 레벨에서 정의했다. NULL값이 허용된다.

 

 

• PRIMARY KEY (기본키)

테이블의 각 행을 고유하게 식별할 수 있는 값이다.

테이블 전체에서 고유한 값이어야 하며, null값도 허용하지 않는다.

예를 들어 학번, 사번, 과목코드, 송장번호. 시퀀스로 생성한 고유번호를 사용하기도 함.

CREATE TABLE users(
    user_no number(10) CONSTRANINT user_no_pk PRIMARY KRY,
    ...
);

CREATE TABLE course_registerations (
    stud_no number(10),
    course_no number(5),
    ...,
CONSTRAINT course_regist_pk PRIMARY_KEY(stud_no, course_no)
);

-- 이건 뭐냐면 수강신청을 한다 했을 때 (학생번호, 코스번호)가 중복되면 안된다는 거임.

 

 

 

• FOREIGN KEY (외래키, 참조키)

 해당 컬럼의 값은 다른 혹은 같은 테이블의 특정 컬럼과 관련된 값만 가져와야 한다 = 특정 컬럼 안에 있는 값이어야 한다.

 종속 테이블(자식 테이블) : FOREING KEY로 설정된 컬럼은 부모테이블의 기존 값과 일치하거나 NULL이어야 한다.

 참조 테이블(부모 테이블) : 자식테이블이 참조하는 부모테이블의 컬럼은 PRIMARY KEY 혹은 UNIQUE 제약조건이 정의된 컬럼이어야 한다.

 자식테이블에서 참조하고 있는 부모테이블의 행은 삭제할 수 없다.

 예를 들어 직원테이블의 부서ID는 부서테이블의 부서ID를 참조한다. (부서ID는 하나, 그걸 사용하는 직원들은 여러명)

 부서테이블의 부서ID행을 삭제하기 위해서는 직원테이블의 부서ID를 우선삭제해야 한다.

 

  CREATE TABLE emps(
    ...
    dept_id number(10) CONSTRAINT emp_dept_id_fk REFERENCES depts(dept_id),
    외래키  데이터타입 CONSTRAINT       별칭     REFERENCES 테이블명(부모키)
    ...
  );

  CREATE TABLE emps(
    ...
    dept_id number(10),
    ...,
    CONSTRAINT emp_dept_id_fk FOREIGN KEY (dept_id) REFERENCES depts(dept_id),
    CONSTRAINT       별칭     FOREIGN KEY(외래키)   REFERENCES 테이블명(부모키)
  );

코드 설명

 emps 테이블의 dept_id 컬럼의 값은 depts테이블의 dept_id 값을 참조한다.
 REFERENCES 키워드와 참조대상테이블(컬럼명)을 지정한다.

 

 

• CHECK

제시된 조건을 만족하는 값을 가져야 한다.

타 컬럼과의 비교는 불가하다 (ex. 수정일 컬럼은 생성일 컬럼보다 시간이 뒤에 위치해야 한다 등)

 

  CREATE TABLE products (
    ...
    pro_price number(10) CONSTRAINT pro_price_ck CHECK (pro_price > 0),
    pro_sell varchar2(20) CONSTRAINT pro_sell_ck CHECK (pro_sell in ('판매중', '재고부족', '절판')),
    pro_discount number(4, 3) CONSTRAINT pro_discount_ck CHECK (pro_discount >= 0.0 and pro_discount <= 0.5),
  );

코드 설명

 상품가격은 0보다 커야 한다.

 상품판매상태는 판매중, 재고부족, 절판 중 하나여야 한다.

 상품할인률은 0보다 크고 0.5보다 작아야 한다.

 

 

 

---

 

 

 

잘못된 정보에 대한 지적은 언제나 환영입니다. 

 

복습 끝!