[SQL] 테이블 생성, 수정, 삭제

---

테이블 생성

 

CREATE TABLE table_name (
    column1 data_type [NOT NULL] [DEFAULT default_value],
    column2 data_type [NOT NULL] [DEFAULT default_value],
    ...
    [PRIMARY KEY (column_list)],
    [UNIQUE (column_list)],
    [FOREIGN KEY (column_list) REFERENCES other_table(other_column_list)]
        [ON DELETE option] [ON UPDATE option],
    [CONSTRAINT constraint_name] [CHECK (check_condition)]
);

여기서 []로 쌓인 부분은 생략 가능한 부분이다. SQL 질의문은 세미콜론(;)으로 문장의 끝을 표시하며 대소문자를 구분하지 않는다. 단, 가독성을 위해 SQL 키워드를 대문자로 쓰는 것이 좋다.

 

• 테이블을 구성하는 각 속성의 이름, 데이터 타입, 기본 제약 사항 정의

속성을 정의할 때는 속성의 데이터 타입과 NULL 허용 여부, DEFAULT 값 설정 여부를 결정한다.

NULL을 허용하지 않으려면 NOT NULL을 사용하면 되고, 기본 값을 설정하고 싶다면 DEFAULT를 사용하면 된다.

참고로 데이터 타입은 아래와 같다.

데이터 타입 키워드	의미
INT 또는 INTEGER	정수
SMALLINT	INT 보다 작은 정수
CHAR(n) 또는 CHARACTER(n)	길이가 n인 고정 길이의 문자열
VARCHAR(n) 또는 CHARACTER VARYING(n)	최대 길이가 n인 가변 길이의 문자열
NUMERIC(p, s) 또는 DECIMAL(p, s)	고정 소수점 실수 p는 소수점을 제외한 전체 숫자의 길이 s는 소수점 이하 숫자의 길이
FLOAT(n)	길이가 n인 부동 소수점 실수
REAL	부동 소수점 실수
DATE	연, 월, 일로 표현되는 날짜
TIME	시, 분, 초로 표현되는 시간
DATETIME	날짜와 시간

 

• 기본키 정의

기본키를 지정하는 것으로 PRIMARY KEY를 사용한다.

 

• 대체키 정의

대체키를 지정하는 것으로 UNIQUE를 사용한다.

대체키로 지정되는 속성의 값은 유일성을 가지며 기본키와 달리 NULL 값이 허용된다.

 

• 외래키 정의

외래키를 지정하는 것으로 FOREIGN KEY를 사용한다. 외래키가 어떤 테이블의 무슨 속성을 참고하는지는 REFERENCES를 통해 제시한다.

참조 무결성 제약조건 유지를 위해 참조되는 테이블에서 튜플 삭제 시 처리 방법을 ON DELETE를 통해 지정할 수 있다.

옵션 키워드	의미
ON DELETE NO ACTION	튜플 삭제 불가
ON DELETE CASCADE	관련 튜플을 함께 삭제
ON DELETE SET NULL	관련 튜플의 외래키 값을 NULL로 변경
ON DELETE SET DEFAULT	관련 튜플의 외래키 값을 DEFAULT로 변경

참조 무결성 제약조건 유지를 위해 참조되는 테이블에서 튜플 변경 시 처리 방법을 ON UPDATE를 통해 지정할 수 있다.

옵션 키워드	의미
ON UPDATE NO ACTION	튜플 변경 불가
ON UPDATE CASCADE	관련 튜플에서 외래키 값을 함께 변경
ON UPDATE SET NULL	관련 튜플의 외래키 값을 NULL로 변경
ON UPDATE SET DEFAULT	관련 튜플의 외래키 값을 DEFAULT로 변경

 

• 데이터 무결성을 위한 제약조건 정의

데이터 무결성 제약조건을 CHECK을 통해 지정할 수 있다.

예를 들어 재고량이라는 속성은 항상 $ 0 $ 이상, $ 1000 $ 이하여야 한다면 CHECK (재고량 >= 0 AND 재고량 <= 1000) 으로 설정하여 제약조건을 설정해주는 것이다.

 

---

테이블 변경

 

• 새로운 속성(칼럼) 추가

ALTER TABLE table_name
ADD column_name data_type [NOT NULL] [DEFAULT default_value];

테이블 생성 부분과 마찬가지로 여기서 []로 쌓인 부분은 생략 가능한 부분이다.

 

• 기존 속성(칼럼) 삭제

ALTER TABLE table_name
DROP COLUMN column_name;

만약 속성과 관련된 제약조건이 존재한다면 관련된 제약조건이 먼저 삭제되어야 속성 제거가 가능하다.

 

• 새로운 제약조건 추가

ALTER TABLE table_name
ADD CONSTRAINT constraint_name constraint_details;

여기서 제약 내용은 칼럼 제약, 기본키, 대체키, 외래키 설정이 올 수 있다. 즉 다음과 같이 쓸 수 있다.

ALTER TABLE table_name
ADD CONSTRAINT constraint_name
CHECK (check_condition);

ALTER TABLE table_name
ADD CONSTRAINT primary_key_name
PRIMARY KEY (column_list);

ALTER TABLE table_name
ADD CONSTRAINT unique_name
UNIQUE (column_list);

ALTER TABLE table_name
ADD CONSTRAINT foreign_key_name
FOREIGN KEY (column_list)
REFERENCES other_table(other_column_list);

 

• 기존 제약조건 삭제

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

 

---

테이블 삭제

 

DROP TABLE table_name;

만약 삭제하려는 테이블을 참조하는 테이블이 있다면 테이블 삭제가 수행되지 않는다. 관련된 외래키 제약조건을 먼저 삭제하고 그 후에 테이블을 제거해야 한다.