[DB] 10. 제약조건

제약조건이란?

 

• 테이블의 컬럼에 원하지 않는 데이터가 입력 / 수정 / 삭제되는 것을 방지하기 위한 조건이다.
• 제약조건의 종속성이 존재하는 경우 테이블의 삭제를 방지한다.

-- 제약조건의 조회는
select * from user_constrains;

제약조건 유형

제약조건의 정의 기본틀

0️⃣ 열 레벨 제약조건과 테이블 레벨 제약 조건

1️⃣ create table - 열 레벨 제약 조건

열레벨 제약 조건

 

🔸 constraint로 이름을 준것.

-- constraint 직접 이름을 지정할 때 사용 dept_no_pk로 이름을 줬다.
-- check (소괄호 안의 조건)
create table dept2(
    dept_no number(2)           constraint dept2_no_pk primary key,
    dept_name varchar2(15)      constraint dept2_name_nn not null,
    loca number(4)              constraint dept2_loca_locaid_fk REFERENCES locations(location_id),
    dept_date date              default sysdate,
    dept_bonus number(10)       default 0,
    dept_phone varchar(20)      constraint dept2_phone_uk unique,
    dept_gender char(1)         constraint dept2_gender_ch check(dept_gender in ('M','F'))
);

 

🔸 constraint를 제거하고 이름없이 제약조건만 준것.

create table dept2(
    dept_no number(2)           primary key,
    dept_name varchar2(15)      not null,
    loca number(4)              REFERENCES locations(location_id),
    dept_date date              default sysdate,
    dept_bonus number(10)       default 0,
    dept_phone varchar(20)      unique,
    dept_gender char(1)        check(dept_gender in ('M','F'))
);

 

2️⃣ create table - 테이블 레벨 제약 조건

테이블 레벨 제약 조건의 틀

create table dept2(
    dept_no number(2),
    dept_name varchar2(15)  not null,
    loca number(4),
    dept_date date          default sysdate,
    dept_bonus number(10)   default 0,
    dept_phone varchar(20),
    dept_gender char(1),
    constraint dept2_no_pk primary key(dept_no/*dept_name*/), -- 슈퍼키
    constraint dept2_loca_locaid_fk foreign key(loca) REFERENCES locations(location_id),
    constraint dept2_phone_uk unique(dept_phone),
    constraint dept2_gender_ch check(dept_gender in ('M','F'))
);

 

🔸 2개의 컬럼을 묶어서 primary key로 만들기 (참고)

 

제약조건의 종류

 

1️⃣ UNIQUE

• 값들이 유일해야한다. 중복값 불가능
• NULL값은 가질 수 있다.

 

2️⃣ PRIMARY KEY

• NOT NULL + UNIQUE KEY 특징 = PRIMARY KEY
• 값들이 유일하고 중복이 불가능하고 NULL값도 가질 수 없다.

 

3️⃣ NOT NULL

• NULL값을 가질 수 없다.
• PK는 NOT NULL을 기재하지 않아도 DEFAULT로 NOT NULL이 들어간다.

 

4️⃣ FOREIGN KEY

• 테이블 간의 관계를 설정한다.
• 어떤 TABLE에 PK를 연동하기 위해서 담아놓은 컬럼을 FK라고 한다.
• 중복값과 NULL값은 사용 가능하다. 그러나 PK에 없는 값은 사용 불가능하다.

 

5️⃣ CHECK

 

 

 

제약조건을 위배하는 INSERT 시도

 

🔸 EMPLOYEES TABLE에 제약조건에 위배되는 DATA를 넣어보도록 하겠다.

1️⃣ 개체무결성 위배 (NULL, 중복값은 PK에 들어가지 못한다)

-- ORA-00001: unique constraint (HR.EMP_EMP_ID_PK) violated 
---> EMPLOYEE_ID가 이미 있기 때문에 INSERT 할 수 없다.
-- 개체무결성 위배 (NULL, 중복값은 PK에 들어가지 못한다)
INSERT INTO EMPLOYEES(EMPLOYEE_ID, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID)
VALUES(100, 'TEST', 'TEST', SYSDATE, 'TEST');

 

2️⃣ 참조무결성 위배 (참조하는 TABLE에 PK로 존재해야 FK에 들어갈 수 있다.)

-- ORA-02291: integrity constraint (HR.EMP_JOB_FK) violated - parent key not found 
---> DEPARTMENT_ID(PK)에 5번이 없어서 INSERT 할 수 없다.
-- 참조무결성 위배 (참조하는 TABLE에 PK로 존재해야 FK에 들어갈 수 있다.)
INSERT INTO EMPLOYEES(EMPLOYEE_ID, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID, DEPARTMENT_ID)
VALUES(501, 'TEST', 'TEST', SYSDATE, 'TEST', 5);

 

