9일차_제약 조건

--------------------------------------
UNIQUE KEY(UK)

1. Table에서 지정한 컬럼의 데이터가 중복되지 않고 유일하다. PRIMARY KEY와 유사하나 NULL값을 (중복)허용한다. 내부적으로 UNIQUE INDEX를 만들어 처리한다. 테이블 내에서 UK는 여러번 지정 가능합니다.

2.
컬럼 레벨의 형식
컬럼명 데이터타입 [CONSTRAINT constraint명] UNIQUE

테이블 레벨의 형식
컬럼명 데이터타입,
컬럼명 데이터타입,
...
CONSTRAINT constraint명 UNIQUE(컬럼1명, 컬럼2명, ...)

UK 지정 테스트 - 컬럼 레벨의 형식
CREATE TABLE test5 (
 col1 NUMBER(3) PRIMARY KEY
 ,col2 VARCHAR2(10) UNIQUE
);

INSERT INTO test5 (col1, col2)
 VALUES (1, 'TEST');
INSERT INTO test5 (col1, col2)
 VALUES (2, 'ABCD');
INSERT INTO test5 (col1, col2)
 VALUES (3, NULL);
INSERT INTO test5 (col1, col2)
 VALUES (4, 'ABCD');  --ORA-00001
INSERT INTO test5 (col1, col2)
 VALUES (3, NULL);  --ORA-00001
COMMIT;

UK 지정 테스트 - 테이블 레벨의 형식
CREATE TABLE test6 (
 col1 NUMBER(3)
 ,col2 VARCHAR2(10)
 ,CONSTRAINT TEST6_COL1_PK PRIMARY KEY(col1)
 ,CONSTRAINT TEST6_COL2_UK UNIQUE(col2)
);

--UK 제약 확인
SELECT * FROM constcheck WHERE table_name='TEST6';

UK 지정 테스트 - 테이블 생성 후 제약 추가
CREATE TABLE test7 (
 col1 NUMBER(3)
 ,col2 VARCHAR2(10)
);

ALTER TABLE test7
 ADD (CONSTRAINT TEST7_COL1_PK PRIMARY KEY(col1)
  ,CONSTRAINT TEST7_COL2_UK UNIQUE(col2));

--UK 제약 확인
SELECT * FROM constcheck WHERE table_name='TEST7';

------------------------------------------------------
HR 스키마 ERD에서 UNIQUE 제약 확인

SELECT * FROM constcheck WHERE table_name='EMPLOYEES';
--> EMAIL 컬럼에 UNIQUE 제약 지정됨.

------------------------------------------------------
CHECK (CK)

1. 컬럼에서 허용 가능한 데이터의 범위나 조건을 지정하기 위한 제약조건이다. 컬럼에 입력되는 데이터를 검사해서 조건에 맞는 데이터만 입력되도록 한다.

2.
컬럼 레벨의 형식
컬럼명 데이터타입 CONSTRAINT constraint명 CHECK(컬럼명 조건)

테이블레벨의 형식
컬럼명 데이터타입,
컬럼명 데이터타입,
...
CONSTRAINT constraint명 CHECK(컬럼명 조건)

 

CK 제약 테스트 - 컬럼 레벨의 형식
CREATE TABLE test8 (
 col1 NUMBER PRIMARY KEY
 ,col2 VARCHAR2(10)
 ,col3 NUMBER(3) CHECK (col3 BETWEEN 0 AND 100)
);

INSERT INTO test8 (col1, col2, col3)
 VALUES (1, 'HONG', 100);
INSERT INTO test8 (col1, col2, col3)
 VALUES (2, 'PARK', 80);
INSERT INTO test8 (col1, col2, col3)
 VALUES (3, 'CHOI', 200); --ORA-02290
COMMIT;

 

CK 제약 테스트 - 테이블 레벨의 형식
CREATE TABLE test9 (
 col1 NUMBER
 ,col2 VARCHAR2(10)
 ,col3 NUMBER(3)
 ,CONSTRAINT TEST9_COL1_PK PRIMARY KEY(col1)
 ,CONSTRAINT TEST9_COL3_CK CHECK (col3 BETWEEN 0 AND 100)
);

