Oracle 기초 : 실전(12) DDL (Data Definition Language), Constraint (update: 2020/06/22)

codePark·2020년 6월 21일
oracle
0

Oracle

목록 보기
16/23

Introduction: DDL (Data Definition Language)


데이터 정의어. CRUD 중 CUD에 해당하는 명령어인 Create(생성), Alter(수정), Drop(삭제)를 통해 Database Object를 정의한다. 자동 commit을 지원하므로 TCL(Transaction Control Language)이 필요하지 않다. 또한 데이터의 생성(insert)/수정(update)/삭제(delete), (즉 DML의 영역)와 Database Object(개체)의 생성(create)/수정(alter)/삭제(drop), (즉 DDL의 영역)는 엄연히 다르므로 용어를 혼동하지 않도록 한다.

Database Object:

table, user, view, sequence, index, synonym, procedure, trigger, function 등을 말하는 것이고, 여기서 procedure, trigger, function등은 PLSQL(Procedural Language for SQL) Syntax에 속하는 것이다. (Oracle 단독)

CREATE: Create Table/Column Comments

Syntax of adding the comment to Tab/Col:
comment on table TabName is 'COMMENT ON HERE';
comment on column TabName.ColName is 'COMMENT ON HERE';

comment on table member is 'Employee-table';
comment on column member.emp_name is 'Employee name on here';

각 Table 또는 Column에 comment(Remark)를 작성한다. 이는 별도의 수정이 불가하고, 해당 Table 또는 Column에 comment를 새로 선언하게 되면 기존의 데이터를 덮어씌우게 된다. 따라서 Comment를 삭제하고자 한다면 ''에 '';로 값을 입력하지 않은 comment를 덮어씌워 (null)로 처리할 수 있다.

또한 작성한 주석은 Data Dictionary를 통해 다음과 같이 조회가 가능하다:
Syntax of the Looking up the comments:

select *
from user_tab_comments UTC 
join user_col_comments UCC
using (table_name)
where table_name = 'TABLE_NAME'

CREATE: Introduction of Constraints

Constraint(제약조건)이란 데이터의 Intergrity(무결성)을 지키기 위해, 즉 부정한 데이터가 존재하지 않도록 방지하기 위해 입력 데이터를 제한하기 위한 조건을 의미한다. 데이터의 무결성이란 각 데이터가 정확하고, 부정한 데이터가 존재하지 않는 것을 말한다. 예를 들어 변경된 데이터를 제대로 반영하지 않는다거나, 일치하지 않는 데이터가 존재하거나, 필수 정보가 누락되는 등의 경우를 부정한 데이터라 일컫는다. 이 때, Constraint가 에러를 발생시켜서 부정한 데이터의 입력을 막는 역할을 수행한다.

Constraint의 종류로는 총 5가지가 존재한다:

  1. not null : 데이터에 null을 허용하지 않는 것. 즉 필수 데이터를 설정한다.
  2. unique(UQ): 중복된 값을 비허용 한다.
  3. primary key(PK): 기본키, 주키, 식별키(Identifier)라고도 불린다. not null+unique의 기능, 즉 null 비허용 및 고유 데이터만 허용한다. Table당 단 하나의 Column에만 사용이 가능하다.
  4. foreign key(FK): 외래키. RDBMS의 핵심. 두 테이블간의 관계를 정의하는 제약 조건. 자식 테이블의 Column은 부모 테이블의 Column값만 가져다 쓸 수 있다. 이 때의 부모-자식 상속관계란 하나의 테이블이 또 다른 테이블을 참조하고 있음을 의미한다. 여기서 참조 테이블이 자식, 피참조 테이블이 부모이다.
  5. check(CK): 데이터의 범위, 조건등을 설정한다. (ex. gender)

마찬가지로 작성된 Constraint는 Data Dictionary의 Table을 통해 조회가 가능하다. 

select *
from user_constraints

--OR
select *
from user_cons_columns

단, user_constraint의 경우는 ColumnName을 조회할 수 없으므로 통상적으로 저 두 Table을 join으로 병합하여 사용한다. 아래와 같은 코드를 사용하면 조회가 간편하다:

select  UC.table_name, UCC.column_name, constraint_name, 
		UC.constraint_type, UC.search_condtition
from user_constrinats UC join user_cons_columns UCC
using(constraint_name)
where UCC.table = 'TABLE_NAME'

일반적으로 Constraint를 선언할 때 constraint_name을 지정하여 주는데, not null constraint의 경우 별도로 constraint_name을 지정하지 않는 것이 보편적이다.

CREATE: Create Constraint: not null