3️⃣ 도매인 무결성 위배 (컬럼에 정의된 값만 들어갈 수 있다)

-- ORA-02290: check constraint (HR.EMP_SALARY_MIN) violated
---> SALARY 0초과값만 들어갈 수 있으므로 음수값이 들어갈 수 없어서 INSERT 할 수 없다.
-- 도매인 무결성 위배 (컬럼에 정의된 값만 들어갈 수 있다)
INSERT INTO EMPLOYEES(EMPLOYEE_ID, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID, SALARY)
VALUES(501, 'TEST', 'TEST', SYSDATE, 'TEST', -10);

 

제약조건 관리

제약 조건은 추가 or 삭제는 가능하지만 수정은 불가능하다.

제약 조건의 활성화 or 비활성화도 가능하다.

modify절을 사용하여 not null을 추가 할 수 있다.

 

0️⃣ 테스트 할 dept2 table을 drop으로 삭제 후 create한다. (기존에 없다면 그냥 create)

DROP TABLE DEPT2;

create table dept2(
    dept_no number(2),
    dept_name varchar2(15),
    loca number(4),
    dept_date date          default sysdate,
    dept_bonus number(10)   default 0,
    dept_phone varchar(20),
    dept_gender char(1)
);

 

1️⃣ 제약 조건의 추가

제약 조건의 추가 (alter를 사용하여)

• 제약  조건들을 alter구문을 사용하여 아래와 같이 개별로 추가해 줄 수 있다.

--PK 제약조건 추가
ALTER TABLE DEPT2 add constraints dept_no_pk PRIMARY KEY(dept_no);

--FK 제약조건 추가
ALTER TABLE DEPT2 ADD CONSTRAINTS dept_loca_fk FOREIGN KEY(loca) REFERENCES locations(location_id);

--unique 추가
alter table dept2 add constraint dept2_phone_uk unique(dept_phone);

-- check 추가
alter table dept2 add constraint dept2_gender_ch check(dept_gender in ('M','F'));

🔸 modify 절을 사용하여 not null 조건 주기.

-- not null 추가 - 컬럼변경문으로
alter table dept2 modify dept_name varchar(15) not null;

 

2️⃣ 제약조건 삭제

제약 조건의 삭제

alter table dept2 drop constraint dept_loca_fk;

 

연습문제

 

--------------------------------------------------------------------------------연습문제
--문제 1.
--다음과 같은 테이블을 생성하고 데이터를 insert하세요 (커밋)
--조건) M_NAME 는 가변문자형, 널값을 허용하지 않음
--조건) M_NUM 은 숫자형, 이름(mem_memnum_pk) primary key
--조건) REG_DATE 는 날짜형, 널값을 허용하지 않음, 이름:(mem_regdate_uk) UNIQUE 키
--조건) GENDER 가변문자형
--조건) LOCA 숫자형, 이름:(mem_loca_loc_locid_fk) foreign key – 참조 locations테이블(location_id)

create table test1(
    m_num number(3) constraint mem_memnum_pk primary key);
drop table members;

create table members (
    m_name varchar2(10) not null,
    m_num number(3) constraint mem_memnum_pk primary key,
    reg_date date default sysdate not null constraint mem_regdate_uk unique,
    gender varchar2(1) check(gender in ('M','F')),
    loca number(4) constraint mem_loca_loc_locid_fk REFERENCES locations(location_id)
);

select * from locations; -- loca 참고용

insert into members values('홍길동', 20, sysdate, 'M', 1000);
insert into members values('김민지', 100, sysdate, 'F', 2100);
insert into members values('이지연', 50, sysdate, 'F', 2500);
insert into members values('AAA', 1, '2018-07-01', 'M', 1800);
insert into members values('BBB', 2, '2018-07-02', 'F', 1900);
insert into members values('CCC', 3, '2018-07-03', 'M', 2000);
insert into members values('DDD', 4, sysdate, 'M', 2000);

select * from members order by m_num;  
commit;

--문제 2.
--MEMBERS테이블과 LOCATIONS테이블을 INNER JOIN 하고 m_name, m_mum, street_address, location_id
--컬럼만 조회 m_num기준으로 오름차순 조회

select * from members;
select * from locations;

select m.m_name, m.m_num, l.street_address, l.location_id 
from members m 
inner join locations l 
on m.loca = l.location_id order by m_num asc;