제약조건문법(컬럼레벨/테이블레벨), 제약조건 5가지, 버퍼에 있는 자주 사용할 쿼리구문을 저장하는 방법

제약조건명 생성시 문법 2가지

제약조건명 생성시 oracle에서 권장하는 이름작성 방법이 두가지(컬럼레벨문법과 테이블레벨문법)가 있다.

Column-level 문법

• Not Null 제약조건사용시 column-level문법으로 작성해야한다!
• 나머지 4종류의 제약조건은 레벨문법 둘 중 하나만 쓰면 된다
• 기본형 : 테이블명약자_컬럼명약자_제약조건유형약자
• 예시 :
  제약조건 2개 이상 작성하고 싶다면 스페이스누르고 constraint 적은 뒤 다시 제약조건을 적으면 된다.

create table title_copy
( copy_id number(10),
title_id number(10) CONSTRAINT tc_tid_fk REFERENCES title(title_id),
rating VARCHAR2(4) CONSTRAINT tc_ra_nn not null 
  CONSTRAINT tc_ra_ck check (rating in ('G', 'PG', 'R', 'NC17', 'NR'));

Table-level 문법

• 기본형 : 테이블명약자_컬럼명약자_제약조건유형약자(컬럼명)
• 예시 :
  PK를 두 컬럼에 하려면 테이블레벨문법으로 작성해야한다.

create table title
( copy_id number(10),
title_id number(10) constraint t_tid_nn not null,
constraint t_id_pk primary key (copy_id, title_id));

제약조건 5가지

Not null (NN)

• 컬럼레벨문법만 가능
• 빈 값(null값)이 컬럼에 삽입될 수 없다.
• 테이블안의 여러컬럼에 선언 가능
• 예를들어, 회원가입테이블에서 이름과 전화번호가 필수인 경우 Not Null 제약조건을 걸어서 필수사항을 꼭 기입하도록 하는 조건이다
• 주로 사용하는 약자 nn
• 예시 :

create table test1
(id number(10) constraint t1_id_nn not null,
name varchar2(30) constraint t1_name_nn not null,
email varchar2(20),
start_date date);

Unique (UK)

• 고유한 값만 가능하다. (중복불가)
• 테이블안의 여러컬럼에 선언 가능
• 예시 :

create table test2
(id number(10) CONSTRAINT t2_id_nn not null CONSTRAINT t2_id_uk unique,
email VARCHAR2(20),
start_date date,
CONSTRAINT t2_email_uk unique(email));

Primary key (PK)

• Not null과 Unique성격을 동시에 가진다.
• 테이블안의 한번만 선언 가능.
  • 테이블에서 선언시 한 번에 여러 컬럼에 선언하면 됨
  • 나중에 다른 테이블과 join시 여러 컬럼의 조합값으로 join됨
• 어떤 컬럼을 Primary key 조건을 걸면 좋을까?
  • 개인의 선택이지만 지극히 개인정보는 선택하면 안된다
  • 예를들어 사원테이블에서 사원주민번호를 Primary key를 설정하면 주민번호가 대표key이므로 이곳 저곳에 데이터가 불려다니게 되므로 정보유출이 될 수 있다. (보안에 취약)
  • 따라서 사원 주민번호보단 사원번호를 Primary key를 설정하는 것이 좋다
• 예시1 :
  copy_id, title_id의 조합값에 pk를 걸 수 있다.

create table title
( copy_id number(10),
title_id number(10) constraint t_tid_nn not null,
constraint t_id_pk primary key (copy_id, title_id));

• 예시2 :

Foreign Key (FK)

• 자기 자신 테이블이나 다른 테이블의 특정 컬럼(PK, UK)를 참조하는 제약조건

• 아무컬럼이나 참조할 수 있는 것은 아니고 PK조건이나 UK조건이 있는 컬럼만 FK로 참조할 수 있다

• FK가 지정되어있으면 해당컬럼이 자식컬럼이 되고 참조한 PK이나 UK조건컬럼이 부모컬럼이 된다.

• 테이블안의 여러컬럼에 선언 가능

• 자식컬럼이 있는한 부모컬럼은 삭제되지않는다.

• 부모컬럼에 있는 내용만 자식컬럼에 들어올 수 있다. BUT null

• 유일하게 부모컬럼에 없는 null은 자식컬럼에 허용한다

  • null이란 zero도 아니고 공백도 아닌 특수한 값으로 아직 정의되지않은 값을 의미한다.
  • 부모컬럼에 없는 null이 자식컬럼에 들어올 수 있다

• 컬럼레벨 FK 예시 :

create table test4
(id number(10) CONSTRAINT t3_id_pk primary key,
email VARCHAR2(20),
department_id number(10) CONSTRAINT t4_dept_fkdept_fk references departments(department_id));

• 테이블레벨 FK 예시 :

create table test4
(id number(10) CONSTRAINT t3_id_pk primary key,
email VARCHAR2(20),
department_id number(10), 
CONSTRAINT t4_dept_fk FOREIGN KEY (department_id) references departments(department_id));

Check (CK)

• 해당 컬럼이 만족해야하는 조건들을 자유자재로 지정하는 제약 조건
• 테이블안에 여러컬럼에 선언 가능
• 컬럼레벨로 선언했을때와 테이블레벨로 선언했을때의 차이가 없는 유일한 제약조건이다
• 예시1 : 컬럼레벨과 테이블레벨 차이점 예시
  Salary라는 컬럼은 급여데이터이기때문에 CHECK (salary > 0)으로 제약조건을 걸수있다.
• 컬럼레벨 :

salary number(2), ... constraint emp_salary_min CHECK (salary > 0), ...
```	

- 테이블레벨 : 
위와 비교해서 알 수 있듯이 다른 제약조건들과 달리 두 레벨의 차이점이 없는 것이 특징이다 

```bash
salary number(2), ... constraint emp_salary_min CHECK (salary > 0), ...
```	
	- 
- 예시2 : 길이조건

```bash
주민번호 varchar2(13) constraint 000 check (length(주민번호) = 13), ...
```	

- 예시3 : 성별조건
아래 두 문장은 동일한 결과값을 가진다.

```bash
성별 varchar2(10) constraint 000 check (성별 = '여' or 성별 = '남' ), ...
성별 varchar2(10) constraint 000 check (성별 in ('여','남')), ...

제약조건 전체 예시

위에서 배운 제약조건들을 다 넣은 예시룰 보자

1. 사원번호는 주로 PK제약조건을 사용하기때문에 PK제약조건을 사용했다
2. 이름에 공백에 있으면 안되므로 NN제약조건을 사용했다
3. number(8,2)는 전체 데이터는 8byte이고 소수점 둘째자리까지 나타낸다는 의미이다
4. CHECK제약조건을 사용하여 급여는 0보다 커야한다는 제약을 걸었다
5. 내 테이블안의 FK제약조건을 사용했다
   • 부모칼럼 : employees1 테이블의 employee_id 칼럼
   • 자식칼럼 : employees1 테이블의 manager_id 칼럼
6. 다른 테이블을 가져와서 FK제약조건을 사용했다
   • 부모컬럼 : departments 테이블의 department_id 칼럼
   • 자식컬럼 : employees1 테이블의 department_id 칼럼

제약조건 확인하는 방법

DB사전에서 찾아보면 테이블에 선언된 제약조건을 확인할 수 있다

1. select 명령어를 통해 제약조건_이름과 제약조건_타입, C가 not null인지 checnk조건인지 서치, 래퍼런스가있으면 그이름 가져오기

2. from user_constraints 은 DB사전안에 제약조건을 확인할 수 있는 곳이다

3. where table_name = 테이블명을 적어줘야하는데 아무명이나 들어갈 수 있도록 &tablename으로 설정한다 -> 추후 sql쿼리문을 저장하기 위해서

4. 테이블명을 항상 대문자로 적어야한다

5. 데이터가 출력되었으나 보기 좋게 출력되지 않았다. 데이터를 가독성있게 작성해보자

6. not null과 check는 둘다 C라고 뜬다 따라서 search를 꼭 입력해줘야 그 뒤의 결과값으로 두가지가 구분 가능하다.search_condition으로 C가 not null조건인지 check조건인지 알수있다.

7. col 원하는컬럼이름 format a원하는크기 : col은 컬럼의 약어로 col이 나타나는 형식을 바꿔서 가독성을 높이는 명령어이다

   • 컬럼포맷
   • 여기서 col은 끝에 ;(세미콜론)을 넣지않았다. 이건 시스템언어이기때문이다
   • 컬럼아래의 점선의 한 점이 1byte라고 생각하면 된다 따라서 글씨크기는 보통 a20해주면 깔끔

8. enter value for tabkename: 테이블명은 항상 대문자로 적어준다

9. 짜잔- 가독성있게 데이터가 잘 출력되었다.

제약조건을 확인하는 쿼리만들기

col column_name format a15
col constraint_name format a15
col r_constraint_name format a20
select a.column_name, a.constraint_name, b.constraint_type, b.search_condition, b.r_constraint_name
from user_constraints b join user_cons_columns a
on a.constraint_name = b.constraint_name
where lower(a.table_name) = lower('&table_name')

버퍼에 있는 자주 사용할 쿼리구문을 저장하는 방법

• 버퍼에 있는 쿼리구문이 앞으로 자주 사용될 것같다는 판단이 들면 버퍼가 덮어씌어지기전에 따로 저장해놓으면 된다
• 쿼리구문이 들어가 있는 파일을 저장하려면 무조건 확장자는 .sql로 저장해야한다

1. 현재 버퍼의 쿼리구문 확인

   • 현재 버퍼에 어떤 퀴리구문이 남아있는지 확인하는 명령어는 l이다.
   • 버퍼로 들어가지않고도(ed) 버퍼에 남은 마지막 퀴리구문이 뭔지 확인하는 명령어이다. -> l에서 빠져나오려면 / + enter 하면 된다.

2. 쿼리구문 저장하기

   • 명령어는 save 저장경로/저장하고픈 파일명.sql 이다
   • home 안에 있는 oracle에 cons.sql이라고 저장해보려면 아래의 문구를 쓰면 된다.

     save /home/oracle/cons.sql

3. 저장한 쿼리파일 실행하기

   • 명령어는 @/저장경로/저장하고픈 파일명.sql
   • 위에 저장했더 경로의 파일을 그대로 불러오면된다

     @/home/oracle/cons.sql

4. 테이블이름 입력에 확인하고싶은 테이블이름을 무조건 대문자로 입력한다
   아래 5번에 제약조건쿼리구문이 실행된 것을 확인할 수 있다.