오라클 테이블, 외래 키 제약조건(Foreign Key Constraints)

 

오라클 테이블, 외래 키 제약조건(Foreign Key Constraints)

외래 키foreign key는 테이블의 컬럼값이 자신 또는 다른 테이블의 기본 키와 고유 키 값만 오도록 할 때 지정하는 제약조건입니다. 따라서 외래 키로 지정할 때 참조되는 테이블 컬럼은 기본 키 또는 고유 키이어야 합니다. 물론 외래키 컬럼은 NULL을 허용할 수 있으며 이 경우 NULL 값은 입력될 수 있습니다. 즉 외래키는 값이 없는것(NULL)은 이해 하지만 값이 들어 올때는 부모 테이블(참조되는 테이블)의 기본키, 고유키 값만 들어 올 수 있다고 정의하는 것 입니다. 

사원테이블emp의 deptno 컬럼은 NULL을 허용하면서 부서테이블dept의 PK 컬럼(deptno)을 참조하는 외래키로 NULL 값은 입력 될 수 있지만 값이 들어 온다면 부서 테이블의 기본키인 deptno 값 중에서만 들어와야 한다는 것입니다. 이때 사원테이블이 자식, 부서테이블이 부모 테이블이 되며 사원테이블의 deptno 컬럼은 외래키foreign key, 부서테이블의 deptno 컬럼은 참조키referencial key라고 합니다.

외래키와 참조키가 하나의 테이블에 존재할 수 있는데 이를 자기참조(Self Reference)라고 하고 이 경우 부모 테이블과 자식 테이블은 동일합니다. 예를 들어 사원테이블에서 관리자(mgr) 컬럼은 외래 키 컬럼으로 자기자신 테이블(EMP)의 사번(empno) 컬럼을 참조합니다. 이러한 구조를 가진 경우 관리자의 이름을 알려면 관리자의 사번으로 사원 테이블을 검색해야 하므로 셀프 조인self jon이 자주 사용됩니다.

 

사용자가 작성한 USER_CONSTRAINTS 뷰를 통해 외래키 제약조건들을 조회하고 외래키를 삭제 후 다시 생성하는 실습을 해보겠습니다.

실습

USER_CONSTRAINTS 딕셔너리 뷰를 통해 사용자가 생성한 제약조건을 조회할 수 있습니다.

사원(EMP) 테이블의 제약조건을 조회 합시다.

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE 

FROM   USER_CONSTRAINTS

WHERE TABLE_NAME = 'EMP';

<실행결과>

	CONSTRAINT_NAME	CONSTRAINT_TYPE
1	FK_DEPTNO	R
2	FK_MGR	R
3	PK_EMP	P

R은 참조무결성, 외래 키를 의미하고, P는 기본 키를 의미합니다. EMP 테이블에서 deptno 컬럼은 외래키로 DEPT 테이블의 deptno 컬럼을 참조하고, mgr 컬럼은 자기자신 테이블인 EMP 테이블의 empno 컬럼을 참조합니다. 

부서(DEPT) 테이블의 제약조건을 조회 합시다.

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE 

FROM   USER_CONSTRAINTS

WHERE TABLE_NAME = 'DEPT';

<실행결과>

	CONSTRAINT_NAME	CONSTRAINT_TYPE
1	PK_DEPT	P

실습

EMP 테이블에서 외래키를 삭제해보고 다시 만들어 보겠습니다.

사원(EMP) 테이블의 deptno 컬럼에 걸려있는 외래 키를 삭제 후 다시 생성하세요 외래키 가끔은 외래키 제약조건을 비활성화, 활성화, 삭제 하는 경우도 있으므로 생성시 이름을 부여하여 생성하세요.

ALTER TABLE emp DROP CONSTRAINT fk_deptno; ALTER TABLE emp ADD CONSTRAINT fk_deptno FOREIGN KEY(deptno) REFERENCES dept(deptno);

실습

실습을 위해 고객과 매출 테이블을 생성하고 데이터를 입력 합시다.

고객(CUSTOMER), 매출(SALES) 테이블을 생성합니다. 1:N 관계로 CUSTOMER가 부모 테이블, SALES가 자식 테이블입니다.

DROP TABLE CUSTOMER;

DROP TABLE SALES;

CREATE TABLE CUSTOMER (

    ID NUMBER PRIMARY KEY,

    NAME VARCHAR2(50) NOT NULL 

);

