Or11ConStraintRule

/********

# 파일명 : Or11ConStraintRule.sql

# 제약조건

# 설명 : 테이블 생성시 필요한 여러가지 제약조건에 대해 학습

********/

/*

[primary key : 기본키]

--참조무결성을 유지하기 위한 제약조건이다.

--하나의 테이블에는 하나의 기본키만 생성할 수 있다.

--기본키로 설정되면 그 컬럼은 값이 중복되거나 NULL값을 허용하지 않는다.

--즉 NOT NULL + UNIQUE의 의미를 가진다.

[UNIQUE : 유니크키]

- 값의 중복을 허용하지 않는 제약조건이다.

- 숫자, 문자 등의 데이터는 중복되지 않지만, NULL값에 대해서는 중복을

허용한다. 즉, 빈값(NULL)은 허용된다.

[Foreign key : 외래키]

--외래키는 참조무결성을 유지하기 위한 제약조건이다.

--만약 2개의 테이블이 외래키로 설정되어 있다면 부모테이블의

레코드는 삭제되지 않는다.

[NOT NULL]

--NULL 값을 허용하지 않는 컬럼을 의미한다.

--NOT NULL로 지정된 칼럼에 빈값을 입력하면 오류가 발생한다.

--주민번호, 회원ID와 같이 반드시 입력해야 하는 항목에 설정한다.

[Default]

--데이터 입력시 해당 컬럼에 아무값도 입력하지 않았을 때 자동으로 입력되는

값을 말한다. (제약명을 지정할 수 없다)

[check]

--Domain(자료형) 무결성을 유지하기 위한 제약조건으로 해당 컬럼에 잘못된

데이터가 입력되지 않도록 유지하기 위한 제약조건이다.

*제약조건을 확인하는 방법

select * from user_cons_columns

select table_name, constraint_name from user_constraints;

*/

--primary key(기본키)

/*

칼럼1을 기본키로 설정해야 한다면...

사용법1: 인라인방식

    create table 테이블명(

        컬럼1 자료형 [constraint PK제약명] primary key

    );

사용법2: 아웃라인방식

    create table 테이블명(

    컬럼1 자료형,

    [constraint PK제약명] primary key (컬럼1)

    );

사용법3: 테이블 생성 후 별도로 기본키 지정방법

    alter table 테이블명 add [constraint PK제약명]

    primary key (컬럼1);

    

[]부분은 생략이 가능함. 생략할 경우 오라클이 임의로 이름을 지정함.

*/

--사용법1을 이용한 PX생성

create table tb_primary1(

    student_id number(10) primary key,

    name varchar2(30)

);

--사용법2를 이용한 PK생성

create table tb_primary2 (

    student_id number(10) ,

    name varchar2(30),

    primary key (student_id)

);

--사용법3을 이용한 PK생성 : 테이블 생성후 alter명령어 이용

create table tb_primary3

(

    student_id number(10),

    name varchar2(50)

);

desc tb_primary3;

alter table tb_primary3 add constraint student_pk

primary key (student_id);

/*  제약조건 확인하기 */

select * from user_cons_columns;

select * from user_constraints;

/*

user_cons_columns : 테이블에 걸려있는 제약조건명과 컬럼명을 간단히

확인할 수 있다.

user_constraints : 테이블에 걸려있는 제약조건의 상세한 내용을

확인할 수 있다.

*user_constraints테이블의 constraint_type 플래그 설명

P : Primary key (기본키)

R : (Reference integrity) : Foreign key(외래키 or 참조키)

U : Unique(유니크)

C : Check, Not NULL

*/

insert into tb_primary1 (student_id, name) values (1, '홍길동');

insert into tb_primary1 values (1, '홍길동');/*

    PK가 걸려있는 student_id에 중복된 값을 입력했기 때문.

*/

insert into tb_primary1 values (2, '홍길동');

select * from tb_primary;

insert into tb_primary1 values ('', '김길동');/*

    ORA-01400: NULL을 ("HR"."TB_PRIMARY1"."STUDENT_ID")

    안에 삽입할 수 없습니다

    PK가 걸려있는 student_id에 빈값(NULL)을 입력했기 때문.

*/

update tb_primary1 set student_id='' where name='홍길동';

-- Unique(유니크)

/*

사용법 1:

    create table 테이블명 (

        컬럼명 자료형 unique

    );

    

사용법 2:

    create table 테이블명 (

        컬럼1 자료형,

        컬럼2 자료형,

        [constraint FK제약명] unique(컬럼1, 컬럼2)  

    );

    * unique는 2개이상의 컬럼에 적용할 수 있다.

*/

create table tb_unique1 (

    member_id number(10) unique not null,

    name varchar2(30),

    telephone varchar2(20),

    nickname varchar2(50),

    unique(telephone, nickname)

);

