오라클 테이블 기본 키 제약조건(주키, Primary Key, PK)

 

오라클 테이블 기본 키 제약조건(주키, Primary Key, PK)

 

테이블에서 레코드를 고유하게 구별하는 키로서, 프라이머리 키primary key 또는 기본 키, PKprimary key라고 부릅니다. 테이블당 하나만 가질 수 있으며 기본 키로 지정된 컬럼은 자동으로 NOT NULL과 고유 키uniaue key 제약조건을 가집니다. 

오라클에서는 PK가 정의되면 별도의 저장공간에 PK 이름으로 PK 컬럼에 대해 Unique Key Index를 생성합니다.  PK 이름은 별도로 지정할 수도 있고 지정하지 않을 경우 오라클에서 내부적으로 SYS_CXXXX 형태로 자동 부여합니다.

테이블의 생성하면서 기본키 제약조건을 컬럼레벨, 테이블 레벨로 지정하고 생성된 기본키 제약조건을 딕셔너리 뷰를 통해 확인하는 실습을 해보겠습니다.

실습

컬럼 레벨로 기본키를 지정하고 이를 딕셔너리 뷰를 통해 확인 합니다.

id number, name varchar2(100) 두개의 컬럼을 가지는 T2 테이블을 생성하면서 id 컬럼을 기본 키(Primary Key)로 컬럼 레벨 제약조건으로 정의합니다. 생성한 기본 키 제약조건을 딕셔너리뷰를 통해 확인합시다.

CREATE TABLE t2 (     ID        NUMBER CONSTRAINT pk_t2 PRIMARY KEY,     NAME VARCHAR2(100) ); SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME  FROM    USER_CONSTRAINTS WHERE TABLE_NAME = 'T2';

<실행결과>

	CONSTRAINT_NAME	CONSTRAINT_TYPE	TABLE_NAME
1	PK_T2	P	T2

제약조건의 타입에 대한 정의는 다음과 같습니다.

• C : CHECK 제약조건

• P : Primary Key

• U : Unique Key

• R : Foreign Key(외래 키, 참조무결성)

실습

기본키를 테이블 레벨로 지정해 보겠습니다.

T2 테이블을 생성하면서 id 컬럼의 기본 키를 테이블 레벨 제약조건으로 정의합니다.

CREATE TABLE t2 (     ID        NUMBER ,     NAME VARCHAR2(20),     CONSTRAINT pk_t2 PRIMARY KEY (ID) );

<실행결과>

Table T2이(가) 생성되었습니다.

실습

2개 이상의 컬럼에 대해 기본키를 지정하기 위해서는 테이블 레벨로만 정의해야 합니다.

name, tel 컬럼으로 T2 테이블을 생성하면서 name, tel 두 컬럼을 복합 키 형태의 기본 키로 지정하세요. 참고로 테이블 레벨에서 정의할 수 있습니다.

CREATE TABLE t2 (     NAME  VARCHAR2(100) PRIMARY KEY ,     TEL      VARCHAR2(20) PRIMARY KEY  );

<실행결과>

ORA-02260: 테이블에는 하나의 기본 키만 가질 수 있습니다.

CREATE TABLE T2 (     NAME  VARCHAR2(100) ,     TEL      VARCHAR2(20) ,     CONSTRAINT PK_T2 PRIMARY KEY (NAME, TEL) );

<실행결과>

Table T2이(가) 생성되었습니다.

 

#기본키, #테이블기본키, #PrimaryKey​, #무결성제약조건, #오라클, #오라클강좌

 

오라클 테이블, 데이터 무결성 제약조건

 

오라클 테이블, 데이터 무결성 제약조건

무결성 제약조건integrity constraints, IC은 테이블에 신뢰할 수 없는 데이터를 입력하지 못하게 합니다. 테이블 내의 컬럼값에 적용되는데 데이터값의 무결함, 정확성, 일관성을 보장하는 방법으로 테이블의 컬럼에 무결성 제약조건에 맞지 않는 데이터가 입력되면 DMLdata manipulation language INSERT, UPDATE, DELETE, MERGE등은 롤백됩니다.

예를 들어 사원 테이블에 성별 컬럼이 있다면 남성(M) or 여성(F)만 입력되어야겠죠? 다른 값이 들어오지 못하게 CHECK 제약조건을 컬럼에 걸면 됩니다.

또 사원 테이블에 같은 사원번호를 가지는 데이터가 입력되면 안 됩니다. 이때는 사원번호 컬럼을 기본 키로 지정하면 NOT NULL 제약조건과  UNIQUE 제약조건이 걸려 NULL 아닌 값이 반드시 입력이 되어야 하고 중복되지 않게 됩니다.

