231020 Oracle SQL UNIQUE, CHECK, NOT NULL 제약 조건

- 제약조건 정보 확인. 

SELECT * FROM user_constraints WHERE table_name in ('EMP', 'DEPT');
SELECT * FROM user_cons_columns WHERE table_name in ('EMP', 'DEPT');

- 제약조건 삭제

방법1)

ALTER TABLE hr.emp DROP CONSTRAINT emp_id_pk;

방법2)

ALTER TABLE hr.emp DROP PRIMARY KEY;

# dept 테이블의 pk 
# 오류발생 : 나를 참조하고 있는 foreign key 제약조건이 존재하기 때문이다.
ORA-02273: this unique/primary key is referenced by some foreign keys

ALTER TABLE hr.dept DROP PRIMARY KEY;

1# 해결방법 : 종속되어 있는 대상들을 '모두' 찾아 삭제.(시간적 여유X)

ALTER table hr.emp DROP CONSTRAINT emp_dept_id_fk;
ALTER TABLE hr.dept DROP PRIMARY KEY;

2# 해결방법 
ALTER TABLE hr.dept DROP PRIMARY KEY CASCADE;
CASCADE 옵셥은 나를 참조하고 있는 제약조건을 모두 삭제한 후 제약조건을 삭제한다.

# CASCADE CONSTRAINT PURGE : 참조하는 제약조건들 '모두' 삭제한 후 테이블 삭제하는 옵션

DROP TABLE hr.dept CASCADE CONSTRAINT PURGE;
DROP TABLE hr.EMP CASCADE CONSTRAINT PURGE;

 

# test table 삭제 

DROP TABLE hr.dept CASCADE CONSTRAINTS PURGE;
DROP TABLE hr.EMP CASCADE CONSTRAINTS PURGE;

# test 테이블 생성 

CREATE TABLE hr.emp (
    id    NUMBER ,
    name  VARCHAR2(30),
    sal number ,
    day   DATE
);

CREATE TABLE hr.dept (
    dept_id    NUMBER,
    dept_name  VARCHAR2(30)
);

 

UNIQUE 제약조건 ex) id중복체크
    A. 유일한 값만 체크
    B. 중복값 불허
    C. NULL 허용
    D. 자동으로 UNIQUE INDEX 생성

- 제약조건 정보 확인. 

SELECT * FROM user_constraints WHERE table_name in ('EMP', 'DEPT');
SELECT * FROM user_cons_columns WHERE table_name in ('EMP', 'DEPT');

- UNIQUE 제약조건 추가

ALTER TABLE hr.dept add constraint dept_name_uk unique(dept_name);

- index 제약조건 정보 확인. 

SELECT * FROM user_indexes WHERE table_name in ('EMP', 'DEPT');
SELECT * FROM user_ind_columns WHERE table_name in ('EMP', 'DEPT');

# DATA 로드

insert into hr.dept(dept_id, dept_name) values( 10, 'DA' );

# 오류발생 : ORA-00001: unique constraint (HR.DEPT_NAME_UK) violated
- unique 제약조건에 위반.

INSERT INTO hr.dept(dept_id, dept_name) values( 10, 'DA' );

# NULL 값 허용

INSERT INTO hr.dept(dept_id, dept_name) values( 20, NULL );

SELECT * FROM hr.dept;

# UNIQUE 제약조건 삭제 (2가지)
방법1)

ALTER TABLE hr.dept DROP CONSTRAINT dept_name_uk;

- index 제약조건 정보 확인. 

SELECT * FROM user_indexes WHERE table_name in ('EMP', 'DEPT');
SELECT * FROM user_ind_columns WHERE table_name in ('EMP', 'DEPT');

방법2)  

ALTER TABLE hr.dept ADD CONSTRAINT dept_name_uk UNIQUE(dept_name);
ALTER TABLE  hr.dept DROP UNIQE(dept_name);

4. CHECK 제약조건
    A. 조건에 값이 TRUE 인 경우에 INSERT, UPDATE 할 수 있도록 제약조건
    B. NULL 값 허용한다.
    C. 중복되는 값 허용한다.

방법1)

ALTER TABLE hr.emp ADD CONSTRAINT emp_sal_ck CHECK(sal >= 1000 AND sal <= 2000);

방법2)

ALTER TABLE hr.emp ADD CONSTRAINT emp_sal_ck CHECK(sal BETWEEN 1000 AND 2000);

# DATA 로드

insert into hr.emp ( id, name, sal, day ) values ( 1, '박찬호', 1500, sysdate );