insert into tb_unique1 (member_id, name, telephone, nickname)

values (1, '홍길동', '010-1111-1111', '쾌도길동이'); --입력됨

insert into tb_unique1 values(2, '홍길동', '010-2222-2222', ''); --입력됨

insert into tb_unique1 values(3, '최길동', '', ''); --입력됨

insert into tb_unique1 values(3, '박길동', '010-3333-3333', '박씨길동이');/*

    member_id가 3인 레코드가 존재하므로 무결성제약조건 위반, 입력안됨

*/

insert into tb_unique1 values (4, '박길동', '010-3333-3333', '박씨길동이'); --입력됨

select * from tb_unique1;

update tb_unique1 set nickname='길동이최고' where name='홍길동';

update tb_unique1 set nickname='길동이최고'; --정상실행

update tb_unique1 set member_id = 1; --오류발생

/*

    nickname과 telephone컬럼이 동일한 제약조건으로 설정 되었으므로

    두개의 컬럼이 동시에 동일한 값을 입력받는 경우가 아니라면 중복값이

    허용된다.

*/

update tb_unique1 set telephone= '010-9999-9999' , nickname='길동이최고'; --오류!

/*

tb_unique1 테이블을 생성할때 telephone, nickname은 동시에 unique를 생성했기

때문에 하나의 그룹처럼 생성된다.

*/

update tb_unique1 set telephone = '010-9999-9999', nickname ='업뎃오류'

where name = '홍길동'; --오류발생 : name이 홍길동인 레코드가 2개이므로....

/*

foreign key (외래키)

사용법 : 인라인방식

    create table 테이블명

    (

        컬럼명1 자료형 primary key,

        컬럼명2 자료형 [constraint 제약조건명] references 부모테이블

            (부모테이블의 PK컬럼명)

    )

    

사용법2 : 아웃라인방식

    create table 테이블명

    (

      컬럼명1 자료형 primary key,

      컬럼명2 자료형 ,

      [constraint 제약조건명] foreign key (컬럼명2)

        references 부모테이블 (부모테이블이 PK컬럼)

    );

- 외래키 생성시 부모의 컬럼과 자료형은 반드시 일치해야 한다.

- 컬럼명은 달라도 상관없다.

- 외래키 생성시 on delete cascade 혹은 on delete set null 조건을 추가할 수 있다.

예] 컬럼명2 자료형2 references 부모테이블 (부모의 PK컬럼) on delete cascade

-> 부모 레코드 삭제시 지식레코드까지 같이 삭제됨

혹은

constraint 제약조건명 foreign key (컬렴명) references 부모테이블(부모의 PK컬럼)

-> 부모레코드 삭제시 지식레코드값이 null로 설정됨

*/

--외래키 설정하기

create table tb_foreign_key1

(

    f_id number(10) primary key,

    f_name varchar2(20),

    student_id number(10) 

        constraint st_fk1 references tb_primary1(student_id)

);

update tb_primary1 set name = '이순신' where student_id = 1;

select * from tb_primary1;

insert into tb_foreign_key1 (f_id, f_name, student_id)

values (100, '이순신입니다', 1);  -- 성공

insert into tb_foreign_key1 values(101, '김유신입니다.', 3);

/*

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

부모키가 현재 1, 2번만 존재함. 3번은 없음

*/

insert into tb_foreign_key1

values (101, '홍길동니다', 2);  -- 성공

insert into tb_foreign_key1

values (102, '홍길동일까요?', 2);  -- 성공

delete from tb_foreign_key1 where f_id=100; --정상삭제됨

delete from tb_primary1 where student_id=1; /* 삭제되지않음

    무결성제약조건 위배됨 */

/*

외래키가 걸려있는 2개의 테이블 사이에서 자식 테이블의 레코드를

삭제하려면 반드시 자식테이블의 레코드를 먼저 삭제 후에

부모테이블의 레코드를 삭제해야 한다.

*/

delete from tb_foreign_key1 where f_id=100; 

delete from tb_primary1 where student_id=1;

--외래키설정하기(아웃라인방식)

create table tb_foreign_key2(

    f_id number(10) primary key,

    f_name varchar(50),

    student_id number(10), 

    constraint dept_fkey2 foreign key (student_id)

        references departments (department_id)

);

--제약조건확인

select * from user_cons_columns

where table_name = upper('tb_foreign_key2'); /*

    레코드가 저장될 때 대문자로 저장되므로 upper() 함수를 이용하여

    문자열을 대문자로 변경후 select해야 한다.

*/

--외래키 설정하기3(테이블 생성후 alter명령 이용하기)

create table tb_foreign_key3