CREATE TABLE SALES (

    ID NUMBER PRIMARY KEY,

    CUSTOMER_ID NUMBER DEFAULT 9999 REFERENCES CUSTOMER(ID) ,

    SALE_DATE DATE,    

    AMOUNT NUMBER

);

데이터 입력은 부모 테이블부터 입력해야 합니다. 자식 테이블 SALES는 기본 키 컬럼인 id가 부모 테이블의 id 컬럼을 참조하므로 부모 테이블인 CUSTOMER 없이 SALES 데이터가 만들어질 수 없습니다. 

부모테이블부터 데이터를 입력 합시다.

INSERT INTO CUSTOMER VALUES  (1, '1길동');

INSERT INTO SALES VALUES  (1, 1, SYSDATE, 100000);

COMMIT;

<실행결과>

커밋 완료.

실습

참조무결성을 테스트 하기 위해 부모테이블(CUSTOMER)에 없는 id 값으로 자식테이블(SALES)에 입력을 시도해 보겠습니다.

CUSTOMER에 없는 id ‘2’ 로 SALES 테이블에 새행을 입력합시다. 무결성 제약조건 오류가 발생합니다.

INSERT INTO SALES VALUES  (2, 2,SYSDATE, 100000);

<실행결과>

ORA-02291: 무결성 제약조건(SCOTT.SYS_C007527)이 위배되었습니다- 부모 키가 없습니다.

 

#외래키, #ForeignKey, #오라클케이블, #테이블, #오라클강좌, #오라클교육​

오라클 테이블, 고유 키 제약조건(Unique Key Constraints)

 

오라클 테이블, 고유 키 제약조건(Unique Key Constraints)

 

컬럼의 값은 유일해야 한다는 제약조건입니다. 최대 32개 이내 컬럼에 정의할 수 있으며 NULL값은 항상 유일하므로 입력이 가능합니다. 컬럼 레벨, 테이블 레벨(주로 복합 컬럼으로 제약조건을 만드는 경우) 모두에서 정의할 수 있습니다. 고유 키 제약조건이 지정되면 제약조건과 같은 이름의 인덱스가 별도의 저장공간에 자동으로 생성됩니다.

테이블을 생성할 때 UNIQUE 제약조건을 기술해보고 제약조건의 이름도 부여하거나 부여하지 않고 테이블을 생성해 보겠습니다. 이 제약조건이 있을 때 중복된 데이터가 입력되는지, NULL을 허용하는 컬럼에 NULL을 INSERT구문의 VALUES절에 기술해서 입력할 수 있습니다. 또한 오라클에서 UNIQUE 제약조건을 기술할 때 별도의 영역에 해당 컬럼의 데이터를 정렬하여 인덱스를 생성한다는 것 역시 확인해 보겠습니다.

실습

UNIQUE 제약조건을 걸어 테이블을 생성 후 중복된 데이터를 입력해 봅니다.

id, name, tel 컬럼을 가지는 T2 테이블을 생성하면서 tel 컬럼에 UNIQUE 제약조건을 걸어서 데이터 입력을 테스트 하세요.

CREATE TABLE t2 (     id       NUMBER CONSTRAINT pk_t2 PRIMARY KEY,     name VARCHAR2(100) NOT NULL,     tel      VARCHAR2(20)   UNIQUE ); INSERT INTO t2 VALUES  (1, '1길동', '010-1234-0000'); INSERT INTO t2 VALUES  (2, '2길동', '010-1234-0000');

<실행결과>

ORA-00001: 무결성 제약조건(SCOTT.SYS_C007508)에 위배됩니다.

전화번호는 고유해야 하는데 이미 같은 전화번호가 있어서 오류 메시지가 발생했습니다.

실습

컬럼에 NOT NULL 제약조건이 걸려 있지 않다면 NULL을 입력할 수 있습니다. NULL값은 고유하기 때문입니다.

tel 컬럼에 NULL값을 입력하세요.

INSERT INTO t2 VALUES  (2, '2길동', NULL); INSERT INTO t2 VALUES  (3, '3길동', NULL);

실습

UNIQUE 제약조건이 정의되면 오라클은 자동으로 별도 영역에 해당 컬럼을 인덱싱해둬 검색에서 좋은 성능을 냅니다. USER_INDEXES 딕셔너리 뷰에서 사용자가 생성한 인덱스를 확인 할 수 있습니다.

USER_INDEXES에서 T2 테이블에 생성되어 있는 인덱스를 확인합시다.

