컬럼 속성 - 무결성 제약 조건

• not null : null 값이 입력되지 못하게 하는 조건
• unique : 중복된 값이 입력되지 못하게 하는 조건
• check : 주어진 값만 허용하는 조건
• primary key : not null + unique의 의미
• foregin key(외래키) : 다른 테이블의 필드(컬럼)을 참조해서 무결성을 검사하는 조건

-- 제약 조건
-- not null
-- 기본이 null 허용
CREATE TABLE null_test(
col1 varchar2(20) NOT NULL,
col2 varchar2(20) NULL,
col3 varchar2(20)
);

SELECT * FROM null_test;

INSERT INTO NULL_TEST (COL1, COL2)
VALUES ('aa', 'bb');

-- col1 오류 발생
INSERT INTO NULL_TEST (COL2, COL3)
VALUES ('cc', 'dd');

INSERT INTO NULL_TEST (COL1, COL3)
VALUES ('cc', 'dd');

-- unique
CREATE TABLE unique_test (
col1 varchar2(20) NOT NULL UNIQUE,
col2 varchar2(20) UNIQUE,
col3 varchar2(20) NOT NULL,
col4 varchar2(20) NOT null
);

SELECT * FROM unique_test;

INSERT INTO UNIQUE_TEST (COL1, COL2, COL3, COL4)
VALUES ('aa', 'bb', 'cc', 'dd');
INSERT INTO UNIQUE_TEST (COL1, COL2, COL3, COL4)
VALUES ('aa2', 'bb2', 'cc2', 'dd2');

UPDATE UNIQUE_TEST 
SET COL1 = 'aa'
WHERE COL2 = 'bb2';

INSERT INTO UNIQUE_TEST (COL1, COL2, COL3, COL4)
VALUES ('aa3', '', 'cc3', 'dd3');

INSERT INTO UNIQUE_TEST (COL1, COL2, COL3, COL4)
VALUES ('aa4', '', 'cc4', 'dd4');

-- unique 제약으로 들어가는 컬럼들은 그들의 조합이 유일해야 함
-- 각 컬럼의 데이터의 유일함은 의미가 없고, 조합이 유일해야함
CREATE TABLE UNIQUE_TEST2 (
col1 varchar2(20),
col2 varchar2(20),
-- constraints 제약 조건명 unique(대상 컬럼1, 대상 컬럼2)
CONSTRAINTS temp_unique UNIQUE(col1, col2)
);

SELECT * FROM UNIQUE_TEST2;

INSERT INTO UNIQUE_TEST2 (COL1, COL2)
VALUES ('aa', 'bb');
INSERT INTO UNIQUE_TEST2 (COL1, COL2)
VALUES ('aa', 'aa');
INSERT INTO UNIQUE_TEST2 (COL1, COL2)
VALUES ('aa', 'cc');
INSERT INTO UNIQUE_TEST2 (COL1, COL2)
VALUES ('bb', 'aa');
INSERT INTO UNIQUE_TEST2 (COL1, COL2)
VALUES ('bb', 'bb');
INSERT INTO UNIQUE_TEST2 (COL1, COL2)
VALUES ('bb', 'cc');

-- check
CREATE TABLE check_test(
gender varchar2(10) NOT NULL,
CONSTRAINTS check_gender CHECK (gender IN ('남자', '여자'))
);
SELECT * FROM CHECK_TEST ct;
INSERT INTO CHECK_TEST VALUES('남자');
INSERT INTO CHECK_TEST VALUES('여자');
INSERT INTO CHECK_TEST VALUES('M');
INSERT INTO CHECK_TEST VALUES('f');

1. primary key

• primary key, 기본키, pk, 주키, 실별자 등으로 불리고 있음
• 기본키 역시 기본적인 제약조건들을 테이블을 생성할 때 같이 정의
• 테이블 당 라나만 정의가 가능 ( 두 개 이상의 pk는 조합키/복합키라고 불리고, 묶어서 하나로 봄)
• pk는 not null+unique의 기능을 가지고 있음
• 자동 index가 생성되는데 이는 검색 키로써 검색 속도를 향상시킴

-- primary key
CREATE TABLE PRIMARY_key(
	student_id number(10) primary KEY,
	name varchar2(10)
);

CREATE TABLE PRIMARY_key2(
	student_id number(10),
	name varchar2(10),
	CONSTRAINTS student_pk PRIMARY KEY (student_id)
);

SELECT * FROM JOB_HISTORY jh ORDER BY EMPLOYEE_ID;

2. pk 선언 방법

create table pkTable (
col1 varvhar2(10) primary key,
col2 varchar2(10) constraints pk 이름 primary key,
col3 varchar2(10),
constraints pk이름 primary key(col3)
);

3. Foreign Key(외래키)

• 외부키, fk, 참조키, 외부키, 외부 식별자 등으로 불림
• fk가 정의된 테이블을 자식 테이블이라 칭함
• 참조되는 테이블 즉 pk가 있는 테이블을 부모 테이블이라고 함
• 부모 테이블의 pk 컬럼에 존재하는 데이터만 자식 테이블에 입력할 수 있음
• 부모 테이블은 자식의 데이터나 테이블이 삭제된다고 영향을 받지 않음
• 참조하는 데이터 컬럼과 데이터 타입이 반드시 일치해야함
• 참조할 수 있는 컬럼은 기본키(pk)이거나 unique만 가능 (보통 pk랑 엮음)