(

    f_id number(10) primary key,

    f_name varchar(40) not null, 

    student_id number(10)

);

alter table tb_foreign_key3 add

constraint dept_fkey3   -- 제약조건명

foreign key(student_id) -- 외래키를 지정할 컬럼

references departments (department_id); /*

외래키로 지정된 컬럼이 참조할 테이블의 컬럼

*/

--데이터삽입하기

insert into tb_foreign_key3 (f_id, f_name, student_id)

values(1, '홍길동이',10); --정상입력

insert into tb_foreign_key3 (f_id, f_name, student_id)

values(2, '김길똥이',300); --제약조건위반 : 부서본호 300번은 존재하지 않음

select * from tb_foreign_key3;

-- not null : 공백을 허용하지 않겠다는 제약조건

create table tb_not_null

(

    mem_idx number(10) primary key, -- PK 이므로 NN

    mem_id varchar2(30) not null, -- not null 지정으로 NN

    mem_pw varchar2(50) null, -- nulll허용(디폴트임)

    mem_name varchar2(40) -- null을 허용하는 경우 비워두는 것이 일반적

);

desc tb_not_null;

insert into tb_not_null values(1, 'hong1', '1234', '홍길동');

insert into tb_not_null (mem_idx, mem_pw, mem_name)

values (2, '9999', '김유신'); --입력오류 : mem_id에 null값 입력

insert into tb_not_null values(NULL, '', '', ''); --입력오류

insert into tb_not_null (mem_idx, mem_id) values (2, 'Kang1'); --입력성공

/* default : 값을 입력하지 않았을 때 기본으로 입력되는 값 */

create table tb_default

(

    name varchar2(30) not null,

    pw varchar2(50) default 'qwer'

);

insert into tb_default values ('김길동', '1234'); -- 1234로 입력됨

insert into tb_default values ('최길동', ''); -- null값이 입력됨

insert into tb_default (name) values ('박길동'); --qwer이 입력됨

insert into tb_default values ('성길동' , default); --qwer이 입력됨

select * from tb_default;

/* check : 정해진 값만 입력해야 할 때 사용함 */

create table tb_check

(

    gender varchar2(1) not null

        constraint check_gender1

        check (gender in ('M', 'F'))

);

insert into tb_check values ('M'); --입력성공

insert into tb_check values ('F'); --입력성공

insert into tb_check values ('Male'); --입력실패

insert into tb_check values ('여자'); --입력실패

select * from tb_check;

/*

-------17년 11월 08일-------

#제약조건

1. employees 테이블의 구조를 복사하여 pr_emp_sample 테이블을 만드시오.

복사된 테이블의 사원번호 칼럼에 “my_emp_pk” 라는 이름으로 “primary key” 제약조건을 지정하시오.

2. departments 테이블의 구조를 복사해서 pr_dept_sample 테이블을 만드시오.

부서번호에 “my_dept_pk” 라는 제약조건명으로 “primary_key”를 생성하시오.

3. pr_emp_sample 테이블의 부서번호 칼럼에 존재하지 않는 부서의 사원이

배정되지 않도록 외래키 제약조건을 지정하되 제약조건 이름은 “my_emp_fk”로 지정하시오.

4. pr_emp_sample 테이블의 커미션 칼럼에 0보다 큰 값만을 입력할수 있도록 제약조건을 지정하시오.

5. 위 3번에서는 두 테이블간에 외래키가 설정되어서 PR_DEPT_SAMPLE 테이블에서 레코드를 삭제할 수 없었다. 

이 경우 부모 레코드를 삭제할 경우 자식까지 같이 삭제될수 있도록 외래키를 지정하시오.

*/

/* 1 테이블 구조 복사 */

create table pr_emp_sample as

select * from employees where 1=0;

/* 제약조건 */

alter table pr_emp_sample add constraint my_emp_pk

primary key (employee_id);

/*

풀이

create table pr_emp_sample

as

select * from employees where 1=0; -- 테이블의 구조만 복사

alter table pt_emp_sample add constraint my_emp_pk

primary key(employee_id); -- my_emp_pk라는 이름의 기본기를 생성

--제약조건 확인하기

select * from user_cons_columns where constraint_name=upper('my_emp_pk');

select * from user_constraints where constraint_name=upper('my_emp_pk');

*/

select * from pr_emp_sample;

/* 2 테이블 구조 복사 */

create table pr_dept_sample as

select * from departments where 1=0;

/* 제약조건 */

alter table pr_dept_sample add constraint my_dept_pk

primary key (department_id);

/*

풀이

create table pr_dept_sample as select * from departments where 1=0;

alter table pr_dept_sample add constraint my_dept_pk

primary key (department_id);

select * from user_cons_columns where constraint_name=upper('my_dept_pk');

*/

