본문 바로가기

IT 서비스 바라보기/Programming

테이블 및 열 제약 조건을 정의하기


- 기본키 (primary key) 를 정의하기
- 외부키와 참조 무결성
- 기본키를 드롭하기
- 외부키를 드롭하기


 connect id/passwd@host string 로 로그인 할 것.

 Super Key, Candidate Key, Primary Key, Foreign Key ?
- emp테이블에서 PK와 FK는?

기본 키를 정의하지 않았을 때

질의: 고객의 id, 고객의 이름, 잔액을 열로 가지는 customer1테이블을 만드시오.

SQL> create table customer1
2 (customer_id number not null,
3 cname varchar2(20),
4 balance number(10));

SQL> insert into customer1
2 values (101, 'abkim', 200000);

SQL> insert into customer1
2* values (101, 'abkim', 300000)

SQL> select * from customer1;

기본키를 정의했을 때

SQL> create table customer2
2 (customer_id number not null,
3 cname varchar2(20),
4 balance number(10),
5 constraint customer_pk primary key (customer_id));

SQL> insert into customer2
2 values (101, 'abkim', 200000);

SQL> insert into customer2
2* values (101, 'abkim', 300000)

ERROR at line 1:
ORA-00001: unique constraint (SCOTT.CUSTOMER_PK) violated

질의: 기본키를 정의하지 않았을 때와 기본키를 정의했을 때 어떻게 다른가?

테이블 작성 후에 기본키를 정의하기

SQL> alter table customer1
2* add constraint customer_pk primary key (customer_id)

SQL> create table loan
2 (customer_id number not null
3 constraint customer_id_fk references customer2 (customer_id),
4 cname varchar2(20),
5* amount number(10));

SQL> insert into loan
2 values (101, 'abkim', 400000);
- 입력되었는가?

SQL> insert into loan
2 values (103, 'cdlee', 300000);
- 입력되었는가? 되지 않았는가?
- 이유는 무엇인가? Loan테이블을 확인해 보고 이유를 말하시오.

SQL> delete from customer2
2 where customer_id = 101;

ERROR at line 1:
ORA-02292: integrity constraint (SCOTT.CUSTOMER_ID_FK) violated - child record found

- 위와 같은 에러가 나온 까닭은? (왜 삭제되지 않았는가?)
* loan과 customer2 테이블을 보기로 해서 참조 무결성을 설명하시오.

테이블 작성 후에 외부키를 선언하기

SQL> alter table 테이블명 add constraint 제약조건명 foreign key (열명)
2 references 참조되는테이블명;

제약 조건을 보는 질의

select table_name, column_name, constraint_name
from user_cons_columns
where table_name='CUSTOMER2'
// 테이블 이름을 대문자로

SQL> col table_name format a25
SQL> col column_name formant a25
로 형식을 정한 뒤에 다시 제약 조건 보기

질의:
1. emp_id, name, job, salary, dept_no를 애트리뷰트로 가지는 employee 테이블을 만드시오.
2. dept_no, dept_name, location을 애트리뷰트로 가지는 department 테이블을 만드시오.
3. employee테이블에서 emp_id를 PK로 선언하고, dept_no는 FK로 선언하시오.
4. department테이블이서 dept_no는 PK로 선언하시오.
5. 두 테이블에서 데이터를 입력하시오.
6. 모든 사원의 연봉은 반드시 입력되도록 employee테이블에 제약 조건을 넣으시오.

alter table employee
add constraint employee_salary_nn NOT NULL (salary)
(NOT NULL제약 조건은 add로 안됨, 아래와 같이)

alter table employee
modify salary constraint employee_salary_nn NOT NULL

한 튜플를 넣으시오.

찬호에 대해서는 salary를 널로 하고 싶은데…
alter table employee
disable constraint employee_salary_nn

찬호의 salary를 널로 입력해 보시오.

이제 다른 모든 사람에 대해서 salary를 not null로 돌리고 싶은데…

alter table employee
enable constraint employee_salary_nn


기본키를 드롭하기

SQL> alter table customer2 drop primary key;

ERROR at line 1:
ORA-02273: this unique/primary key is referenced by some foreign keys
- 왜 위와 같은 에러가 발생했는가?
- customer2테이블에서 pk를 지우는 방법은
. 먼저 child테이블의 외부키(fk)를 지운다.
. parent테이블의 기본키를 지운다.
SQL> alter table loan drop constraint customer_id_fk;
SQL> alter table customer2 drop primary key;

질의: employee와 department에 정의한 제약 조건을 검색해 보시오

- select table_name, column_name, constraint_name,
from user_cons_columns
where table_name in (‘EMPLOYEE’, ‘DEPARTMENT’)
//테이블 이름은 대문자로

질의: department에 들어있는 한 튜플을 삭제해 보시오.
질의: department에 PK제약 조건을 한꺼번에 삭제할 수는 없는가?

- alter talbe department
drop primary key cascade

질의: 다시 제약 조건을 검색해 보시오.
- select table_name, column_name, constraint_name,
from user_cons_columns
where table_name in (‘employee’, ‘department’)

유일한 값을 정의하기(unique key)

SQL> create table customer4
2 (customer_id number primary key,
3 cname varchar2(20),
4 jumin_no number(10) unique);

SQL> insert into customer4
2 values (101, 'cdkim', 123456);

SQL> insert into customer4
2* values (101, 'cdkim', 987654)
- 입력되었는가? 입력되지 않았는가? 이유는 무엇인가?
SQL> insert into customer4
2* values (102, 'cdkim', 123456)
- 입력되었는가? 입력되지 않았는가? 이유는 무엇인가?

질의-가: customer2의 모든 잔액이 기재되도록 balance 열에 NOT NULL제약 조건을
추가하시오. 단, 제약 조건의 이름은 customer2_balance_nn으로 하시오.

index 만들기

질의: customer1 테이블에서 cname으로 인덱스를 만드시오.
SQL> create index customer1_cname on customer1 (cname);

check 제약 조건으로 값을 제한

SQL> create table patient ( patient_id varchar2(6) primary key,
2 insurance_status char(1) constraint patient_insurance_status
3 check (insurance_status in ('Y', 'y', 'N', 'n')));

SQL> insert into patient
2 (patient_id, insurance_status)
3 values ('p1001', 'Y');

SQL> insert into patient
2 (patient_id, insurance_status)
3* values ('p1002', 'A')

ERROR at line 1:
ORA-02290: check constraint (SCOTT.PATIENT_INSURANCE_STATUS) violated
- 위와 같은 에러가 발생한 이유는 무엇인가?

같은 테이블 안의 다른 열을 참조하는 check 제약 조건

SQL> create table loan ( loan_no number(6) primary key,
2 amount_requested number (9) not null,
3 amount_approved number (9),
4 constraint amount_approved_limit
5 check (amount_requested >= amount_approved ));

SQL> insert into loan
2 values (1111, 2000, 3000);

ERROR at line 2:
ORA-02290: check constraint (SCOTT.AMOUNT_APPROVED_LIMIT) violated
- 에러가 발생한 이유는?

SQL> insert into loan
2 values (1111, 3000, 2000);
- 입력되었는가?

참고:
- 질의-가: alter table customer2 modify balance constraint customer2_balance_nn NOT NULL
- alter table customer2 drop constraint customer2_balance_nn