SQL의 데이터 정의 기능

SQL의 데이터 정의 기능

1. 테이블 생성

CREATE TABLE

CREATE TABLE 테이블_이름 (
    속성_이름 데이터 타입 [NOT NULL] [DEFAULT 기본_값]
    [PRIMARY KEY (속성_리스트)]
    [UNIQUE (속성_리스트)]
    [FOREIGN KEY (속성_리스트) REFERENCES 테이블_이름(속성_리스트)]
    [ON DELETE 옵션] [ON UPDATE 옵션]
    [CONSTRAINT 이름] [CHECK(조건)]
);

참조무결성 제약조건
왜리키가 어떤 테이블의 무슨 속성을 참조하는지를 REFERENCES 키워드 다음에 명확히 제시하여 참조되는 테이블에서 투플을 함부로 삭제하거나 변경하지 못하게 한다.

참조되는 테이블에서 투플을 삭제할 때 처리하는 방법
1) ON DELETE NO ACTION(투플을 삭제하지 못하게 함)(디폴트)
2) ON DELETE CASCADE(관련 투플을 함께 삭제함)
3) ON DELETE SET NULL(관련 투플의 외래키 값을 NULL로 변경함)
4) ON DELETE SET DEFAULT(관련 투플의 외래키 값을 미리 지정한 기본 값으로 변경함)

참조되는 테이블에서 투플을 변경할 때 처리하는 방법
1) ON UPDATE NO ACTION(투플을 변경하지 못하게 함)(디폴트)
2) ON UPDATE CASCADE(관련 투플에서 외래키 값을 함께 변경함)
3) ON UPDATE SET NULL(관련 투플의 외래키 값을 NULL로 변경함)
4) ON UPDATE SET DEFAULT(관련 투플의 외래키 값을 미리 지정한 기본 값으로 변경함)

데이터 무결성 제약조건의 정의
CREATE TABLE 문으로 테이블을 정의할 때 CHECK 키워드를 사용해 특정 속성에 대한 제약조건을 지정한다. 테이블에 새로운 투플을 삽입하거나 기존 투플을 수정할 때도 이 제약조건을 반드시 지켜야 한다. 이는 테이블에 항상 정확하고 유효한 데이터를 유지하기 위해 데이터 무결성을 위한 제약조건을 표현하는 방법이다.

CHECK(재고량 >=0 AND 재고량 <=1000)
or CONSTRAINT CHK_CPY CHECK(제조업체 = '한빛제과')

고객 테이블
CREATE TABLE customer (
    id                VARCHAR(20) NOT NULL, /* 고객 아이디 */
    name           VARCHAR(10) NOT NULL, /* 고객 이름 */
    age              INT, /* 나이 */
    grade           VARCHAR(10) NOT NULL, /* 등급 */
    job              VARCHAR(20), /* 직업 */
    saved_money INT DEFAULT 0, /* 적립금 */
    PRIMARY KEY(id)
);

제품 테이블
CREATE TABLE product (
    id                CHAR(3) NOT NULL, /* 제품번호 */
    name           VARCHAR(20), /* 제품명 */
    stock           INT, /* 재고량 */
    price            INT, /* 단가 */
    manufacturer VARCHAR(20), /* 제조업체 */
    PRIMARY KEY(id),
    CHECK (stock >= 0 AND stock <= 10000)
);

주문 테이블
CREATE TABLE order_info (
    id            CHAR(3) NOT NULL, /* 주문번호 */
    customer   VARCHAR(20), /* 주문고객 */
    product     CHAR(3), /* 주문제품 */
    quantity     INT, /* 수량 */
    destination VARCHAR(30), /* 배송지 */
    order_date DATETIME, /* 주문일자 */
    PRIMARY KEY(id),
    FOREIGN KEY(customer) REFERENCES customer(id),
    FOREIGN KEY(product) REFERENCES product(id)
);
-- 오라클에서는 주문일자 속성의 데이터 타입은 DATE로 지정

2. 테이블 변경

ALTER TABLE 문을 이용해 새로운 속성 추가, 기존 속성 삭제, 새로운 제약조건 추가, 기존 제약조건 삭제 등이 가능하다.

새로운 속성의 추가

ALTER TABLE 테이블_이름 ADD 속성_이름 데이터_타입 [NOT NULL] [DEFAULT 기본_값];
ex) ALTER TABLE customer ADD join_date DATETIME;

기존 속성의 삭제

ALTER TABLE 테이블_이름 DROP 속성_이름 CASECADE | RESTRICT;

ALTER TABLE 문을 작성할 때는 삭제할 속성과 관련된 제약조건이 존재하거나 이 속성을 참조하는 다른 속성이 존재하는 경우에 처리하는 방법을 선택할 수 있다. 관련된 제약조건이나 참조하는 다른 속성을 함께 삭제하기 위해 CASCADE를 지정하거나, 관련된 제약조건이나 참조하는 다른 속성이 존재하면 삭제가 수행되지 않도록 RESTRICT를 지정할 수 있다.
ex) ALTER TABLE customer DROP grade CASCADE;

새로운 제약조건의 추가

ALTER TABLE 테이블_이름 ADD CONSTRAINT 제약조건_이름 제약조건_내용
ex) ALTER TABLE customer ADD CONSTRAINT CHK_AGE CHECK(age >= 20);

기존 제약조건의 삭제

ALTER TABLE 테이블_이름 DROP CONSTRAINT 제약조건_이름;
ex) ALTER TABLE customer DROP CONSTRAINT CHK_AGE;

3. 테이블 제거

DROP TABLE 테이블_이름 CASECADE | RESTRICT;

DROP TABLE 문을 작성할 때 삭제할 테이블을 참조하는 다른 테이블도 함께 삭제하려면 CASCADE를 지정한다. 반대로 삭제할 테이블을 참조하는 테이블이 있으면 삭제가 수행되지 않도록 하려면 RESTRICT를 지정한다.

ex) DROP TABLE customer RESTRICT;