[ORACLE 제약조건(오라클 Constraints)] 제약조건 과 컬럼 설정, 확인하기

만들어진 스키마에서 기본키, 외래키 , NULLABLE 등의 제약조건 과 그에대한 컬럼 확인 등

제약조건에 대한 것들을 확인 하는 쿼리문 들과  제약조건을 설정하는 법에 대한 내용입니다.

//*****************************************

-- 제약조건 확인

    DESC user_constraints; -- user_constraints 구조 확인

    SELECT * FROM USER_constraints WHERE table_name='테이블명'; -- 어떤 컬럼에 제약조건이 부여되었는지 확인 불가(테이블명은 대문자로)

           P:기본키, C:NOT NULL 등, U:UNIQUE, R:참조키 등

     SELECT constraint_name, table_name, constraint_type FROM user_constraints;

-- 현재 user가 가지고 있는 column에 할당된 제약조건에 대한 정보

    SELECT * FROM USER_cons_columns;

        - 어떤 컬럼에 제약조건이 부여되었는지 확인 가능

        - 제약 조건의 종류는 확인 불가능

-- 제약조건 및 컬럼 확인

SELECT u1.table_name, column_name, constraint_type,

   u1.constraint_name 

   FROM user_constraints u1

   JOIN user_cons_columns u2

   ON u1.constraint_name = u2.constraint_name

   WHERE u1.table_name = UPPER('테이블명');

-- 부와 자 관계의 모든 테이블 출력

 SELECT fk.owner, fk.constraint_name , pk.TABLE_NAME parent_table, 

        fk.table_name child_table

  FROM all_constraints fk, all_constraints pk 

 WHERE fk.R_CONSTRAINT_NAME = pk.CONSTRAINT_NAME 

   AND fk.CONSTRAINT_TYPE = 'R'

 ORDER BY fk.TABLE_NAME;

-- 『테이블명』을 참조하는 모든 테이블 목록 출력(자식 테이블 목록 출력)

SELECT fk.owner, fk.constraint_name , fk.table_name 

  FROM all_constraints fk, all_constraints pk 

 WHERE fk.R_CONSTRAINT_NAME = pk.CONSTRAINT_NAME 

   AND fk.CONSTRAINT_TYPE = 'R'

   AND pk.TABLE_NAME = UPPER('테이블명')

 ORDER BY fk.TABLE_NAME;

 

 

-- 『테이블명』이 참조하고 있는 모든 테이블 목록 출력(부모 테이블 목록 출력)

SELECT table_name FROM USER_constraints

   WHERE constraint_name IN (

SELECT r_constraint_name 

   FROM user_constraints

   WHERE table_name = UPPER('테이블명')

         AND constraint_type = 'R'

);

//*****************************************

-- 개체 무결성(PRIMARY KEY,  UNIQUE)

    릴레이션에 저장되는 튜플(tuple)의 유일성을 보장하기 위한 제약조건.

    컬럼이 중복적인 값을 가질 수 없다.(예 : 회원아이디, 주민번호 등)

    PRIMARY KEY는 NULL 값을 가질 수 없다.

    하나의 테이블에 하나의 기본키만 지정가능하다.

    두개 이상의 컬럼을 이용하여 기본키를 지정할 수 있다.

PRIMARY KEY 와  UNIQUE 제약조건의 컬럼은 자동으로 인덱스가 만들어 진다.

-- 참조 무결성(FOREIGN KEY)

    참조 무결성은 릴레이션 간의 데이터의 일관성을 보장하기 위한 제약조건이다

-- 제약 조건이름

   제약 조건이름은 30자 까지 지정 가능하다.

//*****************************************

-- 컬럼 레벨 기본키 지정

    컬럼 정의 시 해당 컬럼별로 지정하는 무결성 제약조건이다.

    하나의 컬럼으로만 기본키 지정이 가능 하다.

CREATE TABLE 테이블명 (

    컬럼명 데이터타입 [CONSTRAINT 제약조건명] PRIMARY KEY

       :

)

---------------------------------------------

-- 제약 조건 확인

SELECT constraint_name, table_name,  r_constraint_name, constraint_type,  search_condition

            FROM user_constraints;

-- 현재 user가 가지고 있는 column에 할당된 제약조건에 대한 정보

SELECT * FROM user_cons_columns;

//*****************************************

-- 테이블 레벨 기본키

   하나 이상의 컬럼을 이용하여 기본키를 지정 할 수 있다.

CREATE TABLE 테이블명 (

    컬럼명 데이터타입 

       :

    ,[CONSTRAINT 제약조건명] PRIMARY KEY(컬럼명 [,컬럼명])

);

-- 기본키 지정(컬럼 레벨. 하나의 컬럼으로만 기본키 지정가능 : 제약조건명을 지정하지 않는 경우)

CREATE TABLE test1 (

    id  VARCHAR2(30)  PRIMARY  KEY

,name  VARCHAR2(30) NOT  NULL

,birth DATE

);

