3월 28일 (2) DB - My SQL DDL 테이블 제약 조건, 삭제, 수정

 

제약조건(Constraint)

특정 컬럼에 제약조건을 설정하기 위한 명령어 NOT NULL, DEFAULT 제외

이 때 제약조건에 이름(식별자)을 부여한다.

CONSTRAINT 키워드는 생략가능

 

1. UNIQUE

[CONSTRAINT 식별자] UNIQUE (컬럼명)

2. PRIMARY KEY

[CONSTRAINT 식별자] PRIMARY KEY (컬럼명)

3. FOREIGN KEY 

[CONSTRAINT 식별자] FOREIGN KEY (컬럼명)

REFERENCES 테이블명(컬럼명)

4. CHECK

 

 

식별자로 나중에 제약조건을 제어할 수 있음(삭제, 수정 등)

보통 테이블명과 컬럼명, 제약조건의 단어를 조합하여 작명

예) member 테이블의 기본키 : m_id_pk

컬럼이 아니라 규칙에 이름을 붙이기, 기본키만 삭제할 수 없음, 삭제시 테이블까지 전부 삭제됨

제약 조건 자체를 제어하는 것

 

제약조건을 쓸 때 간단하게 사용하는 방법

1. 컬렴에 직접 쓰는 방법이 가장 간단

2. 제약 조건만 몰아서 작성하기

3. ARTER 테이블을 사용해서 

 

예) 

CREATE TABLE company (
    cname VARCHAR(20),
    cphone VARCHAR(15) NOT NULL,
    cloc VARCHAR(50) NOT NULL,
    cmanager VARCHAR(10) NOT NULL,
    CONSTRAINT c_n_pk  PRIMARY KEY(cname),

    UNIQUE (cphone) 
);





CREATE TABLE goods (
    gnumber INT NOT NULL,
    gname VARCHAR(20) NOT NULL,
    ginven INT DEFAULT 0,
    gprice INT NOT NULL,
    gmakename VARCHAR(20),
    gdate DATE,
    gflow INT DEFAULT 0,
    PRIMARY KEY(gnumber),
    CONSTRAINT g_c_fk FOREIGN KEY (gname)
    REFERENCES company(cname)
);

 

반드시 외래키를 추가할 테이블을 먼저 만들어야 외래키가 연결된다.

외래키가 있는 테이블을 먼저 삭제, 생성된 역순으로 삭제해야 한다. 

 

-- 삭제 시 외래키가 있는 테이블 먼저 삭제할 것
DROP TABLE company; -- 두번째 삭제 먼저 만든 테이블이 외래키로 인해서 삭제되지 않음.
DROP TABLE goods; -- 먼저 삭제

 

 

 

외래키 설정 시 같이 설정하는 부가속성

ON UPDATE : 참조되는 테이블(컬럼)의 값이 변경될 때

ON DELETE : 참조되는 테이블(컬럼)의 값이 삭제될 때

 

동작옵션

1) CASCADE : 참조하는 테이블의 값도 같이 처리(금액과 관련 없는 상황에서 많이 사용)

2) SET NULL : 참조하는 테이블의 값을 NULL로 변경

3) NO ACTION : 아무 변경 안함

4) SET DEFAULT : 기본값으로 변경

5) RESTRICT : 참조되는 테이블(컬럽)의 값 변경 불가

 

CREATE TABLE goods (
    gnumber INT NOT NULL,
    gname VARCHAR(20) NOT NULL,
    ginven INT DEFAULT 0,
    gprice INT NOT NULL,
    gmakename VARCHAR(20),
    gdate DATE,
    gflow INT DEFAULT 0,
    PRIMARY KEY(gnumber),
    CONSTRAINT g_c_fk FOREIGN KEY (gname)
    REFERENCES company(cname)
    ON UPDATE CASCADE ON DELETE SET NULL
);

 

자동 증가 컬럼 설정 : Auto Increment(AI)