4. fk 선언 방법

• create table pTable (
  pCol1 number(10) primary key
  );
• create table cTable (
  cCol1 number(10) primary key,
  pCol1 number(10),
  constraints fk이름 Foreign key(pCol1)
  references pTable(pCol1) on delete cascade
  );
• 참조할 컬럼과 같은 컬럼이 자식 테이블에 존재해야함
• 같은 이름을 쓸 필요는 없지만 관계를 알아보기 쉽게 같은 컬럼명을 사용
• 자식 테이블에 값을 먼저 넣을 수 없음
• 참조되는 칼럼에 데이터가 있어야 값을 넣을 수 있음

-- fk
CREATE TABLE daddy (
idx number(10),
mid number(10) PRIMARY KEY 
);
CREATE TABLE daughter (
idx number(10) PRIMARY KEY,
mid number(10),
CONSTRAINTS fk_da FOREIGN key(mid)
REFERENCES daddy(mid) ON DELETE CASCADE 
);

INSERT INTO DADDY VALUES (1,10);
INSERT INTO DADDY VALUES (2,20);
SELECT * FROM DADDY d ;

INSERT INTO DAUGHTER VALUES (100,10);
INSERT INTO DAUGHTER VALUES (101,10);
INSERT INTO DAUGHTER VALUES (102,10);
INSERT INTO DAUGHTER VALUES (200,20);
INSERT INTO DAUGHTER VALUES (201,20);
SELECT * FROM DAUGHTER d ;

INSERT INTO DAUGHTER VALUES (300,30);

SELECT * FROM DADDY d 
LEFT OUTER JOIN DAUGHTER d2 
ON d.mid = d2.mid
;
DELETE daddy WHERE idx = 1;
SELECT * FROM DADDY d ;
SELECT * FROM DAUGHTER d2 ;

5. 삭제 옵션

• on delete cascade
  : 참조되는 부모 테이블의 행에 대한 delete를 허용
  즉, 참조되는 부모 테이블 값이 삭제되면 연쇄적으로 자식 테이블 값 역시 삭제
• on delete set null
  : 참조되는 부모 테이블의 행에 대한 delete를 허용
  이건 cascade와 달리 부모 테이블의 값이 삭제되면 해당 참조되는 자식 테이블의 값들은 null값으로 설정
  

문제

-- 야구 문제
CREATE SEQUENCE team_id_seq
INCREMENT BY 1
START WITH 1
nomaxvalue
MINVALUE 1
;
CREATE TABLE TB_TEAM (
team_id number(10) PRIMARY KEY,
LOCATION varchar2(10) NOT NULL,
team_name varchar2(10) NOT NULL,
num varchar2(15),
home varchar2(60)
);

SELECT * FROM TB_TEAM tt;

CREATE SEQUENCE player_id_seq
INCREMENT BY 1
START WITH 1
nomaxvalue
MINVALUE 1
;
CREATE TABLE TB_PLAYER (
p_num number(10) PRIMARY KEY,
p_name varchar2(10) NOT NULL,
p_position varchar2(10),
height number(3),
team_id number(10),
constraints fk_code foreign KEY (team_id)
REFERENCES tb_team(team_id) ON DELETE cascade
);
SELECT * FROM TB_PLAYER tp;

INSERT INTO TB_TEAM VALUES (team_id_seq.nextval, 'seoul', 'dusan', 024355737, 'http_dusan');
INSERT INTO TB_TEAM VALUES (team_id_seq.nextval, 'lotte', 'busan', 0422867987, 'http_busan');

INSERT INTO TB_PLAYER VALUES (player_id_seq.nextval, '류현진', '투수', 178, 1);
INSERT INTO TB_PLAYER VALUES (player_id_seq.nextval, '최정', '타자', 179, 1);
INSERT INTO TB_PLAYER VALUES (player_id_seq.nextval, '김', '포수', 175, 2);
INSERT INTO TB_PLAYER VALUES (player_id_seq.nextval, '이', '포수', 181, 2);
INSERT INTO TB_PLAYER VALUES (player_id_seq.nextval, '오타니', '타자', 188, 1);
INSERT INTO TB_PLAYER VALUES (player_id_seq.nextval, '톰', '투수', 190, 1);
INSERT INTO TB_PLAYER VALUES (player_id_seq.nextval, '김하성', '타자', 185, 2);
INSERT INTO TB_PLAYER VALUES (player_id_seq.nextval, '최지만', '투수', 178, 2);
INSERT INTO TB_PLAYER VALUES (player_id_seq.nextval, '마티스', '타자', 181, 1);
INSERT INTO TB_PLAYER VALUES (player_id_seq.nextval, '최정희', '포수', 176, 1);

-- 조회
SELECT tp.P_NUM, tp.P_NAME, tp.P_POSITION, tp.HEIGHT, tt.HOME
FROM TB_TEAM tt 
LEFT OUTER JOIN TB_PLAYER tp 
ON tt.TEAM_ID = tp.TEAM_ID 
;