#오류발생: CHECK 제약조건에 조건식에 결과값이 FLASE 이기 때문

INSERT INTO hr.emp ( id, name, sal, day ) VALUES ( 2, '홍길동', 500, sysdate );

 

# CHECK 제약조건에 NULL 값 허용

INSERT INTO hr.emp ( id, name, sal, day ) VALUES ( 1, '박찬호', NULL, sysdate );

 

# 오류발생 : check 제약조건에 조건식에 결과값이 false 이기 때문

UPDATE hr.emp
SET sal = 500 
WHERE id = 1;

# CHECK제약조건에 조건식에 결과값이 TRUE 이기 때문에 UPDATE 된다,

UPDATE hr.emp
SET sal = 1200 
WHERE id = 1;

# check 제약조건 삭제

ALTER TABLE hr.emp DROP CONSTRAINT emp_sal_ck;

5. NOT NULL 제약조건
    A. NULL 값을 허용할 수 없는 제약조건

    a. NOT NULL 제약조건 없을 때 정보 확인. 

SELECT * FROM user_constraints WHERE table_name in ('EMP', 'DEPT');
SELECT * FROM user_cons_columns WHERE table_name in ('EMP', 'DEPT');

 

# NOT NULL 제약조건 추가
    A. NOT NULL 제약조건은 MODIFY 를 이용해서 추가해야 한다.

ALTER TABLE hr.dept MODIFY dept_name CONSTRAINT dept_name_notnull NOT NULL;

    b. NOT NULL 제약조건 있을 때  정보 확인. 

SELECT * FROM user_constraints WHERE table_name in ('EMP', 'DEPT');
SELECT * FROM user_cons_columns WHERE table_name in ('EMP', 'DEPT');

# DATA 로드
# 오류발생 : ORA-01400: cannot insert NULL into ("HR"."DEPT"."DEPT_NAME")

이유: NOT NULL 제약조건에 위반

INSERT INTO hr.dept(dept_id, dept_name) VALUES ( 30, NULL) ;

# UPDATE 실행
# 오류발생(0개 행이 업데이트 되었다.) : NOT NULL 제약조건에 위반

UPDATE hr.dept
SET dept_name = NULL
WHERE dept_id = 1;

# NOT NULL 제약조건 삭제
방법1)

ALTER TABLE hr.dept DROP CONSTRAINT dept_name_notnull;

 

방법2) 

ALTER TABLE hr.dept MODIFY dept_name NULL;

# test 테이블 삭제

drop table hr.emp cascade constraint purge;
drop table hr.dept cascade constraint purge;

# test 테이블 생성

CREATE TABLE hr.dept (
    /* 열 레벌 정의 */
    dept_id    NUMBER    CONSTRAINT dept_dept_id_pk PRIMARY KEY, 
    dept_name  VARCHAR2(30)
);

# hr.dept 제약조건 확인

SELECT * FROM user_cons_columns WHERE table_name in ('DEPT');

# test 테이블 생성

CREATE TABLE hr.emp (
    /* 열 레벨 정의 */
    id NUMBER CONSTRAINT emp_id_pk PRIMARY KEY , 
    name VARCHAR2(30) CONSTRAINT emp_name_nn NOT NULL , /* ★ 열레벨 정의로만 정의해야 한다. */
    sal NUMBER , 
    dept_id NUMBER CONSTRAINT emp_dept_id_fk REFERENCES hr.dept(dept_id) , 
    /* 테이블 레벨 정의 */
    CONSTRAINT emp_name_uk UNIQUE(name), 
    CONSTRAINT emp_sal_ck CHECK(sal BETWEEN 1000 AND 2000)
);

CREAT TABLE 테이블명 (
    # 열 레벨 정의 
    id NUMBER CONSTRAINT emp_id_pk PRIMARY KEY, 
    name VARCHAR2(30) CONSTRAINT emp_name_nn 'NOT NULL', 
    sal NUMBER, 
    dept_id NUMBER CONSTRAINT emp_dept_id_fk REFERENCES hr.dept(dept_id) 
    
    # 테이블 레벌 정의
    CONSTRAINT emp_name_uk UNIQUE(name), 
    CONSTRAINT emp_sal_ck CHECK(sal BETWEEN 1000 AND 2000)
);


# hr.emp 제약조건 확인

SELECT * FROM user_cons_columns WHERE table_name in ('EMP');

# test 테이블 삭제

DROP TABLE hr.emp CASCADE CONSTRAINT PURGE;