테이블 생성 시 또는 생성 후 무결성 제약조건을 추가할 수 있으며, 테이블의 모든 무결성 제약조건은 데이터 딕셔너리에 저장됩니다. 또한 제약조건을 지정할 때 이름을 부여하지 않으면 SYS_Cn 형태로 이름이 부여됩니다. 

아래는 무결성 제약조건의 종류입니다.

무결성 제약조건	역할
NOT NULL	컬럼값은 NULL을 가질 수 없습니다.
PRIMARY KEY(기본 키) 개체 무결성	테이블의 행을 유일하게 구별하는 키.  고유 키(Unique Key) 제약조건, NOT NULL 제약조건을 결합한 형태 입니다.
FOREIGN KEY(외래 키) 참조 무결성	같거나 다른 테이블의 PK/UK값을 참조하는 제약조건(예를 들어 EMP 테이블에서 DEPTNO 컬럼에 값이 입력되려면 반드시 DEPT 테이블의 DEPTNO 컬럼값이 입력되어야 합니다).
UNIQUE	컬럼값은 중복되지 않아야 합니다.
CHECK	컬럼에 입력되는 값을 지정하거나 범위를 지정합니다(예를 들어 성별 컬럼에는 M or F만 입력이 가능하도록 지정합니다)

10.5.1 무결성 제약조건 정의

무결성 제약조건은 제약조건을 기술하는 위치에 따라 컬럼 레벨과 테이블 레벨 제약조건이 있습니다. 각 제약조건을 정의하는 방법을 알아보겠습니다.

컬럼 레벨 제약조건column level constraints은 컬럼의 정의가 끝나기 전에 제약조건을 정의하며 모든 무결성 제약조건을 지정할 수 있습니다. 단일 컬럼에 대해 정의되며, NOT NULL 제약조건은 컬럼 레벨에서만 정의할 수 있습니다.

[기본 형식]

column_name [CONSTRAINT constraint_name] constraint_type

테이블 레벨 제약조건table level constraints은 컬럼을 모두 정의하고 나서 맨 나중에 생성된 컬럼에 대해 제약조건을 정의할 수 있습니다. 콤마(,)를 찍어 하나 이상 컬럼에 대해 제약조건을 걸 수 있습니다.  만약 기본 키 컬럼이 여러 개이면 테이블 레벨에서 한 번에 정의해야 합니다.

[기본 형식]

column,,, [CONSTRAINT constraint_name]constraint_type(column_name,,,)

 

#무결성제약조건, #오라클제약조건, ​#오라클, #기본키, #외래키

오라클 테이블 이름 변경 및 삭제

 

10.4. 오라클 테이블 이름 변경 및 삭제

RENAME 명령으로 생성된 테이블 이름을 변경하고 DROP TABLE 명령으로 테이블을 삭제할 수 있습니다.

[기본 형식]

RENAME old_table_name TO new_table_name DROP TABLE [schema.]table_name [CASCADE CONSTRAINTS]

테이블 이름 변경 및 삭제 원칙은 다음과 같습니다.

1. 삭제할 테이블은 사용자 계정에서 생성한 테이블이어야 합니다. 다른 스키마 계정의 테이블이면 DROP ANY TABLE 시스템 권한이 있어야 삭제할 수 있습니다.

2. 데이터 딕셔너리로부터 테이블 정의가 삭제되며 테이블 로우(ROW) 및 연관되어 생성된 인덱스, 트리거는 함께 삭제되지만 연관된 스키마 오브젝트 중 뷰 및 함수 및 프로시저는 삭제되지 않고 ‘INVALID’ 상태가 됩니다. 

3. 삭제되는 테이블의 기본 키primary key 또는 고유 키unique key를 자식 테이블에서 참조하고 있다면 삭제에 실패하게 됩니다. 그럴 때는 자식 테이블을 먼저 삭제하거나 DROP TABLE 명령어 마지막에 CASCADE CONSTRAINTS 조건을 주면 됩니다. CASCADE CONSTRAINTS 옵션은 삭제하는 테이블의 기본 키나 고유 키를 참조하는 참조 무결성 제약조건을 동시에 삭제하는 경우에 사용됩니다.

4. 삭제되는 테이블에 할당된 확장 영역은 테이블스페이스에 반환되며 다른 오브젝트 생성 시 사용할 수 있게 되고 삭제된 테이블은 플래시백 옵션으로 복구할 수 있습니다. 

이전에 실습한 CREATE TABLE ~ AS 구문으로 존재하는 테이블을 기본으로 새로운 테이블을 생성 후 이름을 변경하고, 테이블을 삭제하는 실습 그리고 부모, 자식 관계를 가지는 테이블에서 부모 또는 자식 테이블을 삭제하는 실습을 해보겠습니다.

실습

CREATE TABLE ~ AS 구문으로 존재하는 테이블을 기본으로 새로운 테이블을 생성하고 이름을 변경 후 삭제해 보겠습니다.