-- 기본키 지정(테이블 레벨 : 제약조건명을 지정하지 않는 경우)

CREATE TABLE test2 (

    id  VARCHAR2(30)

,name  VARCHAR2(30) NOT  NULL

,birth DATE

,PRIMARY  KEY(id)

);

-- 기본키 지정(테이블 레벨 : 2개의 컬럼으로 지정, 제약조건명 지정)

CREATE TABLE test3 (

    num  NUMBER

    ,code  VARCHAR2(30)

,name  VARCHAR2(30) NOT  NULL

,birth DATE

,CONSTRAINT pk_test3_num PRIMARY  KEY(num, code)

);

-- 제약조건 확인(제약조건 종류, 제약조건이 할당된 컬럼은 확인 불가)

SELECT * FROM user_constraints WHERE INSTR(table_name, 'TEST') = 1 ;

-- 제약조건이 할당된 컬럼은 확인. 제약조건 종류는 확인 불가

SELECT * FROM user_cons_columns WHERE INSTR(table_name, 'TEST') = 1;

 num+code : 기본키

INSERT INTO  test3(num, code, name, birth) VALUES(1, 'a', 'aaa', '2000-10-10');

INSERT INTO  test3(num, code, name, birth) VALUES(1, 'b', 'bbb', '2000-09-09');

INSERT INTO  test3(num, code, name, birth) VALUES(1, 'a', 'ccc', '2000-10-10');

   -- ORA-00001 : 에러

   

INSERT INTO  test3(num, code, name, birth) VALUES(1, null, 'ccc', '2000-10-10');

   -- 에러 : 기본키는 NULL이 될수 없다.

 

INSERT INTO  test3(num, code, name, birth) VALUES(1, 'c', '', '2000-10-10');

   -- 에러 : 이름은 NULL이 될 수 없다.

   

//*****************************************

-- 기본키 삭제

 ALTER TABLE 테이블명 DROP PRIMARY KEY;

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

 

ALTER TABLE test3 DROP PRIMARY KEY; 

SELECT * FROM user_constraints WHERE table_name = 'TEST3' ;

//*****************************************

-- 이미 존재하는 테이블에 기본키 추가

   ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건이름 PRIMARY KEY(컬럼명[, 컬럼명]);

   ALTER TABLE test3 ADD CONSTRAINT pk_test3_num PRIMARY KEY(num);

       -- 에러 : 기본키 제약조건을 위반하는 데이터를 가지고 있으므로

   

-- num+code 로 기본키 지정

   ALTER TABLE test3 ADD CONSTRAINT pk_test3_num PRIMARY KEY(num, code);

   

//*****************************************

-- UNIQUE 제약

   - 테이블에서 지정한 컬럼의 데이터가 중복되지 않고 유일하다.

   - null을 허용할 경우 null은 중복이 가능하다.

   - UNIQUE는 두개 이상 지정 가능 하다.

-- 컬럼 레벨

CREATE TABLE 테이블명 (

    컬럼명 데이터타입 [CONSTRAINT 제약조건명] UNIQUE

       :

);

-- 테이블 레벨

CREATE TABLE 테이블명 (

    컬럼명 데이터타입 

       :

    ,[CONSTRAINT 제약조건명] UNIQUE(컬럼명)

);

CREATE TABLE test4 (

    num  NUMBER

    ,code  VARCHAR2(30)

,name  VARCHAR2(30) NOT  NULL

,email  VARCHAR2(50) UNIQUE

,birth DATE

,CONSTRAINT pk_test4_num PRIMARY  KEY(num, code)

);

SELECT * FROM user_constraints;

INSERT INTO  test4(num, code, name, email, birth) VALUES(1, 'a', 'aaa', 'a', '2000-10-10');

INSERT INTO  test4(num, code, name, email, birth) VALUES(1, 'b', 'bbb', 'a', '2000-10-10');

        -- 에러 : UNIQUE 제약 위반

INSERT INTO  test4(num, code, name, email, birth) VALUES(1, 'c', 'ccc', null, '2000-10-10');  -- UNIQUE는 NOT NULL 이 아니므로 가능

---------------------------------------------

-- 이미 존재하는 테이블에 UNIQUE 제약 추가

   ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건이름 UNIQUE(컬럼);

---------------------------------------------

-- UNIQUE 제약 삭제

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

//*****************************************

-- NOT NULL 추가

   테이블에서 지정한 컬럼의 데이터가 NULL(또는 '') 값을 갖지 못한다. ' ' 처럼 공백이 있는 경우는 null이 아니다.

-- 테이블 생성시

  CREATE TABLE 테이블명 (

     컬럼명명 자료형 NOT NULL, 

          :

  );

---------------------------------------------

 -- 존재하는 테이블에 NOT NULL 추가

ALTER TABLE 테이블명 MODIFY 컬럼명 NOT NULL;