--제약 확인
SELECT * FROM constcheck WHERE table_name='TEST9';

 

CK 제약 테스트 - 테이블 생성 후 제약 추가
CREATE TABLE test10 (
 col1 NUMBER
 ,col2 VARCHAR2(10)
 ,col3 NUMBER(3)
);

ALTER TABLE test10
 ADD (CONSTRAINT TEST10_COL1_PK PRIMARY KEY(col1)
 ,CONSTRAINT TEST10_COL3_CK CHECK (col3 BETWEEN 0 AND 100));

--제약 확인
SELECT * FROM constcheck WHERE table_name='TEST10';

문제) ssn 컬럼에서 주민등록번호 입력시 성별 확인용(1 또는 2) 체크 제약 추가할 것.
CREATE TABLE member (
 sid NUMBER
 ,name VARCHAR2(10)
 ,ssn VARCHAR2(14)
 ,tel VARCHAR2(20)
);

--제약 추가
ALTER TABLE member
 ADD (CONSTRAINT MEMBER_SID_PK PRIMARY KEY(sid)
 ,CONSTRAINT MEMBER_SSN_CK CHECK (SUBSTR(ssn, 8, 1) IN (1, 2)));

--제약 확인
SELECT * FROM constcheck WHERE table_name='MEMBER';

--입력 테스트
INSERT INTO member (sid, name, ssn, tel)
 VALUES (1, 'KIM', '801212-1234567', '010-111-2222'); --O
INSERT INTO member (sid, name, ssn, tel)
 VALUES (2, 'PARK', '850101-3234567', '010-222-3333'); --X
COMMIT;

문제) 전화번호가 항상 010~ 으로 시작하도록 제약 추가
ALTER TABLE member
 ADD CONSTRAINT ~   ;

------------------------------------------------------
HR 스키마 ERD에서 CHECK 제약 확인

SELECT * FROM constcheck;

JOB_HISTORY C END_DATE end_date > start_date
JOB_HISTORY C START_DATE end_date > start_date

EMPLOYEES C SALARY salary > 0

 

 

 

 

--------------------------------------------------
FOREIGN KEY (FK)

1. 참조 키 또는 외래 키(FK)는 두 테이블의 데이터 간 연결을 설정하고 강제 적용하는 데 사용되는 열이다. 한 테이블의 기본 키 값이 있는 열을 다른 테이블에 추가하면 테이블 간 연결을 설정할 수 있다. 이 때 두 번째 테이블에 추가되는 열이 외래 키가 된다.

2. 부모 테이블이 먼저 생성된 후 자식 테이블(foreign key를 포함하는 테이블)이 생성되어야 한다.

3.
컬럼 레벨의 형식
컬럼명 데이터타입 CONSTRAINT constraint명
 REFERENCES 참조_테이블명 (참조_컬럼명)
 [ON DELETE CASCADE | ON DELETE SET NULL]

테이블레벨의 형식
컬럼명 데이터타입,
컬럼명 데이터타입,
...
CONSTRAINT constraint명 FOREIGN KEY(컬럼)
 REFERENCES 참조_테이블명 (참조_컬럼명)
 [ON DELETE CASCADE | ON DELETE SET NULL]

 

부모 테이블 생성 작업을 먼저 해야합니다. 부모 테이블에는 반드시 PK 또는 UK 제약이 있는 컬럼이 존재해야 합니다.
CREATE TABLE jobs (
 jikwi_id NUMBER
 ,jikwi_name VARCHAR2(10)
);

ALTER TABLE jobs
  ADD CONSTRAINT JOBS_JIKWI_ID_PK PRIMARY KEY(jikwi_id);
 

INSERT INTO jobs (jikwi_id, jikwi_name) VALUES (1, '과장');
INSERT INTO jobs (jikwi_id, jikwi_name) VALUES (2, '대리');
INSERT INTO jobs (jikwi_id, jikwi_name) VALUES (3, '사원');
COMMIT;