/* 3 칼럼에 존재하지 않는 부서사원이 배정되지 않도록 외래키 설정*/

alter table pr_emp_sample add

constraint my_emp_fk   -- 제약조건명

foreign key(department_id) -- 외래키를 지정할 컬럼

references departments (department_id);

desc pr_emp_sample;

-- 풀이

--1,2에서 만들었던 기본키 삭제

alter table pr_emp_sample drop constraint my_emp_pk;

alter table pr_dept_sample drop constraint my_dept_pk;

-- 제약조건이 삭제되었는지 확인

select * from user_cons_columns where constraint_name=upper('my_emp_pk');

--외래키생성

alter table pr_emp_sample add constraint my_emp_fk

    foreign key (department_id)

    references pr_dept_sample (department_id);

ORA-02270: 이 열목록에 대해 일치하는 고유 또는 기본 키가 없습니다.

* 기본키를 삭제 후 외래키를 지정하면 위와같은 오류발생됨.

--위 1,2에서 기본키를 생성했던 쿼리문을 재실행

alter table pr_emp_sample add constraint my_emp_pk

primary key (employee_id);

alter table pr_dept_sample add constraint my_dept_pk

primary key (department_id);

--외래키 생성

alter table pr_emp_sample add constraint my_emp_fk

foreign key (department_id)

references pr_dept_sample (department_id);

-- 외래키 생성확인

select * 

from user_constraints

where table_name = upper('pr_emp_sample');

select * 

from user_constraints

where table_name = upper('pr_dept_sample');

select

    pk.owner, pk.constraint_name, pk.constraint_type,

    pk.table_name, pk.r_constraint_name,

    fk.owner, fk.constraint_name, fk.constraint_type, fk.table_name

from

    user_constraints pk , user_constraints fk

where

    pk.r_constraint_name = fk.constraint_name and

    fk.table_name = upper('pr_dept_sample')

;

-- pr_emp_sample 테이블에 레코드 삽입하기

insert into pr_emp_sample

    (employee_id, first_name, last_name, email, hire_date,

    job_id, department_id)

values

    (100, '호옹', '길동', 'gildong@kosmo.com',sysdate,

    '의적',10); --삽입 실패(부모테이블에 레코드 없기 때문)

--부모테이블인 pr_dept_sample에 departments 테이블의 레코드 복사하기

insert into pr_dept_sample select * from departments;

select * from pr_dept_sample;

--부모테이블에 레코드 삽입후 자식테이블에 레코드 삽입하기

insert into pr_emp_sample

    (employee_id, first_name, last_name, email, hire_date,

    job_id, department_id)

values

    (100, '호옹', '길동', 'gildong@kosmo.com',sysdate,

    '의적',10); --입력성공

    

--부모테이블에서 부서번호가 10인 레코드 삭제하기

delete from pr_dept_sample where department_id=10; /* 삭제안됨 -

    자식레코드가 존재하기 때문

*/

delete from pr_dept_sample where department_id=20; -- 삭제됨

/* 4 */

ALTER TABLE pr_emp_sample

ADD CONSTRAINT commission_pct

CHECK (commission_pct > 0);

/*

풀이

alter table pr_emp_sample add -- constraint 제약조건이름

    check (commission_pct > 0);

*/

alter table pr_emp_sample add -- constraint 제약조건이름

    check (commission_pct > 0);

insert into pr_emp_sample

    (employee_id, first_name, last_name, email, hire_date,

    job_id, department_id)

values

    (100, '호옹', '길동', 'gildong@kosmo.com',sysdate,

    '의적',10); --입력성공 -- --check 제약조건 위반, 0보다 큰값이 입력되어야 함.

insert into pr_emp_sample

    (employee_id, first_name, last_name, email, hire_date,

    job_id, department_id)

values

    (100, '호옹', '길동', 'gildong@kosmo.com',sysdate,

    '의적',0.9); 

/* 5 */

ALTER TABLE pr_emp_sample ADD CONSTRAINT my_emp_fk

FOREIGN KEY(department_id) REFERENCES pr_dept_sample (department_id) on delete cascade;

/*

풀이

*/

-- 제약조건삭제

alter table pr_emp_sample drop constraint my_emp_fk;

--외래키 재설정 : 부모레코드를 삭제할때 자식레코드도 같이 삭제될 수 있도록 설정

alter table pr_emp_sample add constraint my_emp_fk

foreign key (department_id)

references pr_dept_sample (department_id)

on delete cascade;

--부모레코드 삭제하기

select * from pr_emp_sample;

select * from pr_dept_sample;

delete from pr_dept_sample where department_id=10;/*

    on delete cascade 조건으로 인해 부모레코드를 지우면

    자식레코드까지 같이 삭제된다.

*/