[oracle] PRIMARY KEY FOREIGN KEY 기본키 외래키 보기 sqlplus 로 용-ILE/DB-mysql / oracle2008. 5. 15. 20:10
http://suite.tistory.com/ 2008 01 fs
#>sqlplus
alter table TB_STD_MASTER modify(SEQ NUMBER PRIMARY KEY);
select constraint_type from user_constraints where table_name='TB_STD_MASTER' ;
constraint_type 이 P : 기본키 , R : 왜래키 , C: 체크 , UK: 유니크
키삭제
alter TABLE TB_KLAW_MAIN drop PRIMARY KEY;
기타 :
- Unique 인덱스
Unique 인덱스는 인덱스를 사용한 컬럼의 중복값들을 포함하지 않고 사용할 수 있는 장점이 있습니다.
프라이머리키 와 Unique 제약 조건시 생성되는 인덱스는 Unique 인덱스입니다.
SQL>CREATE UNIQUE INDEX emp_ename_indx
ON emp(ename);
- Non-Unique 인덱스
Non-Unique 인덱스는 인덱스를 사용한 컬럼에 중복 데이터 값을 가질수 있습니다.
SQL>CREATE INDEX dept_dname_indx
ON dept(dname);
출처: http://radiocom.kunsan.ac.kr/lecture/oracle/what_is/constraints.html
Constraints(제약조건)
테이블에 부여된 제약조건의 관계키는 user_constraints 뷰를 사용하여 확인할 수 있다.
제약조건은 data integrity(데이터 무결성)을 위하여 주로 테이블에 행(row)을 입력, 수정, 삭제할 때 적용되는 규칙으로 사용되며 테이블에 의해 참조되고 있는 경우 테이블의 삭제 방지를 위해서도 사용된다.
관계 키 참조
제약조건을 사용하는 이유
제약조건 없이도 기본 구조의 테이블은 생성된다. 그러나 테이블을 생성한 경우에 DML에 의한 데이터 조작이 사용자가 원하는 대로 되지 않을 수 있다.
예를 들어, 유일하게 지정되어야 할 주민번호 컬럼이 UNIQUE 제약조건이 없다면 중복된 데이터가 입력될 수 있다.
이와같이 DML에 의한 데이터가 잘못 조작되는 것을 방지하기 위해 사용자는 각 컬럼에 대한 제약조건을 만들어 줄 수 있다. 이러한 제약조건은 테이블에 행을 삽입, 수정, 삭제할 때마다 사용되며, 테이블에 의해 참조되고 있는 경우 테이블 삭제 방지를 위해서도 사용된다.
제약조건의 특징
• DML 작업에서 잘못되는 것을 제약조건에 의해 방지한다.
• 모든 정보가 데이터 사전에 저장된다.
• 언제든지 disable, enable시킬 수 있다.
• 처리결과가 즉시 사용자에게 넘겨진다.
• 한 컬럼에 여러 개의 제약조건을 지정할 수 있다.
• 하나의 컬럼뿐만 아니라 여러 컬럼을 조합하여 하나의 key를 구성하는 composit key(복합키)를 만들 수 있다. 예: PRIMARY KEY(pno, ename)
• 제약조건의 관리는 DB server가 담당한다.
무결성제약조건(Constraint) 생성
무결성 제약조건은 CREATE TABLE문에서 테이블 생성과 동시에 정의하거나, ALTER TABLE 문을 사용하여 테이블을 생성한 이후에도 추가하거나 삭제할 수 있다.
CREATE TABLE 문을 이용한 constraint 생성 방법은 다음과 같이 두 가지가 있다.
• IN-LINE constraint 방법(column level에서의 제약조건)
• OUT-OF-LINE constraint 방법(Table level에서의 제약조건)
IN-LINE 제약조건은 테이블 생성시 컬럼명 바로 뒤에 기술하여 생성하는 방법이고,
OUT-OF-LINE 제약조건은 마지막 제약조건에 ,(컴마)로 구분한 뒤 제약조건을 기술하여 생성한다.
하나의 컬럼에 대하여 제약조건을 생성할경우에는 두 가지 방법이 모두 사용될 수 있지만, 하나의 제약조건이 두 개 이상의 컬럼에 대하여 동시에 적용될 때는 반드시 OUT-OF-LINE 제약조건 방법을 사용하여 생성해야 한다.
그리고, NOT NULL 제약조건은 OUT-OF-LINE 제약조건 방법으로는 생성될 수 없다.
1) 컬럼 레벨 무결성제약조건
• 컬럼 정의시 해당 컬럼별로 지정하는 무결성 제약조건이다.
• 하나의 컬럼에 적용되는 제약조건을 정의할 때 사용한다.
• 컬럼의 정의 뒤에 곧바로 선언한다.
• 컬럼별로 제약조건을 정의한다.
• 무결성 5가지(아래 표에 나열된 PK,FK,UK,CK,NN)를 모두 적용이 가능하다.
| constraint | 설명 |
|---|---|
| PRIMARY KEY(PK) | 해당 컬럼 값은 반드시 존재해야 하며, 유일해야 함 (NOT NULL과 UNIQUE 제약조건을 결합한 형태) |
| FOREIGN KEY(FK) | 해당 컬럼 값은 참조되는 테이블의 컬럼 값 중의 하나와 일치하거나 NULL을 가짐 |
| UNIQUE KEY(UK) | 테이블내에서 해당 컬럼 값은 항상 유일해야 함 |
| NOT NULL | 컬럼은 NULL 값을 포함할 수 없다. |
| CHECK(CK) | 해당 컬럼에 저장 가능한 데이터 값의 범위나 조건 지정 |
컬럼레벨의 제약조건을 생성하는 방법은 다음과 같으며, FOREIGN KEY 제약조건을 지정할 경우에는, FOREIGN KEY라는 키워드 대신에 REFERENCES라는 키워드를 반드시 명시해 주어야 한다.
| • PRIMARY KEY | <PRE> 컬럼명 데이터타입 CONSTRAINT constraint명 PRIMARY KEY [USING INDEX STORAGE (STORAGE parameter) TABLESPACE index_tablespace명]</PRE> |
| • UNIQUE | <PRE> 컬럼명 데이터타입 CONSTRAINT constraint명 UNIQUE [USING INDEX STORAGE (STORAGE parameter) TABLESPACE index_tablespace명]</PRE> |
| • FOREIGN KEY | <PRE> 컬럼명 데이터타입 CONSTRAINT constraint명 REFERENCES 참조테이블명 (참조컬럼명)</PRE> |
| • CHECK | <PRE> 컬럼명 데이터타입 CONSTRAINT constraint명 CHECK (조건)</PRE> |
| • NOT NULL | <PRE> 컬럼명 데이터타입 CONSTRAINT constraint명 NOT NULL</PRE> |
• constraint명은 생략 가능하며, 오라클 서버가 자동적으로 constraint명을 부여한다.
이때 부여되는 형식은 SYS_Cnnn 형태로 부여된다.
그러나 알기 쉽게 하기 위해 사용자가 정의한 constraint명을 부여하는 것이 보다 편리하다.
일반적으로 constraint명은 테이블명_컬럼명_constraint약자를 사용한다.
예를 들어, abc 테이블의 name 컬럼을 PRIMARY KEY로 선언할 때 이의 constraint 명은 abc_name_pk와 같이 부여한다.
• PRIMARY KEY, UNIQUE와 같은 제약조건은 자동으로 UNIQUE INDEX가 생성된다. 이때, USING INDEX를 사용하여 오라클 서버가 사용할 INDEX의 크기 및 테이블스페이스를 지정할 수 있다.
2) 테이블 레벨 무결성제약조건
• 하나 이상의 컬럼을 참조하거나 하나의 컬럼에 두 개 이상의 제약조건을 지정하는 경우 사용한다.
• 사용자는 무결성 제약조건의 이름을 지정할 수 있으며 지정하지 않으면 SYS_Cn 형태로 자동적으로 생성된다.
• USER_CONS_COLUMNS 데이터 딕셔너리를 통해서 조회할 수 있다.
• 특정 컬럼과 독립적으로 정의되므로 반드시 제약조건이 적용되는 컬럼을 괄호 안에 명시해야 한다.
• NOT NULL 제약조건은 정의되지 않는다.(위 표에서 4가지(PK,FK,UK,CK)만 가능함)
테이블 레벨의 제약조건의 생성은 다음과 같다.
| <PRE> CREATE TABLE 테이블명 (컬럼명 데이터타입, ...... 컬럼명 데이터타입,</PRE> | |
| • PRIMARY KEY 생성 | <PRE> CONSTRAINT constraint명 PRIMARY KEY(컬럼명1,...)</PRE> |
| • FOREIGN KEY 생성 | <PRE> CONSTRAINT constraint명 FOREIGN KEY(컬럼명) REFERENCES 참조테이블명(참조컬럼명)</PRE> |
| • UNIQUE CONSTRAINT 생성 | <PRE> CONSTRAINT constraint명 UNIQUE(컬럼명1,...)</PRE> |
| • CHECK CONSTRAINT 생성 | <PRE> CONSTRAINT constraint명 CHECK(조건)</PRE> |
데이터 조작시 무결성 제약조건의 적용
• DML에서 데이터의 무결성을 유지하기 위하여 테이블의 정의에 기술한 제약조건의 효력이 발생한다.
• 참조 무결성 제약조건을 위반한 경우 오류를 발생한다.
• 무결성 제약조건을 위반한 경우 오류를 발생한다.
• 자식 테이블에 입력하려고 하는 값이 부모 테이블에 없는 경우 오류를 발생한다.
• 자식 테이블에서 수정하려고 하는 값이 부모 테이블에 없는 경우 오류가 발생한다.
• 부모 테이블에서 삭제하려고 하는 값이 자식 테이블에서 참조되는 경우 오류를 발생한다.
• 기타 제약조건에 위배되는 데이터를 입력, 수정, 삭제하는 경우에 제약조건 오류가 발생한다.
• user_constraints 뷰를 통해서 부모와 자식 테이블관계를 알 수 있다. <PRE>SQL> select constraint_name,constraint_type, 2 table_name,r_constraint_name 3 from user_constraints; CONSTRAINT_NAME C TABLE_NAME R_CONSTRAINT_NAME-------------------- - ---------- --------------------FK_DEPTNO R EMP PK_DEPTPK_DEPT P DEPTPK_EMP P EMP SQL> </PRE>
기존 테이블에 무결성제약조건의 추가방법
무결성 제약조건은 기존 테이블ㅇ ㅔ대해서도 추가로 생성할 수 있다. ALTER TABLE ... ADD CONSTRAINT 문은 기존 테이블에 제약조건을 추가하기 위한 명령문이다. 하지만, NOT NULL 무결성 제약조건의 추가는 'NULL 허용' 상태를 'NULL 입력불가' 상태로 변경하는 것을 의미하므로 ALTER TABLE ... MODIFY 문을 사용해야 한다.
<PRE>【형식】 ALTER TABLE 테이블명 ADD [CONSTRAINT 제약조건명] 제약조건타입 (컬럼명);
</PRE><PRE>SQL> alter table test 2 add constraint test_tel_pk primary key(tel); 테이블이 변경되었습니다. SQL>SQL> select * from user_cons_columns; OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAME POSITION--------------- -------------------- ---------- -------------------- ----------SCOTT PK_DEPT DEPT DEPTNO 1SCOTT PK_EMP EMP EMPNO 1SCOTT FK_DEPTNO EMP DEPTNO 1SCOTT TEST_TEL_PK TEST TEL 1 SQL></PRE>
1) PRIMARY KEY (PK)
• 테이블에 대한 기본 키를 생성한다.
• 기본키는 테이블당 하나만 존재한다. 그러나, 반드시 하나의 컬럼으로만 구성되는 것은 아니다.
• 테이블에서 각 행을 유일하게 식별하는 컬럼 또는 컬럼의 집합이다. NULL 값이 입력될 수 없고, 이미 테이블에 존재하고 있는 데이터를 다시 입력할 수 없다. 즉, UNIQUE와 NOT NULL 제약조건을 결합한 개념이다.
• UNIQUE INDEX가 자동으로 만들어 진다. 데이터를 select할 때 where절에 대한 조건으로 INDEX가 생성되어 있는 컬럼이 사용되면 오라클은 자동으로 그 INDEX를 사용하여 데이터를 빠르게 찾아낸다. 그러므로 한 개의 테이블에는 primary key가 하나밖에 존재할 수 없다. user_indexes 뷰를 이용하여 인덱스를 확인할 수 있다.
<PRE>【컬럼레벨의 형식】 컬럼명 데이터타입 [CONSTRAINT constraint명] PRIMARY KEY(컬럼명,...)</PRE><PRE>【테이블레벨의 형식】 컬럼명 데이터타입, 컬럼명 데이터타입, ... [CONSTRAINT constraint명] PRIMARY KEY(컬럼1명, 컬럼2명,...)</PRE>
• constraint를 추가시에 constraint명을 생략하면 오라클 서버가 자동적으로 constraint명을 부여한다.
• 일반적으로 constraint명은 '테이블명_컬럼명_constraint약자'처럼 기술한다.
【예제】 constraint명을 생략한 경우
<PRE>SQL> create table test(id number(13) primary key); 테이블이 생성되었습니다. SQL> select constraint_name,table_name,r_constraint_name, constraint_type 2 from user_constraints; CONSTRAINT_NAME TABLE_NAME R_CONSTRAINT_NAME C-------------------- ---------- -------------------- -SYS_C005273 TEST P SQL></PRE>
【예제】 id 컬럼에 PRIMARY KEY 제약조건을 컬럼레벨로 부여하는 예
| 컬럼명 | 데이터타입 | constraint | SQL 문 |
|---|---|---|---|
| id | number(4) | primary key | <PRE>SQL> create table aa( 2 id number(4) constraint aa_id_pk PRIMARY KEY, 3 name varchar2(10), 4 no number(4)); Table created. SQL> </PRE> |
| name | varchar2(10) | ||
| no | number(4) |
【예제】 no 컬럼에 PRIMARY KEY 제약조건을 테이블레벨로 부여하는 예
| 컬럼명 | 데이터타입 | constraint | SQL 문 |
|---|---|---|---|
| no | number(4) | primary key | <PRE>SQL> create table bb( 2 no number(4), 3 sno number(4), 4 name varchar2(10), 5 count number(7), 6 constraint bb_no_pk PRIMARY KEY(no)); Table created. SQL> </PRE> |
| sno | number(4) | ||
| name | varchar2(10) | ||
| count | number(7) |
2) FOREIGN KEY (FK)
두 테이블 A,B에서 테이블 B의 기본키가 테이블A의 외래키(Foreign key)이다.
• 부모, 자식 테이블간의 행 사이에 일관성을 유지하기 위한 제약조건이다.
• 부모 테이블은 참조를 당하는 쪽이고, 자식 테이블은 참조하는 쪽이다.
• FOREIGN KEY 제약조건은 참조하는 자식 테이블에서 하나 이상의 컬럼에 대해 선언한다. 이때 참조되는 테이블의 컬럼의 수와 데이터타입이 같아야 한다.(부모 테이블과 자식 테이블의 참조하는 컬럼과 참조 당하는 컬럼의 데이터 타입은 일치해야 한다.)
• 부모 테이블이 먼저 생성된 후 자식 테이블(foreign key를 포함하는 테이블)이 생성되어야 한다.
• FOREIGN KEY는 부모 테이블의 PRIMARY KEY, UNIQUE만 참조할 수 있고, 컬럼의 값과 일치하거나 NULL 값이어야 한다.
• ON DELETE CASCADE 옵션을 이용하면 부모 테이블의 행이 삭제될 때 이를 참조한 자식 테이블의 행을 동시에 삭제할 수 있다.
• ON DELETE SET NULL은 자식 테이블이 참조하는 부모 테이블의 값이 삭제되면 자식 테이블의 값을 NULL 값으로 변경시킨다.
• 참조 무결성 제약조건에서 부모 테이블의 참조 키 컬럼에 존재하지 않는 값을 자식 테이블에 입력하면 오류가 발생한다.
<PRE>【컬럼레벨의 형식】 컬럼명 데이터타입 CONSTRAINT constraint명 REFERENCES 참조테이블명 (참조컬럼명) [ON DELETE CASCADE | ON DELETE SET NULL]</PRE><PRE>【테이블레벨의 형식】 컬럼명 데이터타입, 컬럼명 데이터타입, ... CONSTRAINT constraint명 FOREIGN KEY(컬럼) REFERENCES 참조테이블명 (참조컬럼명) [ON DELETE CASCADE | ON DELETE SET NULL]</PRE><PRE>【예제】테이블레벨로 참조토록 한 경우SQL> ALTER TABLE aa ADD (CONSTRAINT aa_no_fk FOREIGN KEY(no) REFERENCES orders(no));</PRE>
• 테이블 생성 후에 constraint의 추가는 테이블 레벨로 해야 한다.
【예제】aa 테이블의 id 컬럼을 사용한 컬럼레벨로 참조토록함
| 컬럼명 | 데이터 타입 | constraint |
|---|---|---|
| catalogno | number(4) | primary key |
| name | varchar2(10) | |
| no | number(4) | foreign key |
| <PRE>SQL> create table catalog( 2 catalogno number(4) CONSTRAINT catalog_catalogno_pk PRIMARY KEY, 3 name VARCHAR2(10), 4 no NUMBER(4) CONSTRAINT catalog_no_fk REFERENCES aa(id)); Table created. SQL> select constraint_name,constraint_type 2 from user_constraints 3 where table_name='CATALOG'; CONSTRAINT_NAME C------------------------------ -CATALOG_CATALOGNO_PK PCATALOG_NO_FK R SQL> </PRE> | ||
FOREIGN KEY 생성시 주의사항
• 참조하고자 하는 부모 테이블을 먼저 생성해야 한다.
• 참조하고자 하는 컬럼이 PRIMARY KEY 또는 UNIQUE 제약조건이 있어야 한다.
• 테이블 사이에 PRIMARY KEY와 FOREIGN KEY가 정의 되어 있으면, primary key 삭제시 foreign key 컬럼에 그 값이 입력되어 있으면 삭제가 안된다. (단, FK 선언때 ON DELETE CASCADE나 ON DELETE SET NULL옵션을 사용한 경우에는 삭제된다.)
• 부모 테이블을 삭제하기 위해서는 자식 테이블을 먼저 삭제해야 한다.
<PRE>【예제】자식 테이블(catalog)이 먼저 삭제후 부모 테이블(aa)을 삭제해야 함SQL> drop table aa;drop table aa *ERROR at line 1:ORA-02449: unique/primary keys in table referenced by foreign keys SQL> drop table catalog; Table dropped. SQL> drop table aa; Table dropped. SQL> </PRE>
| DML 문 | 부모 테이블 | 자식 테이블 |
|---|---|---|
| INSERT | 참조키 값이 유일한 경우에만 가능 | 외래 키 값이 참조 키 값 중의 하나와 일치하거나 NULL인 경우에 가능 |
| UPDATE | 참조 키 값을 참조하는 자식 테이블의 컬럼 값이 없는 경우에만 가능 | 수정되는 외래 키 값이 참조 키 값 중의 하나와 일치할 경우에만 가능 |
| DELETE RESTRICT | 참조 키 값을 참조하는 자식 테이블의 컬럼 값이 없는 경우에만 가능 | 항상 가능 |
| DELETE CASCADE | 항상 가능 | 항상 가능 |
3) UNIQUE KEY (UK)
Table에서 지정한 컬럼의 데이터가 중복되지 않고 유일하다.
• primary key가 아닌 경우라도 컬럼내의 모든 값이 유일해야할 경우 사용한다.
• 중복된 값을 가지는 행이 존재할 수 없다.
• PRIMARY KEY와 유사하나 NULL값을 허용한다.
• 내부적으로 UNIQUE INDEX를 만들어 처리한다. 즉, 유일 키 제약조건이 정의되면 테이블에 제약조건과 같은 이름의 인덱스가 자동적으로 생성된다. user_indexes 뷰를 이용하여 인덱스를 확인할 수 있다.
• PRIMARY KEY와 UNIQUE KEY의 비교
| PRIMARY KEY | UNIQUE KEY |
|---|---|
| 한 테이블에 하나 | 한 테이블에 여러 개 가능 |
| 중복되지 않는 데이터(unique) | 중복되지 않는 데이터(UNIQUE) |
| NOT NULL | NULL 허용 |
| UNIQUE INDEX가 생성됨 | UNIQUE INDEX가 생성됨 |
<PRE>【컬럼레벨의 형식】 컬럼명 데이터타입 CONSTRAINT constraint명 UNIQUE</PRE><PRE>【테이블레벨의 형식】 컬럼명 데이터타입, 컬럼명 데이터타입, ... CONSTRAINT constraint명 UNIQUE(컬럼1명, 컬럼2명,...)</PRE><PRE>【예제】primary key는 P, Unique key는 U로 표시됨SQL> desc bb; Name Null? Type ----------------------------------------- -------- ---------------------------- NO NOT NULL NUMBER(4) SNO NUMBER(4) NAME VARCHAR2(10) COUNT NUMBER(7) SQL> alter table bb 2 ADD (constraint bb_sno_uk UNIQUE(sno)); Table altered. SQL> desc bb; Name Null? Type ----------------------------------------- -------- ---------------------------- NO NOT NULL NUMBER(4) SNO NUMBER(4) NAME VARCHAR2(10) COUNT NUMBER(7) SQL> select constraint_name,constraint_type 2 from user_constraints 3 where table_name='BB'; CONSTRAINT_NAME C------------------------------ -BB_NO_PK PBB_SNO_UK U SQL> </PRE>
4) CHECK (CK)
컬럼에서 허용 가능한 데이터의 범위나 조건을 지정하기 위한 제약조건이다.
• 하나의 컬럼에 대하여 여러 개의 CHECK 무결성 제약조건을 지정할 수 있다.
• CURRVAL, NEXTVAL과 같은 가상 컬럼이나 SYSDATE, USER와 같은 함수는 사용할 수 없다.
• 동일 테이블의 컬럼에서 다른 행을 참조할 수 있다.
• 컬럼에 입력되는 데이터를 검사해서 조건에 맞는 데이터만 입력되도록 한다.
• 조건은 where절과 유사하게 해당 컬럼에 저장되는 데이터 값의 범위, 특정 패턴의 숫자 또는 문자열뿐만 아니라 같은 테이블 내의 다른 컬럼도 참조할 수 있다.
• row(행)이 만족해야 하는 조건을 정의한다.
• user_constraints 뷰의 constraint_type에서 CHECK 제약조건이나, NOT NULL 제약조건이 모두 C로 표시되지만, 구체적으로 확인하기 위해서 search_condition 컬럼의 값으로 확인할 수 있다.
<PRE>【컬럼레벨의 형식】 컬럼명 데이터타입 CONSTRAINT constraint명 CHECK(컬럼명 조건)</PRE><PRE>【테이블레벨의 형식】 컬럼명 데이터타입, 컬럼명 데이터타입, ... CONSTRAINT constraint명 CHECK(컬럼명 조건)</PRE><PRE>【예제】 SQL> alter table bb 2 ADD (constraint bb_sno_ck check(sno between 1000 and 5000)); Table altered.SQL> select constraint_name,constraint_type,search_condition 2 from user_constraints 3 where table_name='BB'; CONSTRAINT_NAME C SEARCH_CONDITION------------------- - -------------------------------------BB_NO_PK PBB_SNO_UK UBB_SNO_CK C sno between 1000 and 5000 SQL> alter table bb 2 add (constraint bb_nake_nn check (name is not null)); 테이블이 변경되었습니다. SQL> select constraint_name,constraint_type,search_condition 2 from user_constraints 3 where table_name='BB'; CONSTRAINT_NAME C SEARCH_CONDITION------------------- - -------------------------------------BB_NO_PK PBB_SNO_UK UBB_SNO_CK C sno between 1000 and 5000BB_NAKE_NN C name is not null SQL> </PRE>
5) NOT NULL (NN)
테이블에서 지정한 컬럼의 데이터가 NULL 값을 갖지 못한다.
• NOT NULL 제약조건이 없는 컬럼은 디폴트값으로 NULL 값이 허용된다.
• 컬럼레벨에서만 기술이 가능하다.
• NOT NULL 제약조건이 정의된 컬럼에는 NULL 값이 올 수 없다.
• 기본적으로 테이블에 있는 모든 컬럼에는 NULL 값이 들어갈 수 있지만, 절대적으로 NULL이 입력되지 못하게 할 필요가 있을 경우도 있다.
• INSERT, UPDATE문의 실행시에 체크하여 삽입이나 갱신의 성공여부에 영향을 준다.
• user_constraints 뷰의 constraint_type에서 CHECK 제약조건이나, NOT NULL 제약조건이 모두 C로 표시되지만, 구체적으로 확인하기 위해서 search_condition 컬럼의 값이 IS NOT NULL으로 확인할 수 있다.
• 기존 데이터를 NULL로 수정하는 경우에는 오류가 발생한다.
<PRE>【컬럼레벨의 형식】 컬럼명 데이터타입 CONSTRAINT constraint명 NOT NULL</PRE><PRE>【테이블레벨의 형식】 컬럼명 데이터타입, 컬럼명 데이터타입, ... CONSTRAINT constraint명 CHECK(컬럼명 IS NOT NULL)</PRE>
• 기존 테이블에 NOT NULL 컬럼을 추가시에는 ADD보다는 MODIFY절을 사용하면 더 간단하게 부여할 수 있다.
<PRE>【예제】컬럼레벨
| 컬럼명 | 데이터타입 | constraint | SQL 문 |
|---|---|---|---|
| no | number(4) | not null | <PRE> SQL> create table test( 2 no number(4) constraint test_no_nn NOT NULL, 3 name varchar2(10)); Table created. SQL></PRE> |
| name | varchar2(10) | ||
| <PRE>SQL> select constraint_name,constraint_type 2 from user_constraints 3 where table_name='TEST'; CONSTRAINT_NAME C------------------------------ -TEST_NO_NN C SQL></PRE> | |||
<PRE>【예제】테이블레벨SQL> select constraint_name,constraint_type 2 from user_constraints 3 where table_name='TEST'; CONSTRAINT_NAME C------------------------------ -TEST_NO_NN C SQL> alter table test 2 add (constraint test_name_nn check (name IS NOT NULL)); Table altered. SQL> select constraint_name,constraint_type,search_condition 2 from user_constraints 3 where table_name='TEST'; CONSTRAINT_NAME C SEARCH_CONDITION------------------- - -------------------------------------TEST_NO_NN C "NO" IS NOT NULLTEST_NAME_NN C name is not null SQL> </PRE> </PRE>
constraint의 비활성화에 대하여 살펴보자.
• 기존에 선언된 제약조건을 비활성화시킬 수 있다.
• constraint를 체크하지 않는다.
• 관련된 INDEX도 DROP된다. user_indexes 뷰를 사용하여 확인할 수 있다.
• 대량의 데이터를 테이블에 입력하거나, batch 작업시에 작업의 성능을 향상하기 위하여 사용한다.(primary key를 삭제하지 않고도 삭제한 것과 같은 기능을 하게 된다)
<PRE>【형식】 ALTER TABLE 테이블명 DISABLE CONSTRAINT constraint명 [CASCADE];</PRE>
• 여기서 cascade옵션은 비활성화시키려는 constraint를 참조하는 다른 모든 constraint들도 비활성화시킨다.
Disable 절을 가진 ALTER TABLE 문장을 사용하여 삭제 또는 재생성없이 constraint를 비활성화 시킬 수 있다.
<PRE>【예제】SQL> create table dept( 2 deptno number(4) not null primary key, 3 dname varchar2(10), 4 loc varchar2(10)); Table created. SQL> desc dept; Name Null? Type ----------------------------------------- -------- ---------------------------- DEPTNO NOT NULL NUMBER(4) DNAME VARCHAR2(10) LOC VARCHAR2(10) SQL> select constraint_name, table_name,status from user_constraints 2 where table_name='DEPT'; CONSTRAINT_NAME TABLE_NAME STATUS-------------------- -------------------- --------SYS_C005837 DEPT ENABLEDSYS_C005838 DEPT ENABLED SQL> alter table dept disable primary key cascade; Table altered. ☜ cascade옵션을 사용한 이유는 dept 테이블의 primary key는 emp 테이블의 deptno 컬럼에 의해 참조되고 있기 때문임SQL> select constraint_name, table_name, status from user_constraints 2 where table_name='DEPT'; CONSTRAINT_NAME TABLE_NAME STATUS-------------------- -------------------- --------SYS_C005837 DEPT ENABLEDSYS_C005838 DEPT DISABLED SQL>【예제】SQL> alter table dept 2 add (constraint dept_dname_nn check (dname IS NOT NULL)); Table altered. ☜ ALTER TABLE dept MODIFY dname NOT NULL);처럼 해도 됨, 다만 constraint명을 지정할 수 없음 SQL> select constraint_name, table_name,status from user_constraints 2 where table_name='DEPT'; CONSTRAINT_NAME TABLE_NAME STATUS-------------------- -------------------- --------SYS_C005837 DEPT ENABLEDSYS_C005838 DEPT DISABLEDDEPT_DNAME_NN DEPT ENABLED SQL> alter table dept 2 disable constraint dept_dname_nn cascade; Table altered. SQL> select constraint_name, table_name,status from user_constraints 2 where table_name='DEPT'; CONSTRAINT_NAME TABLE_NAME STATUS-------------------- -------------------- --------SYS_C005837 DEPT ENABLEDSYS_C005838 DEPT DISABLEDDEPT_DNAME_NN DEPT DISABLED SQL> </PRE>
• 기존에 선언된 constraint의 비활성화 상태를 활성화시킨다.
• constraint가 check 기능을 다시 수행한다.
• 관련된 index가 다시 생성된다.
• 테이블의 행(row)이 constraint에 위배되는지 않되는지 체크한다.
• check가 끝날 때까지 테이블에 LOCK이 걸린다.
• constraint가 생성되면, 디폴트로 Enable(활성화)된다.
• utlexcpt.sql은 제약조건에 위배되어 활성화 되지 않은 행을 보관하는 테이블로 이를 수정하여 활성화 시킬 수 있다.
<PRE>【형식】 ALTER TABLE 테이블명 ENABLE CONSTRAINT constraint명;</PRE>
• constraint가 disable된 후 constraint에 위배되는 데이터가 들어간 경우, 다시 constraint를 ENABLE시키려하면 error가 발생한다.
• cascade되어 있는 primary key를 disable시킨 후, enable하면 PK는 enable이 되지만, 자식 테이블의 FK는 여전히 disable 상태로 있게 된다.
• 결론적으로 constraint의 Enable과 Disable에서 Disable은 CASCADE 즉, 종속적 비활성화가 일어나지만, Enable은 CASCADE가 되지 않는다.
constraint의 삭제
• 제약조건은 수정할 수 없으며, 기존의 constraint를 삭제 후 재 생성하여야 한다.
• constraint를 삭제할려면, 직접 constraint명을 사용해서 삭제하거나 또는 constraint가 포함된 테이블을 삭제하면 그 테이블에 속한 constraint도 함께 삭제된다.
• 무결성 constraint를 삭제할 때, 그 constraint는 더 이상 서버에 의해서 적용되지 않기 때문에 data dictionary에서 확인할 수 없다.
• primary key는 테이블당 하나만 존재하므로 삭제시 constraint명을 지정하지 않아도 primary key 제약조건이 삭제된다.
예) SQL> ALTER TABLE 테이블명 DROP PRIMARY KEY;
방법1)
ALTER TABLE 테이블명
DROP [CONSTRAINT constraint명 | PRIMARY KEY | UNIQUE(컬럼명)]
[CASCADE];
CASCADE옵션은 참조하는 FOREIGN KEY가 있을 때 사용한다.
방법2)
DROP TABLE 테이블명 CASCADE CONSTRAINTS;
테이블과 그 테이블을 참조하는 foreign key를 동시에 삭제할 수 있다.
방법3)
DROP TABLESPACE 테이블스페이스명
INCLUDING CONTENTS
CASCADE CONSTRAINTS;
이 방법은 테이블이 다른 테이블스페이스에 있는 테이블의 FOREIGN KEY에 의하여 참조되는 경우 TABLESPACE까지 함께 삭제하는 경우이다.
<PRE>【예제】SQL> select * from user_cons_columns; OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAME POSITION--------------- -------------------- ---------- -------------------- ----------SCOTT PK_DEPT DEPT DEPTNO 1SCOTT PK_EMP EMP EMPNO 1SCOTT FK_DEPTNO EMP DEPTNO 1SCOTT TEST_TEL_PK TEST TEL 1 SQL> alter table test 2 drop constraint test_tel_pk cascade; 테이블이 변경되었습니다. SQL> select * from user_cons_columns; OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAME POSITION--------------- -------------------- ---------- -------------------- ----------SCOTT PK_DEPT DEPT DEPTNO 1SCOTT PK_EMP EMP EMPNO 1SCOTT FK_DEPTNO EMP DEPTNO 1 SQL>SQL> select constraint_name,constraint_type,table_name 2 from user_constraints; CONSTRAINT_NAME C TABLE_NAME------------------------------ - ------------------------------TEST_NAME_NN C TESTTEST_NO_NN C TESTBB_SNO_CK C BBBB_NO_PK P BBBB_SNO_UK U BB 5 rows selected. SQL> alter table test 2 drop constraint test_name_nn; Table altered. SQL> alter table bb 2 drop primary key;☜ 다른 방법으로, alter table bb drop constraint bb_no_pk;처럼해도 됨 Table altered. SQL> alter table bb 2 drop constraint bb_sno_uk; Table altered. SQL> alter table bb 2 drop constraint bb_sno_ck; Table altered. SQL> select constraint_name,constraint_type,table_name 2 from user_constraints; CONSTRAINT_NAME C TABLE_NAME------------------------------ - ------------------------------TEST_NO_NN C TEST SQL></PRE>
deferred constraint
Deferred constraint는 transaction이 COMMIT된 상태에서 수행된 모든 DML 작업들을(insert, delete, update, select 문) 한꺼번에 처리한다.
이때 constraint에 위배 된다면, transaction 전체를 ROLLBACK 시킨다.
일반적으로 DML 작업의 끝에서 constraint를 처리하는 것을 IMMEDIATE CONSTRAINT라고 한다.
DEFERRED CONSTRAINT는 FOREIGN KEY가 설정된 부모 테이블과 자식 테이블 관계에서 유용하게 사용할 수 있다.
constraint 관리
Deferred constraint의 상태에 따라 INDEX를 다르게 생성한다.
단계1 : 현재의 primary key, unique constraint 상태가 DISABLE이라면
INDEX가 필요 없다. ENABLE 상태라면 단계2로 넘어간다.
단계2 : 기존의 INDEX가 있다면 별도로 만들지 않는다. INDEX가 없다면 다음 단게로 넘어간다.
단계3 : constraint의 상태가 DEFERRED CONSTRAINT라면 INDEX에 중복값이 허용되지 않으므로
NON UNIQUE INDEX가 생성되고, DEFERRED CONSTRAINT가 아니라면 UNIQUE INDEX가 생성된다.
또한 테이블 수정이나 삭제 시 참조하는 자식 테이블이 있을 경우에는 CASCADE CONSTRAINTS 옵션을 사용해야 한다.
DROP TABLE table명 CASCADE CONSTRAINTS;
그리고 tablespace 삭제시에 tablespace 내부의 object에서 foreign key가 존재할 경우에는CASCADE CONSTRAINTS 옵션을 사용해야 한다
DROP TABLESPACE tablespace명 INCLUDING CONTENTS CASCADE CONSTRAINTS;
constraint 정보조회
• USER_CONSTRAINTS와 USER_CONS_COLUMNS를 조회하여 사용자의 모든 constraint 정보를 확인할 수 있다.
• DBA_CONSTRAINTS와 DBA_CONS_COLUMNS를 조회하여 데이터베이스의 모든 constraint 정보를 확인할 수 있다.
• DBA_CONSTRAINTS 뷰는 데이터베이스 내부의 모든 constraint의 세부정보로 constraint명과 상태등에 관한 정보.
• DBA_CONS_COLUMNS 뷰는 CONSTRAINT와 연관된 컬럼의 정보.
<PRE>$ sqlplus '/as sysdba' SQL> desc dba_constraints; Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER NOT NULL VARCHAR2(30) CONSTRAINT_NAME NOT NULL VARCHAR2(30) CONSTRAINT_TYPE VARCHAR2(1) TABLE_NAME NOT NULL VARCHAR2(30) SEARCH_CONDITION LONG R_OWNER VARCHAR2(30) R_CONSTRAINT_NAME VARCHAR2(30) DELETE_RULE VARCHAR2(9) STATUS VARCHAR2(8) DEFERRABLE VARCHAR2(14) DEFERRED VARCHAR2(9) VALIDATED VARCHAR2(13) GENERATED VARCHAR2(14) BAD VARCHAR2(3) RELY VARCHAR2(4) LAST_CHANGE DATE INDEX_OWNER VARCHAR2(30) INDEX_NAME VARCHAR2(30) INVALID VARCHAR2(7) VIEW_RELATED VARCHAR2(14) SQL> desc dba_cons_columns; Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER NOT NULL VARCHAR2(30) CONSTRAINT_NAME NOT NULL VARCHAR2(30) TABLE_NAME NOT NULL VARCHAR2(30) COLUMN_NAME VARCHAR2(4000) POSITION NUMBER SQL> DBA_CONSTRAINTS 뷰에서 CONSTRAINT_TYPE의 의미는 다음과 같다.
| constraint type | PRIMARY KEY | P |
| UNIQUE | U | |
| FOREIGN KEY | F | |
| CHECK, NOT NULL | C |
SQL> SELECT constraint_name,constraint_type,search_condition 2 FROM dba_constraints 3 WHERE table_name='TABLE1'; CONSTRAINT_NAME C SEARCH_CONDITION------------------------------ - -------------------------TABLE1_NAME_NN C "NAME" IS NOT NULLTABLE1_MAIL_UK UTABLE1_NO_PK P SQL> select b.constraint_name AS "PK", a.constraint_name AS "FK" 2 FROM dba_constraints a, dba_cons_columns b 3 WHERE a.owner='JIJOE' AND a.table_name='TABLE2' 4 AND a.r_owner=b.owner AND a.r_constraint_name=b.constraint_name 5 AND a.constraint_type='R'; PK FK------------------------------ ------------------------------TABLE1_NO_PK TABLE2_NO_FK SQL>
</PRE>일반적으로 dba_constraints와 dba_cons_columns을 join시켜 사용함으로써 필요한 정보를 획득한다. 특히 두 테이블 사이에 형성되는 FOREIGN KEY CONSTAINT를 확인하는데 이 두 뷰를 join시켜 사용하면 유용하다.
<PRE>SQL> list 1 select a.constraint_name, a.status, b.column_name 2 FROM dba_constraints a, dba_cons_columns b 3* WHERE a.owner=b.owner AND a.table_name='TABLE1'SQL> / CONSTRAINT_NAME STATUS COLUMN_NAME------------------------------ -------- --------------------TABLE1_NO_PK ENABLED NOTABLE1_MAIL_UK ENABLED NOTABLE1_NAME_NN ENABLED NOTABLE1_NO_PK ENABLED NAMETABLE1_MAIL_UK ENABLED NAMETABLE1_NAME_NN ENABLED NAMETABLE1_NO_PK ENABLED MAILTABLE1_MAIL_UK ENABLED MAILTABLE1_NAME_NN ENABLED MAILTABLE1_NO_PK ENABLED NOTABLE1_MAIL_UK ENABLED NO CONSTRAINT_NAME STATUS COLUMN_NAME------------------------------ -------- --------------------TABLE1_NAME_NN ENABLED NOTABLE1_NO_PK ENABLED JUMIN1TABLE1_MAIL_UK ENABLED JUMIN1TABLE1_NAME_NN ENABLED JUMIN1TABLE1_NO_PK ENABLED JUMIN2TABLE1_MAIL_UK ENABLED JUMIN2TABLE1_NAME_NN ENABLED JUMIN2TABLE1_NO_PK ENABLED ADDRESSTABLE1_MAIL_UK ENABLED ADDRESSTABLE1_NAME_NN ENABLED ADDRESSTABLE1_NO_PK ENABLED ID CONSTRAINT_NAME STATUS COLUMN_NAME------------------------------ -------- --------------------TABLE1_MAIL_UK ENABLED IDTABLE1_NAME_NN ENABLED IDTABLE1_NO_PK ENABLED IDTABLE1_MAIL_UK ENABLED IDTABLE1_NAME_NN ENABLED IDTABLE1_NO_PK ENABLED NAMETABLE1_MAIL_UK ENABLED NAMETABLE1_NAME_NN ENABLED NAMETABLE1_NO_PK ENABLED AATABLE1_MAIL_UK ENABLED AATABLE1_NAME_NN ENABLED AA 33 rows selected. SQL> </PRE>
무결성 제약 조건이 있는 상태에서 insert, update,delete시 ERROR발생
제약조건에 대한 정보조회
| all_constraints | user가 access할 수 있는 constraint정보 |
| all_cons_columns | user가 access할 수 있는 컬럼별 constraint정보 |
| user_constraints | user 소유 테이블의 constraint정보 |
| user_cons_columns | user 소유 테이블의 각 컬럼별 constraint정보 |
| dba_constraints | 모든 테이블에 대한 constraint정보 |
| dba_cons_columns | 모든 테이블에 대한 컬럼별 constraint정보 |
'용-ILE > DB-mysql / oracle' 카테고리의 다른 글
| [DB] 서브쿼리 여러값을 합치기 ?? 하위 쿼리 둘 이상 반환 어쩌구 일때? 여러행 한행 ? (0) | 2009.01.30 |
|---|---|
| [oracle] exp imp (export , import) 덤프 (0) | 2008.05.15 |
| [ oracle ] 오라클 sqlplus 에서 한글이 ??? 이렇게 깨져 나올때 (0) | 2008.05.15 |
| [ oracle ] rownum 슈도(Pseudo) 컬럼 사용 (0) | 2008.05.15 |
| [mysql] 비밀번호 변경 (0) | 2008.05.15 |