FK 제약 지정 - 컬럼 레벨의 형식
CREATE TABLE employees (
 sid NUMBER PRIMARY KEY
 ,name VARCHAR2(10)
 ,jikwi_id NUMBER REFERENCES jobs(jikwi_id)
);

INSERT INTO employees (sid, name, jikwi_id)
 VALUES (1, '홍길동', 1);
INSERT INTO employees (sid, name, jikwi_id)
 VALUES (2, '김길동', 2);
INSERT INTO employees (sid, name, jikwi_id)
 VALUES (3, '박길동', 4);  --ORA-02291
COMMIT;

SELECT sid, name, jikwi_name
  FROM employees e, jobs j
  WHERE e.jikwi_id = j.jikwi_id;

 

FK 제약 지정 - 테이블 레벨의 형식
CREATE TABLE employees (
 sid NUMBER
 ,name VARCHAR2(10)
 ,jikwi_id NUMBER
 ,CONSTRAINT EMPLOYEES_SID_PK PRIMARY KEY(sid)
 ,CONSTRAINT EMPLOYEES_JIKWI_ID_FK
   FOREIGN KEY(jikwi_id)
   REFERENCES jobs(jikwi_id)
);

FK 제약 지정 - 테이블 생성 후 제약 추가
CREATE TABLE employees (
 sid NUMBER
 ,name VARCHAR2(10)
 ,jikwi_id NUMBER
);

ALTER TABLE employees
 ADD (CONSTRAINT EMPLOYEES_SID_PK PRIMARY KEY(sid)
  ,CONSTRAINT EMPLOYEES_JIKWI_ID_FK
   FOREIGN KEY(jikwi_id)
   REFERENCES jobs(jikwi_id));

4. FOREIGN KEY 생성 시 주의사항
- 참조하고자 하는 부모 테이블을 먼저 생성해야 한다.
- 참조하고자 하는 컬럼이 PRIMARY KEY 또는 UNIQUE 제약조건이 있어야 한다.
- 테이블 사이에 PRIMARY KEY와 FOREIGN KEY가 정의 되어 있으면, primary key 삭제 시 foreign key 컬럼에 그 값이 입력되어 있으면 삭제가 안 된다. (단, FK 선언 때 ON DELETE CASCADE나 ON DELETE SET NULL옵션을 사용한 경우에는 삭제된다.)
- 부모 테이블을 삭제하기 위해서는 자식 테이블을 먼저 삭제해야 한다.
- 서로 제약에 의해 참조하는 경우는 서로 삭제되지 않는다. 제약조건을 먼저 삭제해야 한다.

ALTER TABLE 테이블명
 DROP CONSTRAINT (참조)제약명;

DROP TABLE jobs; --ORA-02449

DROP TABLE employees; --자식 테이블 먼저 삭제

DROP TABLE jobs; --부모 테이블 삭제

 

-------------------------------------
--(참조)제약 조건 확인용 뷰 생성
CREATE OR REPLACE VIEW constraint_check
AS
SELECT  uc.table_name AS table_name   --테이블명
 , ucc.column_name AS column_name --지정된 컬럼
 , uc.constraint_name AS constraint_name --제약명
 , uc.constraint_type AS constraint_type --제약조건 종류(P, R, U, C)
 , uc.search_condition AS search_condition --CHECK 제약 조건식
 , uc.delete_rule AS delete_rule --ON DELETE CASCADE 옵션
 , (SELECT table_name
  FROM user_cons_columns
  WHERE constraint_name=uc.r_constraint_name)
  AS r_table_name  --참조하는 테이블
 , (SELECT column_name
  FROM user_cons_columns
  WHERE constraint_name=uc.r_constraint_name)
  AS r_conlumn_name  --참조하는 컬럼
 FROM user_constraints uc, user_cons_columns ucc
 WHERE uc.constraint_name = ucc.constraint_name;

--제약 조건 확인 방법
SELECT * FROM constraint_check;

SELECT * FROM constraint_check
  WHERE table_name='테이블명';

