[SQL] 제약조건 (constraint) 2

[제약조건이 설정되지 않은 경우]

1) 제약조건이 설정되지 않은 테이블 생성하기

 create table c_emp(
  id        number(5)
 ,name    varchar2(25)
 ,salary    number(7,2)
 ,phone   varchar2(15)
 ,dept_id  number(7)
 );

 

2) id칼럼에 pk 제약조건 추가 

⊙ alter table 테이블명 add constraint 제약조건이름 제약조건종류(필드명) 

    → (필드명) : id 칼럼에 조건을 추가하는 것이기 때문에 (id)라고 넣어주면 됨

alter table c_emp add constraint c_emp_id_pk primary key(id);    -- id칼럼의 조건 변경
select * from user_constraint where table_name='C_EMP';          -- c_emp라고 소문자로 검색시, 결과가 안 뜸

 

⊙ id칼럼에 pk 제약조건 추가한 후 아래의 요소를 삽입

 insert into c_emp(id, name) values(1, '개나리'); 
 insert into c_emp(id, name) values(1, '진달래'); 
 insert into c_emp(id, name) values(1, '라일락'); 

 

⊙ '개나리'와 '진달래'의 id가 1로 겹치기 때문에 오류발생 (primary key가 들어가게 되면서 겹치는 id는 사용불가)

 -  오류 보고 - ORA-00001: unique constraint (JAVA0514.C_EMP_ID_PK) violated  ▶  'unique constraint 을 위반' 했다

 

2) salary 칼럼에 check 제약조건

create table c_emp( 
   id       number(5)    
   ,name    varchar2(25)
   ,salary  number(7,2)   constraint c_emp_salary_ch check (salary between 100 and 1000)
   ,phone   varchar2(15)
   ,dept_id number(7)
   );

 insert into c_emp(id, name, salary) values(1, 'kim', 100);
 insert into c_emp(id, name, salary) values(1, 'lee', 1000);
 insert into c_emp(id, name, salary) values(1, 'park', 10000); 

 

check (salary between 100 and 1000)	salary 를 입력할 때 100에서 1000 사이로만 입력하겠다는 것.
insert into c_emp(id, name, salary) values(1, 'park', 10000);
오류 보고 - ORA-02290: check constraint (JAVA0514.C_EMP_SALARY_CH) violated ▶ salary의 범위가 100~1000인데 넘음

 

4) name 칼럼에 unique 제약조건

create table c_emp(
id number(5)
,name varchar2(25) constraint c_emp_name_uni unique
,salary number(7,2)
,phone varchar2(15)
,dept_id number(7)
);

insert into c_emp(name) values(1, 'kim', 100);
insert into c_emp(name) values('park');
insert into c_emp(name) values('park');

 

c_emp(name) values(1, 'kim', 100);	오류 보고 -SQL 오류: ORA-00913: too many values 00913. 00000 -  "too many values"  ▶ 주어진 칼럼보다 더 많은 값이 주어짐 (name 만 원했는데 name 외의 것 존재)
두 번째 c_emp(name) values('park');	오류 보고 - ORA-00001: unique constraint (JAVA0514.C_EMP_NAME_UNI) violated  ▶ unique에 걸려서 겹치는 두번째 park 은 오류발생

 

5) dept_id칼럼에 foreign key 제약조건

create table c_emp(
id number(5)
,name varchar2(25)
,salary number(7,2)
,phone varchar2(15)
,dept_id number(7) constraint c_emp_dept_id_fk references dept(deptno)
);

insert into c_emp(name, dept_id) values('태평양',10);
insert into c_emp(name, dept_id) values('대서양',40); 
insert into c_emp(name, dept_id) values('카리브',90);


<참고 dept>
10, '경리팀', '부산'
20, '연구팀', '대전'
30, '총무팀', '대구'
40, '전산팀', '인천'

 

c_emp(name, dept_id) values('카리브',90);

오류 보고 -ORA-02291: integrity constraint (JAVA0514.C_EMP_DEPT_ID_FK) violated - parent key not found ▶ 부모클래스인 deptno가 10~40까지 밖에 없어서 90은 해당하지 않음

 

 6. 제약조건 최종 결과값

drop table c_emp;    -- 이미 저장되어 있을 수도 있기 때문에 수행
create table c_emp(
id number(5) primary key
,name varchar2(25) not null
,salary number(7,2) check(salary between 0 and 100)
,phone varcahr2(15) null
,dept_id number(7) references dept(deptno)
);

 

필드명	자료형	제약조건	설명
id	number(5)	primary key	최대 99999까지 가능한 겹치지 않는 id
name	varchar2(25)	not null	25자까지의 문자열을 넣되 null값이 들어갈 수 없음
salary	number(7,2)	check(salary between 0 and 100)	숫자 7자중 소수점 2자리 허용,  0~100까지의 수
phone	varchar2(15)	null	null값 허용
dept_id	number(7)	references dept(deptno)	부모인 dept의 deptno를 참조함

 

7. 제약조건 목록 확인 (데이터 사전) 

 ⊙ select * from user_constraints where 조건문;

 select * from user_constraints where table_name='C_EMP';