SQL 제약조건

create table constraint_emp

(eid char(3) constraint pkeid primary key,

 ename varchar2(20) constraint nename not null,

 eno char(14) constraint neno not null constraint ueno unique,

 email varchar2(25) constraint uemail unique,

 phone varchar2(12),

 hire_date date default sysdate,

 jid varchar2(10) constraint fkjid references jobs(job_id) on delete set null,

 salary number,

 bonus_pct number,

 marriage char(1) default 'n' constraint chk check (marriage in ('y','n')),

 mid number constraint fkmid references emp(empno) on delete set null,

 did number,

constraint fkdid foreign key(did) references dept(deptno) on delete cascade);

select * from user_constraints;

select * from user_cons_columns;

select a.table_name, a.constraint_name, a.constraint_type, 

       b.column_name 

from user_constraints a, user_cons_columns b

where a.owner = 'HR'

and a.table_name = 'EMPLOYEES'

and a.owner = b.owner

and a.constraint_name = b.constraint_name;

insert into constraint_emp (eid, ename, eno,email, hire_date, MARRIAGE)

values ('126', '김민철', '123456786','mckmckmc@kmckmckmc', '09/24/2014', 'y')

select * from constraint_emp

//phone의 길이가 5 이상 되게 제약조건

alter table constraint_emp

add constraint phone_chk

          CHECK ( length(phone) >= 5 )

//salary의 5000 이상 되게 제약조건

alter table constraint_emp

modify constraint salary_chk

          CHECK ( salary >= 5000 )

select * from user_constraints where table_name = 'constraint_emp'

alter table constraint_emp

drop constraint salary_chk

desc constraint_emp

select * from user_constraints;

select * from user_cons_columns;

 

========= 기본키, 외래키에 포함된 칼럼 나태내기 ============

select a.table_name, a.constraint_name, a.constraint_type, 

       b.column_name 

from user_constraints a, user_cons_columns b

where a.owner = 'HR'

and a.table_name = 'EMPLOYEES'

and a.owner = b.owner

and a.constraint_name = b.constraint_name;