-------------------------------------------------
HR 스키마 ERD에서 FK 제약 확인 (자식 테이블, 부모 테이블 확인)

SELECT * FROM constraint_check;

 

-------------------------------------------------
ON DELETE CASCADE 옵션 지정 테스트

ON DELETE CASCADE 옵션 지정 전 -----------------------
CREATE TABLE jobs (
 jikwi_id NUMBER
 ,jikwi_name VARCHAR2(10)
 ,CONSTRAINT JOBS_JIKWI_ID_PK PRIMARY KEY(jikwi_id)
);

INSERT INTO jobs (jikwi_id, jikwi_name) VALUES (1, '과장');
INSERT INTO jobs (jikwi_id, jikwi_name) VALUES (2, '대리');
INSERT INTO jobs (jikwi_id, jikwi_name) VALUES (3, '사원');
COMMIT;

FK 제약 지정
CREATE TABLE employees (
 sid NUMBER PRIMARY KEY
 ,name VARCHAR2(10)
 ,jikwi_id NUMBER REFERENCES jobs(jikwi_id)
);

INSERT INTO employees (sid, name, jikwi_id)
 VALUES (1, '홍길동', 1);
INSERT INTO employees (sid, name, jikwi_id)
 VALUES (2, '김길동', 2);
COMMIT;

employees 테이블에서 '김길동' 자료 삭제 시도. --O
DELETE FROM employees WHERE sid=2;

jobs 테이블에서 '과장' 자료 삭제 시도. --X
DELETE FROM jobs WHERE jikwi_id=1;

jobs 테이블에서 '사원' 자료 삭제 시도. --O
DELETE FROM jobs WHERE jikwi_id=3;

--> ON DELETE CASCADE 옵션 지정이 필요합니다.
--> employees 테이블에서 FK 제약 제거 후 다시 FK 제약 지정
SELECT * FROM constraint_check WHERE table_name='EMPLOYEES';
--> NO_ACTION

ALTER TABLE employees
 DROP CONSTRAINT 제약명;

ON DELETE CASCADE 옵션 지정 후 -----------------------
ALTER TABLE employees
 ADD CONSTRAINT EMPLOYEES_JIKWI_ID_FK
  FOREIGN KEY (jikwi_id)
  REFERENCES jobs(jikwi_id)
  ON DELETE CASCADE;

SELECT * FROM constraint_check WHERE table_name='EMPLOYEES';
--> CASCADE

--> 옵션 지정 후에는 PK 자료를 언제든지 삭제 가능.
    단. PK 지정 자료가 삭제되면 FK 자료도 같이 삭제됨.

jobs 테이블에서 '과장' 자료 삭제 시도. --O
DELETE FROM jobs WHERE jikwi_id=1;
--> employees 테이블의 '홍길동' 자료도 같이 삭제됨.

과정 개설 취소하는 경우 과정에 등록된 과목들을 같이 삭제해야 하는 경우가 있다.
이런 경우는 ON DELETE CASCADE 옵션이 필요하다.

-------------------------------------
HR 스키마 ERD에서 ON DELETE CASCADE 옵션 분석

SELECT * FROM constraint_check WHERE table_name='JOB_HISTORY';
--> NO_ACTION으로 지정됨.

SELECT * FROM constraint_check WHERE table_name='EMPLOYEES';
--> NO_ACTION으로 지정됨.

* HR 스키마에서 모든 테이블은 ON DELETE CASCADE 옵션이 적용 안된 상태임.

-------------------------------------
NOT NULL

1. 테이블에서 지정한 컬럼의 데이터가 NULL 값을 갖지 못한다.

2.
컬럼레벨의 형식
컬럼명 데이터타입 [CONSTRAINT constraint명] NOT NULL

테이블레벨의 형식
컬럼명 데이터타입,
컬럼명 데이터타입,
...
CONSTRAINT constraint명 CHECK(컬럼명 IS NOT NULL)

3. 기존 테이블에 NOT NULL 컬럼을 추가 시에는 ADD보다는 MODIFY절을 사용하면 더 간단하게 부여할 수 있다.

