DB:200526-DB 제약조건 관련: 데이터를 추가, 삭제, 수정하는 가운데 db의 무결성을 유지

최무회 2020. 5. 26. 10:37

제약조건 관련

데이터를 추가, 삭제, 수정하는 가운데 db의 무결성을 유지

CREATE TABLE EMP01 (

EMPNO NUMBER,

ENAME VARCHAR2(20),

job VARCHAR2(20),

deptno NUMBER

)

CREATE TABLE EMP01 (EMPNO NUMBER,ENAME VARCHAR2(20),job VARCHAR2(20),deptno NUMBER)

INSERT INTO EMP01 VALUES(NULL, NULL, 'IT', 30); <- 잘못된것이지만 들어가는 데이터 , 그래서 제약 조건을 사용

CREATE TABLE EMP01 (

EMPNO NUMBER,

ENAME VARCHAR2(20),

job VARCHAR2(20),

deptno NUMBER

);

--INSERT INTO EMP01 VALUES(NULL, NULL, 'IT', 30);

CREATE TABLE EMP01 (EMPNO NUMBER,ENAME VARCHAR2(20),job VARCHAR2(20),deptno NUMBER);--INSERT INTO EMP01 VALUES(NULL, NULL, 'IT', 30);

CREATE TABLE EMP02 (

EMPNO NUMBER NOT NULL,

ENAME VARCHAR2(20) NOT NULL,

job VARCHAR2(20),

deptno NUMBER

);

CREATE TABLE EMP02 (EMPNO NUMBER NOT NULL,ENAME VARCHAR2(20) NOT NULL,job VARCHAR2(20),deptno NUMBER);

--INSERT INTO EMP02 VALUES(NULL, NULL, 'IT', 30);

INSERT INTO EMP02 VALUES(100, 'Kim', 'IT', 30);

INSERT INTO EMP02 VALUES(100, 'Park', 'IT', 30);

CREATE TABLE EMP03 (

EMPNO NUMBER UNIQUE,

ENAME VARCHAR2(20) NOT NULL,

job VARCHAR2(20),

deptno NUMBER

);

INSERT INTO EMP03 VALUES(100, 'Kim', 'IT', 30);

INSERT INTO EMP03 VALUES(100, 'Park', 'IT', 30)

INSERT INTO EMP03 VALUES(100, 'Park', 'IT', 30)

* PRIMARY KEY 제약조건

-> UNIQUE + NOT NULL

CREATE TABLE EMP04 (

EMPNO NUMBER PRIMARY KEY,

ENAME VARCHAR2(20) NOT NULL,

job VARCHAR2(20),

deptno NUMBER

);

CREATE TABLE EMP04 (EMPNO NUMBER PRIMARY KEY,ENAME VARCHAR2(20) NOT NULL,job VARCHAR2(20),deptno NUMBER);

INSERT INTO EMP04 VALUES(100, 'Kim', 'IT', 30);

INSERT INTO EMP04 VALUES(100, 'Park', 'IT', 30);

INSERT INTO EMP03 VALUES(100, 'Park', 'IT', 30000)

* FOREIGN KEY <- 외부값을 참조 함

CREATE TABLE EMP05 (

EMPNO NUMBER PRIMARY KEY,

ENAME VARCHAR2(20) NOT NULL,

job VARCHAR2(20),

deptno NUMBER REFERENCES departments(department_id)

);

INSERT INTO EMP05 VALUES(100, 'Kim', 'IT', 30000);

컬럼레벨 방식 <- 컬럽 옆에 제약조건을 넣는 식

* 테이블 레벨 방식 -> 제약조건 이름을 명시

CREATE TABLE EMP06(

EMPNO NUMBER PRIMARY KEY,

ENAME VARCHAR2(20) NOT NULL,

job VARCHAR2(20),

deptno NUMBER,

constraint emp06_empno_pk PRIMARY KEY(empno),

constraint emp06_deptno_fk FOREIGN KEY(deptno)

REFERENCES departments(department_id)

);

CREATE TABLE EMP06(EMPNO NUMBER PRIMARY KEY,ENAME VARCHAR2(20) NOT NULL,job VARCHAR2(20),deptno NUMBER,constraint emp06_empno_pk PRIMARY KEY(empno),constraint emp06_deptno_fk FOREIGN KEY(deptno)REFERENCES departments(department_id));

INSERT INTO EMP05 VALUES(100, 'Kim', 'IT', 30000);

* 테이블 수정 방식

DROP TABLE EMP07;

CREATE TABLE EMP07(

EMPNO NUMBER ,

ENAME VARCHAR2(20) NOT NULL,

job VARCHAR2(20),

deptno NUMBER

);

ALTER TABLE emp07

ADD CONSTRAINT emp07_empno_fk

FOREIGN KEY(deptno)

REFERENCES departments(department_id);

ALTER TABLE EMP07

ADD CONSTRAINT emp07_empno_pk

PRIMARY KEY(empno);

CHECK 제약조건(의도된 데이터만 입력 받기 위해)

CREATE TABLE EMP08(

EMPNO NUMBER ,

ENAME VARCHAR2(20) NOT NULL,

job VARCHAR2(20),

deptno NUMBER,

gender char(1) CHECK(gender IN('M', 'F'))

);

CREATE TABLE EMP08(EMPNO NUMBER ,ENAME VARCHAR2(20) NOT NULL,job VARCHAR2(20),deptno NUMBER,gender char(1) CHECK(gender IN('M', 'F')));

INSERT INTO EMP08 VALUES(100, 'Kim', 'IT', 30, 'A');

DEFAULT 제약조건 <- 접근제어자

CREATE TABLE EMP09(

EMPNO NUMBER ,

ENAME VARCHAR2(20) NOT NULL,

job VARCHAR2(20),

deptno NUMBER,

loc VARCHAR2(20) DEFAULT 'Seoul'

);

INSERT INTO EMP09 (empno, ename, job, deptno)

VALUES(100, 'Kim', 'IT', 30);

2개이상 주식별자 설정 시 <- 2 컬럼 모두 동일해야 제약조건 실행됨

CREATE TABLE EMP10(

EMPNO NUMBER ,

ENAME VARCHAR2(20) NOT NULL,

job VARCHAR2(20),

deptno NUMBER

);

ALTER TABLE EMP10

ADD CONSTRAINT emp10_empno_pk

PRIMARY KEY(empno, ename);

제약조건 삭제

ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건명;

alter table member

drop CONSTRAINT member_membercl_pk;

저작자표시 비영리 변경금지 (새창열림)