SELECT INDEX_NAME, TABLE_NAME 

FROM    USER_INDEXES

WHERE TABLE_NAME = 'T2';

<실행결과>

	INDEX_NAME	TABLE_NAME
1	PK_T2	T2
2	SYS_C007508	T2

UNIQUE 제약조건을 기술할 때 별도로 이름을 주지않아 오라클에서 자동으로 SYS_C007508을 이름으로 부여했습니다. 인덱스는 활성화 또는 비활성화, 삭제 및 재생성 작업이 자주 일어나므로 이름을 부여해 주어야 관리하기 편합니다.

실습

UNIQUE 제약조건의 이름을 주고 테이블을 생성 합니다.

T2 테이블을 삭제 후 다시 생성하는데, UNIQUE 제약조건의 이름을 기술하여 생성하세요.

CREATE TABLE T2 (     ID         NUMBER CONSTRAINT PK_T2 PRIMARY KEY,     NAME  VARCHAR2(100) NOT NULL,     TEL      VARCHAR2(20) CONSTRAINTS UK_T2_TEL UNIQUE ); SELECT INDEX_NAME, TABLE_NAME  FROM    USER_INDEXES WHERE TABLE_NAME = 'T2';

<실행결과>

	INDEX_NAME	TABLE_NAME
1	PK_T2	T2
2	UK_T2_TEL	T2

 

#고유키, #오라클고유키, #UniqueKey, #ORACLE, #ORACLE강좌, #ORACLE교육

오라클 테이블, NOT NULL 제약조건

 

오라클 테이블, NOT NULL 제약조건

 

컬럼의 값이 NULL일 수 없다는 제약조건 입니다(반드시 NULL이 아닌 값을 가져야 합니다).

컬럼 레벨로만 정의가 가능하며 NOT NULL도 기본값으로 지정할 수 있습니다. 테이블에 값을 INSERT할 경우 DEFAULT로 정의한 경우는 제외하고는 기본값이 NOT NULL인 컬럼은 반드시 컬럼을 기술하고 값도 지정해야 합니다.

실습

테이블을 생성할 때 NOT NULL, UNIQUE 제약조건을 기술하여 생성해 보고 이 두 제약조건이 기본키 제약조건과 같다는 것을 확인해 보는 실습 입니다.

id 컬럼에 NOT NULL + UNIQUE 제약조건을 기술하면서 테이블을 생성하고 딕셔너리 뷰를 통해 생성된 제약조건을 확인 합니다. 이 두 제약조건은 기본키로 동일한 역할을 하지만 제약조건을 조회하면 두개의 제약조건으로 조회 됩니다.

id number, namevarchar2(20) 2개의 컬럼을 가지는 T2 테이블을 생성하면서 기본 키를 지정하지 않습니다. 그대신 id 컬럼에 UNIQUE, NOT NULL 제약조건을 걸어줍시다.

CREATE TABLE T2 (     ID NUMBER UNIQUE NOT NULL ,     NAME VARCHAR2(20) ); SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME  FROM    USER_CONSTRAINTS WHERE TABLE_NAME = 'T2';

<실행결과>

	CONSTRAINT_NAME	CONSTRAINT_TYPE	TABLE_NAME
1	SYS_C007498	C	T2
2	SYS_C007499	U	T2

UNIQUE 제약조건의 타입은 U입니다. NOT NULL 제약조건 타입은 C가 됩니다. 

10.5.2 첫번째 실습에서 id 컬럼에 기본 키 제약조건을 기술한 뒤 USER_CONSTRAINTS 뷰를 통해 제약조건을 확인했을 때와 비교해보세요. 기본 키로 기술하면 제약조건의 타입이 P인 제약조건 하나만 조회됩니다.

NOT NULL + UNIQUE 제약조건이 PRIMARY KEY 제약조건과 동일한지 확인하세요.

CREATE TABLE t2 (     ID NUMBER UNIQUE NOT NULL ,     NAME  VARCHAR2(20),     CONSTRAINT PK_T2 PRIMARY KEY (ID) );

<실행결과>

ORA-02261: 고유 키 또는 기본 키가 이미 존재하고 있습니다.

id에 NOT NULL + UNIQUE 제약조건을 걸고 나서 테이블 레벨 제약조건으로 id를 기본 키로 지정했더니 중복 정의 에러가 발생했습니다..

 

#NOTNULL, #제약조건,​ #오라클, #테이블, #ORACLE, #오라클교육