ALTER TABLE 테이블명 MODIFY (컬럼명 자료형 NOT NULL);

ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 CHECK(컬럼명 IS NOT NULL);

---------------------------------------------

 -- NOT NULL 제거

 ALTER TABLE 테이블명 MODIFY 컬러명 NULL;

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

//*****************************************

-- DEFAULT(초기값) 추가

    - INSERT할 때 값을 입력하지 않은 경우 DEFAULT에서 설정한 값으로 입력

    - INSERT와 UPDATE 문에서 특정 값이 아닌 디폴트값을 입력할 수 도 있다.

  CREATE TABLE 테이블명 (

     컬럼명 자료형 DEFAULT 값, 

          :

  );

---------------------------------------------

-- DEFAULT 제거

ALTER TABLE 테이블명 MODIFY 컬럼명 DEFAULT NULL;

---------------------------------------------

-- DEFAULT 확인

SELECT column_name, data_type, data_precision, data_length, nullable, data_default 

            FROM user_tab_columns WHERE table_name='테이블명';

CREATE TABLE test (

    num  NUMBER  PRIMARY KEY

,name VARCHAR2(30) NOT NULL

,content  VARCHAR2(4000) NOT NULL

,created  DATE  DEFAULT SYSDATE

);

INSERT INTO  test(num, name, content, created) VALUES (1, 'a', 'aaa', '2000-10-10');

INSERT INTO  test(num, name, content, created) VALUES (2, 'b', 'bbb', SYSDATE);

INSERT INTO  test(num, name, content, created) VALUES (3, 'c', 'ccc', DEFAULT);

INSERT INTO  test(num, name, content) VALUES (4, 'd', 'ddd');

INSERT INTO  test(num, name, content, created) VALUES (5, 'e', 'eee', NULL);

SELECT * FROM test;

UPDATE test SET created = '2010-11-11' WHERE num=5;

SELECT * FROM test;

SELECT * FROM user_tab_columns WHERE table_name='TEST';

ALTER TABLE test MODIFY created DEFAULT NULL;

SELECT * FROM cols WHERE table_name='TEST';

//*****************************************

-- CHECK 제약 추가

   - 컬럼 레벨

      CREATE TABLE 테이블명 (

          컬럼명 데이터타입 [CONSTRAINT 제약조건명] CHECK(조건)

                :

       );

   - 테이블 레벨

      CREATE TABLE 테이블명 (

          컬럼명 데이터타입 

                :

          ,CONSTRAINT 제약조건명 CHECK(조건)

);

-- gender 컬럼에 남자와 여자만 입력을 허용 할 경우

    gender VARCHAR2(6) CHECK(gender IN ('남자', '여자'))

-- score 컬럼에 0~100 점만 입력을 허용 할 경우

    score NUMBER(3) CHECK(score BETWEEN 0 AND 100)

-- gender, score 컬럼을 test에 추가

ALTER TABLE test ADD (gender VARCHAR2(6) CHECK(gender IN ('남자', '여자')),

                     score NUMBER(3));

ALTER TABLE test ADD CONSTRAINT ck_test_score CHECK (score BETWEEN 0 AND 100);

INSERT INTO  test(num, name, content, gender, score) VALUES (6, 'e', 'eee', '남', 80);

    -- 에러. CHECK 제약 위반

INSERT INTO  test(num, name, content, gender, score) VALUES (6, 'e', 'eee', '남자', 80);

SELECT * FROM user_constraints WHERE table_name='TEST';

---------------------------------------------

-- 존재하는 컬럼명에 CHECK 제약 추가

ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 CHECK (조건);

---------------------------------------------

-- CHECK 제약 삭제

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

-- 다음의 두컬럼을 test 테이블에 추가

   sdate DATE

   edate DATE

-- 데이터를 추가하거나 수정할때 sdate 컬럼보다 edate가 크거나 같도록 CHECK 제약을 추가

ALTER TABLE test ADD (sdate DATE, edate DATE);   

ALTER TABLE test ADD CONSTRAINT ck_test_date CHECK (sdate <= edate);

 

INSERT INTO  test(num, name, content, gender, score, sdate, edate)

        VALUES (7, 'f', 'fff', '남자', 80, '2010-10-10', '2009-10-10');

    -- 에러

INSERT INTO  test(num, name, content, gender, score, sdate, edate)

        VALUES (7, 'f', 'fff', '남자', 80, '2010-10-10', '2010-12-10');

 

 

//*****************************************

-- 제약조건 제거

SELECT constraint_name, table_name,  r_constraint_name, constraint_type,  search_condition

            FROM user_constraints;

---------------------------------------------

 -- 기본키 제거

 ALTER TABLE 테이블명 DROP PRIMARY KEY;

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

 

---------------------------------------------

 -- NOT NULL 제거

 ALTER TABLE 테이블명 MODIFY 컬러명 NULL;

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

---------------------------------------------

-- 기타 제약조건 제거

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