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

제약조건 관련

 

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

 

 

데이터를 추가, 삭제, 수정하는 가운데 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;

 

---