EMP 테이블에서 전체 테이블 구조만 복사하여 EMP10 테이블을 생성 후 이름을 EMP109로 변경하세요.(EMP10 테이블이 이미 존재한다면 삭제 후 실습하세요)

CREATE TABLE EMP10 AS SELECT * FROM EMP WHERE 1 = 2;

RENAME EMP10 TO EMP109;

<실행결과>

테이블 이름이 변경되었습니다.

EMP109 테이블을 삭제하세요.

DROP TABLE EMP109;

<실행결과>

Table EMP109이(가) 삭제되었습니다.

실습

업무상 필요에 따라 사용자 계정의 전체 테이블을 삭제하는 스크립트를 생성하는 경우도 가끔 있습니다. 아래 실습에서 삭제 스크립트 생성하는 방법에 대해 살펴 보겠습니다.

현재 접속한 사용자의 테이블을 모두 삭제하는 DROP TABLE 스크립트를 생성하세요.

SELECT 'DROP TABLE  ' || TABLE_NAME || ';' AS "DROP SCRIPT"  

FROM USER_TABLES;

<실행결과>

	DROP SCRIPT
1	DROP TABLE  BONUS;
2	DROP TABLE CUSTOMER;
3	...

실습

이번에는 부모, 자식 관계를 가지는 테이블을 생성하여 DROP TABLE을 실습합니다.

deptno, dname 컬럼을 가지는 DEPT_TEST라는 테이블을 생성합니다. deptno 컬럼이 기본 키입니다. DEPT_TEST 테이블이 이미 존재한다면 삭제 후 실습하세요.

CREATE TABLE DEPT_TEST (

   DEPTNO NUMBER(2) CONSTRAINT PK_DEPT_TEST PRIMARY KEY,

   DNAME VARCHAR2(14)

) ;

empno, ename, deptno 컬럼을 가지는 EMP_TEST 테이블을 생성하세요. empno는 Primary Key 이고 deptno 컬럼은 외래 키foreign key로 DEPT_TEST 테이블의 deptno 컬럼을 참조합니다.

CREATE TABLE EMP_TEST (

   EMPNO NUMBER(4) CONSTRAINT PK_EMP_TEST PRIMARY KEY,

   ENAME VARCHAR2(100),

   DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO_TEST REFERENCES DEPT_TEST

);

EMP_TEST 테이블의 deptno 컬럼에 KEY를 내려주는 DEPT_TEST를 부모이고 받는 EMP_TEST가 자식입니다.

외래 키를 지정하는 이유는 EMP_TEST 테이블의 deptno 컬럼은 값이 없는 것은 받아주지만(NULL 허용 컬럼), 값이 들어온다면 DEPT_TEST 테이블의 DEPTNO값만 허용하려는 의도에서 입니다. 이것이 ‘참조하는 데이터는 무결해야 한다’는 데이터 무결성의 한 종류인 참조 무결성입니다. 

참고로 개체 무결성이라는 것은 개체, 테이블이 무결해야 한다는 의미입니다. 예를 들어 Primary Key 컬럼은 중복이 없어합니다, 사번이 같은 직원이 있으면 안되니 까요. 오라클에서 Primary Key를 지정하면 해당 컬럼에 대해 별도의 영역에 인덱스를 생성하고 PK  컬럼에 NOT NULL, UNIQUE 제약조건을 걸어줍니다. 이렇게 하면 새 행을 추가할 때 PK 컬럼에 값이 꼭 들어와야 하며 중복되어서도 안 됩니다(개체 무결성).

부모 테이블인 DEPT_TEST 삭제를 시도해봅시다.

DROP TABLE DEPT_TEST;

<실행결과>

ORA-02449: 외래 키에 의해 참조되는 고유/기본 키가 테이블에 있습니다.

외래 키로 지정된 자식 테이블이 있기 때문에 부모를 먼저 지울 수는 없어 에러가 발생했습니다. 자식 DEMP_TEST 테이블을 먼저 삭제 후 DEPT_TEST 테이블을 삭제하는 것이 정상적인 방법입니다. 아래에서 제약조건을 비활성해서 삭제해봅니다. 

CASCASE CONSTRAINTS 옵션을 이용해 부득이 자식이 있는 DEPT_TEST를 삭제합시다.

DROP TABLE DEPT_TEST CASCADE CONSTRAINTS;

<실행결과>

Table DEPT_TEST이(가) 삭제되었습니다.

외래 키 제약조건을 삭제 후 테이블을 삭제했습니다. DEPT_TEST 테이블을 삭제 후 EMP_TEST 테이블의 테이블 생성 스크립트를 확인하면 다음과 같습니다(deptno 컬럼에 기술한 외래 키 제약조건이 사라졌습니다).

​

 

#테이블이름변경, #테이블삭제, #오라클테이블, #오라클, #ORACLE