DAY17(3) - COLUMN 속성(무결성 제약조건)

📌 1. COLUMN 속성

• DB의 각 COLUMN에는 무결성을 보장하기 위한 속성을 지정할 수 있다.
• 무결성이란 데이터의 정확성, 일관성을 보장하기 위해 저장, 삭제, 수정 등을 제약하기 위한 조건으로, 주요 목적은 데이터베이스의 저장된 데이터의 상태를 일관되게 유지하는 것이다.

📍 1-1. NOT NULL

• 해당 컬럼에 NULL값이 입력되지 못하게 하는 조건이다.
• 테이블을 생성할 때 NOT NULL을 붙이면 컬럼에서 NULL값을 허용하지 않게 되고, NULL을 붙이면 아무것도 붙이지 않을 때의 디폴트 설정과 동일하게 NULL을 허용한다.

  • CREATE TABLE null_test(
    	col1	varchar2(10) NOT NULL,
    	col2 	varchar2(10) NULL,
    	col3	varchar2(10)
    );

📍 1-2. UNIQUE

• 해당 컬럼에 중복된 값이 입력되지 못하게 하는 조건이다.

  • CREATE TABLE unique_test(
    	col1	varchar2(20) UNIQUE NOT NULL,
    	col2	varchar2(20) UNIQUE,
    	col3	varchar2(20) 
    	CONSTRAINT temp_unique unique(col2,col3)
    );

• CONSTRAINT를 사용해 TEMP_UNIQUE라는 사용자 지정 속성을 만들었다. 이와 같은 경우에는 col2뿐만 아니라, col2와 col3의 조합도 중복되어서는 안 된다.

  • INSERT INTO unique_test (col1,col2,col3)
    VALUES ('aa','bb','cc');
    INSERT INTO unique_test (col1,col2,col3)
    VALUES ('dd','ee','cc');
    // 다음과 같이 col3만 중복되는 것은 가능하나, col2와 col3의 조합은 unique해야 한다.

📍 1-3. CHECK

• 해당 컬럼에 정해진 값만 허용하는 조건이다.

  • CREATE TABLE check_test(
    	gender	varchar2(10) NOT NULL
    	CONSTRAINT check_gender CHECK (gender IN ('여성','남성'))
    );
    // gender 컬럼에서 '여성','남성'이외의 값을 가질 수 없게 된다.

📍 1-4. PRIMARY KEY

• NOT NULL + UNIQUE + INDEX를 전부 포함하는 조건이다. 기본키, 식별자, PK 등으로 불린다.

• 해당 테이블의 핵심이 되는 값을 갖는 컬럼에 지정하며, 한 테이블당 하나만 지정할 수 있다. 다만 복수의 컬럼을 기본키로 만들 수는 있다.

• 선언방법

  • 컬럼 선언 뒤에 한 줄로 붙여서 지정하는 방법을 가장 많이 사용한다.

  CREATE TABLE constTest(
  	pkCol1 varchar2(10) PRIMARY KEY
  );

  • 컬럼 선언 뒤에 CONSTRAINT로 지정할 수도 있다.

  CREATE TABLE constTest(
  	pkCol2 varchar2(10) CONSTRAINT pk_test1 PRIMARY KEY
  );

  • 컬럼을 먼저 선언한 뒤 아웃라인에서 괄호로 명시해 지정하는 방법이다.

  CREATE TABLE constTest(
  	pkCol3 varchar2(10),
  	CONSTRAINT pk_test2 PRIMARY KEY (pkCol3)
  );

📍 1-5. FOREIGN KEY

• 다른 테이블의 컬럼을 참조해서 무결성을 검사하는 조건이다. 외부키, 외래키, 참조키, FK, 외부식별자 등으로 불린다.

• 외래키를 등록하면 참조 키를 갖는 테이블의 자식 테이블이 된다. 외래 키를 지정한 컬럼에서는 참조하는 부모 테이블의 컬럼에 포함된 값만 가질 수 있으며, 반드시 데이터 타입이 일치해야 한다. 참조되는 컬럼은 기본키나 UNIQUE 컬럼만 가능하다.

• 선언방법

  • 인라인 선언방법

  CREATE TABLE FOREIGN_KEY(
  	DEPARTMENT_ID varchar2(10)
  	CONSTRAINT dept_fk REFERENCES departments(department_id),
  	NAME			varchar2(10),
  	SALARY			number(10)
  );

  • 아웃라인 선언방법

  CREATE  TABLE FOREIGN_KEY(
  	DEPARTMENT_ID 	varchar2(10)
  	NAME			varchar2(10),
  	SALARY			number(10),
  	CONSTRAINT dept_fk FOREIGN KEY department_id
  	REFERENCES departments(department_id)
  );

• 부모 테이블은 자식의 데이터나 테이블 삭제에 영향을 받지 않는다.

• 해당 값을 참조하는 자식 테이블 데이터가 있으면 부모 테이블에서 그 값을 갖는 컬럼을 먼저 삭제할 수 없는 것이 기본이다. 외래키를 설정할 때 REFERENCES 절 뒤에 삭제 옵션을 붙여 주면 이 설정을 다르게 할 수 있다.

  • on delete cascade
    • 참조되는 부모 테이블의 행에 대한 delete를 허용한다.
      참조되는 부모테이블 값이 삭제되면 자식테이블의 값이 함께 삭제된다.
  • on delete set null
    • 참조되는 부모테이블의 행에 대한 delete를 허용한다.
      cascade와 달리 부모테이블의 값이 삭제되면 해당 값을 참조하는 자식테이블의 값들이 null값으로 설정된다.