ALTER TABLE 테이블명
 ADD CONSTRAINT constraint명 CHECK(컬럼명 IS NOT NULL);

ALTER TABLE 테이블명
 MODIFY 컬럼명 자료형 NOT NULL;

4. 기존 데이터를 NULL로 수정하는 경우에는 오류가 발생한다.

NOT NULL 제약 테스트 - 컬럼 레벨의 형식
CREATE TABLE test10 (
 col1 NUMBER PRIMARY KEY
 ,col2 VARCHAR2(10) NOT NULL
);

INSERT INTO test10 (col1, col2)
 VALUES (1, 'TEST');
INSERT INTO test10 (col1, col2)
 VALUES (2, 'ABCD');
INSERT INTO test10 (col1, col2)
 VALUES (3, NULL);  --X
COMMIT;

NOT NULL 제약 테스트 - 테이블 레벨의 형식
CREATE TABLE test10 (
 col1 NUMBER
 ,col2 VARCHAR2(10)
 ,CONSTRAINT 제약명 PRIMARY KEY(col1)
 ,CONSTRAINT 제약명 CHECK(col2 IS NOT NULL)
);

NOT NULL 제약 테스트 - 테이블 생성 후 제약 추가
CREATE TABLE test10 (
 col1 NUMBER
 ,col2 VARCHAR2(10)
);

ALTER TABLE test10
 ADD CONSTRAINT 제약명 PRIMARY KEY(col1);
ALTER TABLE test10
 ADD CONSTRAINT 제약명 CHECK(col2 IS NOT NULL);
--> NOT NULL 제약 추가하는 경우만 아래 방법도 가능
ALTER TABLE test10
 MODIFY col2 NOT NULL;

NOT NULL 제약 확인 -- ADD CONSTRAINT 방법 사용한 경우
SELECT * FROM constraint_check WHERE table_name='TEST10';

NOT NULL 제약 확인 -- MODIFY 방법 사용한 경우
DESC test10;

--NOT NULL 제약 확인
SELECT * FROM user_tab_columns
 WHERE table_name='테이블명';
--> nullable 컬럼 확인

-------------------------------------
HR 스키마 ERD에서 NOT NULL 옵션 분석

SELECT * FROM user_tab_columns;
--> nullable 컬럼 확인

 

------------------------------------
DEFAULT 표현식

1. insert와 update 문에서 특정 값이 아닌 디폴트값을 입력할 수 도 있다.

2.
컬럼명 데이터타입 DEFAULT 디폴트값

3. INSERT 명령 실행시 해당 컬럼에 값을 할당하지 않거나, DEFAULT 키워드에 의해서 디폴트값을 입력할 수 있다.

4. DEFAULT 키워드와 다른 제약 (NOT NULL 등) 표기가 같이 오는 DEFAULT 키워드 를 먼저 표기할 것.

DEFAULT 표현식 테스트
CREATE TABLE bbs (
 sid NUMBER PRIMARY KEY  --글번호(자동 번호 증가, 자동 입력)
 ,name VARCHAR2(10)      --글쓴이 이름
 ,content VARCHAR2(100)  --글내용
 ,writeday DATE  DEFAULT SYSDATE  --글쓴 날짜(현재 날짜 자동 입력)
);
--> sid 운영하려면 시퀀스 객체 필요함.
--> 자동 입력되는 컬럼은 입력 항목에서 제외해야 됩니다.

INSERT INTO bbs (sid, name, content) --writeday 컬럼 지정 제외
 VALUES (1, 'hong', 'TEST');
INSERT INTO bbs (sid, name, content) --writeday 컬럼 지정 제외
 VALUES (2, 'park', 'ABCD');
COMMIT;
SELECT * FROM bbs;
--> 출력해보면 writeday 컬럼에 날짜가 자동 입력된 것을 확인할 수 있다.

DEFAULT 표현식 확인 -------------------
SELECT * FROM user_tab_columns
 WHERE table_name='BBS';
--> data_default 컬럼의 값 확인

