SQL - 제약조건

제약조건 확인 : DESC USER_CONSTRAINTS;

• CONSTRAINT_NAME : 제약조건 이름
• CONSTRAINT_TYPE : 유형, P: Primary key), U(Unique), R(Reference), C(Check)
• TABLE_NAME
• SEARCH_CONDITION : CHECK 제약조건 내용
• R_CONSTRAINT_NAME : 참조 테이블의 PK이름
• DELETE_RULE : 참조 테이블의 PK컬럼이 삭제될 떄 사용되는 규칙,(NO ACTION, SET NULL, CASCADE등)

 

Q1 ) EMP 테이블의 테이블 이름, 제약조건, 타입을 확인해보자

SQL> SELECT TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE
  2  FROM USER_CONSTRAINTS
  3  WHERE TABLE_NAME = 'EMP';

TABLE_NAME      CONSTRAINT_NAME                                              CO
--------------- ------------------------------------------------------------ --
EMP             PK_EMP                                                       P
EMP             FK_DEPTNO                                                    R

 

 

삭제 RULE

• ON DELETE CASCADE : 대상 데이터를 삭제하고, 해당 데이터를 참조하는 데이터도 삭제
• ON DELETE SET NULL : 대상 데이터를 삭제하고, 해당 데이터를 참조하는 데이터는 NULL로 변환
• ON DELETE RESTRICTED : 삭제 대상 데이터를 참조하는 데이터가 존재하면 삭제할 수 없다(DELETE 기본)

 

수정 RULE

• ON UPDATE CASCADE : 해당 데이터를 수정하면, 해당 데이터를 참조하는 데이터도 수정

 

테이블 생성

CREATE TABLE 테이블이름 (

                    컬럼명1 데이터타입 [제약조건] ...);

 

테이블 수정

ALTER TABLE 테이블이름

ADD 칼럼명 데이터타입[제약조건]

ADD COSTRAINT 제약조건명 제약조건(컬럼명)

MODIFY 컬럼명 데이터타입

DROP COLUMN 컬럼명 [CASCADE CONSTRAINTS]

DROP PRIMARY KEY [CASCADE] | UNION(컬렴명...) [CASCADE] | CONSTRAINT 제약조건명[CASCADE]

 

이름변경

ALTER TABLE 기본 테이블명 RENAME TO 새 테이블 이름

RENAME 기존 테이블명 TO 새 테이블 명

 

ALTER TABLE 테이블명 RENAME COLUMN 기본칼럼명 TO 새칼럼명

ALTER TABLE 테이블명 RENAME CONSTRAINT 기존제약조건명 TO 새제약조건명

 

 

테이블 목사

서브쿼리를 이용한 테이블 생성 및 행 복사

서브쿼리를 이용해서 복사한 경우 NOT NULL을 제외한 제약 조건은 복사 안됨

(NOT NULL 제약 조건도 SYS_****로 복사된다.)

CREATE TABLE 테이블명[컬럼명] AS 서브쿼리

CREATE TABLE 테이블명 1 AS SELECT * FROM 테이블명 WHERE 1=0

 

 

 

Q2) 테이블 생성

SQL> CREATE TABLE TEST
  2  ( ID NUMBER(5), NAME CHAR(10), ADDRESS VARCHAR(50));

Table created.

SQL> DESC TEST;
 Name                                                                                                              Null?    Type
 ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
 ID                                                                                                                         NUMBER(5)
 NAME                                                                                                                       CHAR(10)
 ADDRESS

 

 

Q3) ID 칼럼을 USR 컬럼으로 변경하자

SQL> ALTER TABLE TEST
  2  RENAME COLUMN ID TO USR
  3  ;

 

 

Q4) 휴지통 비우기

SQL> DROP TABLE TEST01 PURGE;

Table dropped.

 

 

Q5) UNIQUE 테이블 생성/조건삭제

SQL> CREATE TABLE UTABLE(
  2  ID NUMBER CONSTRAINT U_ID_U UNIQUE,
  3  NAME VARCHAR2(10));

SQL> INSERT INTO UTABLE VALUES(NULL,'111');

1 row created.

SQL> INSERT INTO UTABLE VALUES(NULL,'111');

1 row created.

SQL> INSERT INTO UTABLE VALUES(NULL,'111');

1 row created.

SQL> ALTER TABLE UTABLE DROP CONSTRAINT U_ID_U;

Table altered.

# NULL값은 실제로 다 다른 값이므로 UNIQUE 조건에 위배되지 않음

 

 

Q6) 조건 다시 생성(그룹화) 후 다시 넣어보자

SQL> ALTER TABLE UTABLE ADD CONSTRAINT U_ID_U UNIQUE(ID,NAME);
SQL> INSERT INTO UTABLE VALUES(NULL,'111');

1 row created.

SQL> INSERT INTO UTABLE VALUES(NULL,'111');
INSERT INTO UTABLE VALUES(NULL,'111')
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.U_ID_U) violated

SQL> INSERT INTO UTABLE VALUES(NULL,NULL);

1 row created.

SQL> INSERT INTO UTABLE VALUES(NULL,NULL);

1 row created.

# 묶여있는 경우에는(NULL,'111')의 경우 한 묶음으로 보기 떄문에 중복을 허용하지 않는다.

   즉(COLUMN,COLUMN)을 한 덩어리로 보기때문에 (NULL,NULL)은 계속 집어넣을 수 있다.

 

 

Q7) 기본키 칼럼 복수개 선언