SQL에서는 없는 기능, DBMS마다 기능이 약간씩 다름

My SQL 프로그램에서 제공하는 기능(오라클은 없음)

1씩 증가하는 정수값이 자동으로 삽입되는 컬럼을 작성할 때 사용(정수형 기본키 컬럼에만 적용가능)

예) CREATE TABLE datatb1 (

      dno INT AUTO_INCREMENT PRIMARY KEY,

      .......

);

 

예약어 사용 불가

MySQL 5.6 한글메뉴얼

예)

CREATE TABLE order_tb1 (
    ono INT AUTO_INCREMENT PRIMARY KEY,
    omid VARCHAR(20) NOT NULL,
    opno INT NOT NULL,
    oamount INT NOT NULL,
    oloc VARCHAR(50) NOT NULL,
    odate DATE NOT NULL,
    FOREIGN KEY (omid)
    REFERENCES member (mid),
    FOREIGN KEY (opno)
    REFERENCES goods (gnumber)
    ON UPDATE CASCADE 
    ON DELETE RESTRICT
);

 

테이블 삭제 - 복구 불가능

DROP TABLE 테이블명;

 

테이블 수정 ALTER

ALTER TABLE 테이블명

개발자는 잘 안쓰는 명령어

 

추가옵션

1) 컬럼추가 : ADD COLUMN 컬럼명 타입;

2) 컬럼변경 : MODIFY COLUMN 컬럼명 변경타입; 

변경할 컬럼에 데이터가 있을 경우 불가 / 데이터 타입이 다르면 안됨

3) 컬럼삭제 : DROP COLUMN 컬럼명;

4) 테이블명 변경 : RENAME 새테이블명;

5) 컬럼명 변경 : CHANGE COLUMN 원컬럼명 새컬럼명;

 

예) 

-- 컬럼추가
ALTER TABLE testtb1
ADD COLUMN phone VARCHAR(15);
-- NOT NULL도 추가
ALTER TABLE testtb1
ADD COLUMN phone VARCHAR(15) NOT NULL;

-- 컬럼 타입 또는 크기 변경
ALTER TABLE testtb1
MODIFY COLUMN phone VARCHAR(20);



-- 컬럼 이름 변경
ALTER TABLE testtb1
CHANGE COLUMN phone t_phone VARCHAR(15);

-- 컬럼 삭제
ALTER TABLE testtb1
DROP COLUMN t_phone;

-- 테이블명 변경
ALTER TABLE testtb1
RENAME test_tb1;

 

CREATE TABLE로 TABLE만들기

 

 

ALTER TABLE 

제약 조건의 추가 / 삭제

 

ALTER TABLE 테이블명

1) 기본키 추가 : ADD PRIMARY KEY(컬럼명);

2) 기본키 삭제 : DROP PRIMARY KEY;

3) 외래키 추가 : ADD CONSTRAINT 식별자 FOREIGN KEY(컬럼명)

                          REFERENCES 테이블명(컬럼명); //가급적이면 식별자를 써서 삭제시 유용하게 사용 하자! 

4) 외래키 삭제 : DROP FOREIGN KEY 식별자;

5) 기타 제약조건 추가/삭제(UNIQUE, CHECK)

추가 :  ADD CONSTRAINT 식별자 제약조건_키워드;

삭제 : DROP CONSTRAINT 식별자;

 

-- 기본키 추가
ALTER TABLE test_tb1
ADD PRIMARY KEY (t_id);

-- 기본키 삭제
ALTER TABLE test_tb1
DROP PRIMARY KEY;

-- 컬럼 추가
ALTER TABLE test_tb1
ADD COLUMN t_phone VARCHAR(15);

-- 외래키 추가
ALTER TABLE test_tb1
ADD CONSTRAINT t_p_fk FOREIGN KEY(t_phone)
REFERENCES company(cphone);

-- 외래키 삭제
ALTER TABLE test_tb1
DROP FOREIGN KEY t_p_fK;