Syntax of the creating Constraint - not null:
create table TabName(
Col DataType(Size-add'l) not null);

create table employee(
emp_name varchar2(15) not null
);

Column Level, 즉 Column 단위로만 지정이 가능하다. not null Constraint 설정 후 해당 Column에 null값을 대입하려 하면 ORA-01400: cannot insert NULL into () 에러를 발생시켜 필수 데이터의 누락을 방지한다.

CREATE: Create Constraint: unique

Syntax of the creating Constraint - unique
--on Column Level
create table TabName(
Col DataType(Size-add'l) constraint ConstraintName unique);
--on Table Level
create table TabName(
constraints ConstraintName unique);

create table tbl_const_test(
emp_id char(10) constraint UQ_email unique);

Column 입력값에 대해서 중복을 허용하지 않는다. unique Constraint를 생성한 후 중복된 값을 대입하려 하면 ORA-00001: unique constraint (Object) violated, 즉 unique 전제조건 위반이라는 에러가 발생한다. 또한 DBMS마다 unique 제약조건 Column의 null 허용 여부가 다르다. Oracle 및 mysql은 null 삽입이 가능하고, mssql은 불가능하다.

CREATE: Create Constraint: primary key

Syntax of the creating Constraint - primary key (Unique Identifier):
create table TabName(
Col DataType(Size-add'l) primary key);
or
create table TabName(
Col DataType(Size-add'l),
constraints ConstraintName primary key(Col));

--COLUMN LEVEL
create table tbl_alter(
num number primary key
);

--TABLE LEVEL
create table shop_nickname(
member_id varchar2(20),
constraints FK_NMEMBER_ID primary key(memebr_id)
);

--CREATE COMPOSITE-CONSTRAINT 
create table tbl_composite_pk(
product_code varchar2(50),
user_id varchar2(50),
order_date date default sysdate,
num number,
cconstraints PK_ORDER primary key
(product_code, user_id, order_date));

테이블마다 단 하나씩만 존재할 수 있는 Unique Identifier(고유 식별자)로 상술했듯 한 Table당 한 Column에만 부여가 가능하다. 여러 Column을 묶어서 Primary Key 설정도 가능하나(Composite-Primary Key Constraint), 사용된 Column은 그 어떤 것도 null일 수 없다. 단, 여러 Column을 선언하는 만큼 Column Level에 작성할 수 없다. 이렇게 여러 Columns를 묶어 Composite-PK 설정한 경우 선언한 모든 Column의 값을 하나의 그룹으로 묶어 판별하기 때문에 실제 데이터가 전부 동일해야 중복되는 것으로 본다.

CREATE: Create Constraint: foreign key

Syntax of the Creating Constraint - foreign key:
create table TabChild(
member_id DataType(Size-add'l),
constraints ConstraintName foreign key(Col1)
references TabParent(Col2));

create table shop_buy(
member_id varchar2(20),
	constraints FK_MEMBER_ID foreign key(member_id)
	references shop_member(member_id));

Reference Intergrity를 위한 제약조건. 참조된 Parent Table의 Column Data만 사용할 수 있도록 제한하며, null을 허용한다. 참조하고 있는 부모 테이블의 Column은 primary key 또는 unique 제약 조건이 걸려있어야 한다. 또한 foreign key는 Child Table의 Column에 지정하는 것이다. 또한 foreign key를 통해서 참조한 자식 테이블에 새로운 데이터를 추가할 때, 상속받은 부모 테이블의 unique/primary key 조건에 해당하지 않는 데이터가 포함되어 있을 경우 Error report - ORA-00001: unique constraint() violated 에러가 발생되며, 해당 데이터는 추가되지 않는다.

또한 이렇게 피참조-참조의 부모-자식 관계로 엮인 테이블간에서 부모 테이블의 데이터를 삭제하는 시도를 하면 ORA-02292: integrity constraint () violated - child record found 에러가 발생한다. 따라서 피참조-참조 상속 관계에서 참조 테이블인 자식 테이블에서의 데이터 삭제는 문제를 발생시키지 않으나, 피참조 대상인 부모 테이블의 데이터를 삭제하게 되면 Data intergrity가 깨지게 되므로 에러를 발생시키는 것이다. 개발 과정에서 해당 에러를 생각보다 자주 만나게 되므로, Foreign Key는 신중하게, 개발 후순 단계에서 사용해 주는 것이 좋다.

상기한 문제에 관해 부모 테이블의 데이터를 삭제할 때 자식 데이터를 어떻게 처리할 지에 대한 옵션이 존재한다. 이는 총 3가지로, 테이블 및 컬럼 생성 시점에서 설정할 수 있다:

  1. on delete restricted (default)
    기본값으로 설정되어 있으며, 상속관계가 존재하는 부모 테이블의 데이터를 삭제할 수 없게 한다.
  2. on delete set null
    자식 행은 유지하되, 해당 데이터 섹션은 (null)로 표시되게 된다.
    즉, 데이터는 삭제되나 삭제여부를 확인할 수 있게 한다.
  3. on delete cascade
    상속 관계에서 부모 테이블의 데이터 삭제시 자식 테이블의 레코드도 함께 삭제된다.
    on delete set null과 다르게 해당 행 전체가 삭제된 것을 확인할 수 있다.

Identify Foreign Key Relationship

또한 부모-자식 테이블의 피참조-참조 관계를 식별/비식별 관계로 정의할 수도 있다:

  1. 1 : n Relationship: 비식별 관계. 자식 테이블이 부모 테이블로부터 Foreign Key로 가져온 값을 비식별자(다시 말 해 primary key/unique가 아닌 경우)로 사용하고 있는 경우를 일컫는다.
  2. 1 : 1 Relationship: 식별관계. 자식 테이블이 참조하고 있는 Foreign Key가 곧 부모 테이블의 Primary Key로, 이를 자식 테이블의 식별자(primary/unique)로 사용하고 있는 경우이다.

CREATE: Create Constraint: check

Syntax of Creating Constraint - check:
create table TabName(
Col DataType(Size-add'l),
constraints ConstraintName check(Col in (Val1, Val2 ...)));

create table tbl_const_test(
gender char(1),
constraints CK_GENDER check(gender in ('F', 'M')));

해당 Column이 가질 수 있는 값을 특정 범위 내로 한정하여 선언한다. check Constraint를 선언하여 테이블을 생성한 이후에 해당 Column에 값을 대입할 때는 반드시 check(Col in (Val1, Val2 ...))에 상응하는 값만이 대입이 가능하고, 이에 해당하지 않는 경우 ORA-02290: check constraint () violated 에러를 발생시킨다.

UPDATE: alter Statement - add

Syntax of adding Column to Table with alter:
alter table TabName add ColName DataType(SAize-add'l) Constraint;
Syntax of adding Constraint to Table with alter:
alter table TabName add constraint ConstraintName ConstraintType;**

--ADD NEW COLUMN TO TABLE
alter table tbl_alter add name;

--ADD NEW COLUMN WITH CONSTRAINT TO TABLE
alter table tbl_alter add constraint UQ_TBL_ALTER_ID unique(id);

Database Object(데이터베이스 개체)수정. Column/Constraint를 대상으로 추가가 가능하며, Column의 DataType, default Value, Constraint의 추가 및 rename, drop이 가능하다. alter를 통한 add시에 추가된 Column은 무조건 가장 마지막 순서의 위치로 생성되며, 이 순서는 변경할 수 없다. 또한 not null Constraint의 경우는 alter가 아닌 modify에서 진행된다.

UPDATE: alter Statement - modify

Syntax of the modifying nullability with alter:
alter table TabName modify ColName not null
Syntax of the modifying datatype with alter

--MODIFYING NULLABILITY WITH ALTER
alter table tbl_alter modify id not null;

--MODIFYING DATATYPE OF COLUMN WITH ALTER
alter table tbl_alter modify id varchar2(10);

not null Constraint와 Datatype은 alter-add가 아닌 alter-modify로 수정할 수 있다. 단 데이터타입/사이즈를 변경할 때 이미 존재하는 데이터와 상충하여 데이터 손실이 예상되는 경우는 에러를 발생시킨다. 또한 Constraint를 수정하고자 하는 경우에는 수정이 아닌 '덮어씌우기'형식을 사용하고 있으므로 modify가 아닌 add를 통해 수정해 주어야 한다.

UPDATE: alter Statement - rename

Syntax of renaming the Column with alter:
alter table TabName rename column ColName to NewColName;
Syntax of renaming the Constraint Name with alter:
alter table TabName rename constraint ConstName to NewConstName;

--RENAMING COLUMN
alter table tbl_alter rename column pwd to password;

--RENAMING CONSTRAINT
alter table tbl_alter constraint SYS_C007260 to PK_NO;

Column명과 Constrain의 설정된 이름을 변경한다.

DELETE: alter Statement - drop

Syntax of dropping Column with alter:
alter table TabName drop column ColName;
Syntax of dropping Constraint with alter:
alter table TabName drop constraint ConstName;

--DROPPING COLUMN
alter table tbl_alter drop column id;

--DROPPING CONSTRAINT
alter table tbl_alter drop constraint PK_NO;

Column과 Constraint를 삭제한다.

profile
codePark
아! 응애에요!
이전 포스트

Oracle 기초 : 실전(11) DML (Data Manipulation Language) (update: 2020/06/24)

다음 포스트

Oracle 기초 : 실전(13) DCL, Data Control Language

0개의 댓글