[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 제약조건이름;