테이블 생성 후 DEFAULT 표현식 추가 --------------
ALTER TABLE 테이블명
 MODIFY 컬럼명 [자료형] DEFAULT 값;

DEFAULT 표현식 삭제 --------------------
ALTER TABLE 테이블명
 MODIFY 컬럼명 [자료형] DEFAULT NULL;

-------------------------------------
HR 스키마 ERD에서 DEFAULT 확인

SELECT * FROM user_tab_columns;
--> data_default 컬럼 확인

 

-------------------------------------
--(참조)제약 조건 및 NOT NULL, DEFAULT 확인용 뷰 생성
CREATE OR REPLACE VIEW constraint_check
AS
SELECT utc.table_name AS table_name
 , utc.column_name AS column_name
 , data_type, data_length, data_precision
 , nullable, data_default
 , constraint_name, constraint_type
 , search_condition, delete_rule
 , r_table_name, r_conlumn_name
 FROM
 (SELECT  uc.table_name AS table_name   --테이블명
 , ucc.column_name AS column_name --지정된 컬럼
 , uc.constraint_name AS constraint_name --제약명
 , uc.constraint_type AS constraint_type --제약조건 종류(P, R, U, C)
 , uc.search_condition AS search_condition --CHECK 제약 조건식
 , uc.delete_rule AS delete_rule --ON DELETE CASCADE 옵션
 , (SELECT table_name
  FROM user_cons_columns
  WHERE constraint_name=uc.r_constraint_name)
  AS r_table_name  --참조하는 테이블
 , (SELECT column_name
  FROM user_cons_columns
  WHERE constraint_name=uc.r_constraint_name)
  AS r_conlumn_name  --참조하는 컬럼
 FROM user_constraints uc, user_cons_columns ucc
 WHERE uc.constraint_name = ucc.constraint_name) ucc
  , user_tab_columns utc
 WHERE ucc.table_name(+)=utc.table_name
  AND ucc.column_name(+)=utc.column_name;

SELECT * FROM constraint_check
 WHERE table_name='BBS';

-------------------------------------
과제) HR 스키마 ERD를 이용한 테이블 재구성

팀별로 HR 스키마에 있는 모든 테이블(7개)을 재구성하는 쿼리 작성

1. 기존 테이블의 정보 수집
- 테이블구조 (컬럼이름(column_name 컬럼), 자료형(data_type 컬럼), DEFAULT 표현식(data_default 컬럼), NOT NULL(nullable 컬럼)) 확인
SELECT * FROM user_tab_columns 
    WHERE table_name='테이블명';

- 제약 조건 확인
SELECT * FROM constraint_check
    WHERE table_name='테이블명';

2. 테이블 생성 (컬럼이름, 자료형, DEFAULT 표현식, NOT NULL)
CREATE TABLE regions (
 ....
);

3. PRIMARY KEY, CHECK, UNIQUE 제약 추가
ALTER TABLE regions
 ADD CONSTRAINT 제약명(테이블명_컬럼명_제약종류)  ....;

4. FOREIGN KEY 제약 추가 (ON DELETE CASCADE 옵션 확인)
ALTER TABLE regions
 ADD CONSTRAINT ....;

5. 각 테이블별로 샘플 데이터 5개 이상 입력 쿼리 추가
INSERT INTO regions (...) VALUES (...);
...
COMMIT;

6. 작성 후 .SQL 파일로 저장
team1.sql

7. 연습용 계정(team1~5, 1234) 추가. 뷰 생성 권한 추가.

8. .SQL 파일 실행 테스트. 연습용 계정 로그인 후 테스트.
@d:\team1.sql

9. 기존 테이블 정보 확인용 쿼리 작성 추가.  연습용 계정 로그인 후 테스트.
- 테이블 데이터 확인
SELECT * FROM regions;

- 테이블구조 (컬럼이름, 자료형, DEFAULT 표현식, NOT NULL) 확인
SELECT * FROM user_tab_columns 
    WHERE table_name='테이블명';

- 제약 조건 확인
SELECT * FROM constraint_check
    WHERE table_name='테이블명';

----------------------------------------