Oracle 컬럼 속성

1. 컬럼 속성(무결성 제약조건)

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

-- not null

CREATE TABLE null_test(
	col1    varchar2(20) NOT NULL,
	col2    varchar2(20) NULL,
	col3    varchar2(20) 
);


INSERT INTO NULL_TEST nt (col1, col2) 
VALUES ('aa', 'bb');

-- 오류 발생. ORA-01400: cannot insert NULL into ("HR"."NULL_TEST"."COL1")
INSERT INTO NULL_TEST nt (col2, col3) 
VALUES ('cc', 'dd');

INSERT INTO NULL_TEST nt (col1, col3) 
VALUES ('cc', 'dd');

-- unique

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


INSERT INTO UNIQUE_TEST ut (col1, col2, col3, col4)
VALUES ('aa', 'bb', 'cc', 'dd');
INSERT INTO UNIQUE_TEST ut (col1, col2, col3, col4)
VALUES ('aa2', 'bb2', 'cc2', 'dd2');
INSERT INTO UNIQUE_TEST ut (col1, col2, col3, col4)
VALUES ('aa3', '', 'cc3', 'dd3');

-- 오류 ORA-00001: unique constraint (HR.SYS_C007003) violated

UPDATE UNIQUE_TEST 
SET    col1 = 'aa'
WHERE  col2 = 'bb2';

-- unique 제약으로 들어가는 컬럼들은 그들의 조합이 유일해야 한다.
-- 각 컬럼의 데이터의 유일함은 의미가 없고, 조합이 유일해야 한다.

CREATE TABLE UNIQUE_TEST2(
	col1  varchar2(20),
	col2  varchar2(20),
	CONSTRAINTS temp_unique2 unique(col1, col2)
);

INSERT INTO UNIQUE_TEST2 ut (col1, col2)
VALUES ('aa', 'aa');
INSERT INTO UNIQUE_TEST2 ut (col1, col2)
VALUES ('aa', 'aa2');
INSERT INTO UNIQUE_TEST2 ut (col1, col2)
VALUES ('aa2', 'aa');
INSERT INTO UNIQUE_TEST2 ut (col1, col2)
VALUES ('aa3', 'aa');

-- check

CREATE TABLE check_test(
	gender   varchar2(10) NOT NULL
	CONSTRAINT check_gender CHECK(gender IN('M', 'F'))
);


INSERT INTO CHECK_TEST ct VALUES ('M');
INSERT INTO CHECK_TEST ct VALUES ('F');

--오류 : ORA-02290: check constraint (HR.CHECK_GENDER) violated

INSERT INTO CHECK_TEST ct VALUES ('남');
INSERT INTO CHECK_TEST ct VALUES ('여');

1-1. 기본키(Primary Key)

• 기본키는 기본적인 제약 조건들 테이블을 생성할 때 같이 정의한다.
• 테이블당 하나만 정의 가능하다. (두 개 이상의 컬럼으로 정의가 될 때, 묶어서 하나로 본다. 조합키/복합키라고 한다)
• 주키/기본키/식별자 등으로 불리고 있다.
• 자동 INDEX가 생성되는데 이는 검색 키로서 검색 속도를 향상시킨다.
• not null + unique + index

1-2. 기본키 선언 방법

create table pkTest(
		col1   varchar2(10) primary key,
		col2   varchar2(10)
		constraints pk_name primary key(col2)
	);
    
    

-- 기본키(Primary Key)

CREATE TABLE primary_test(
	student_id	number(10) PRIMARY KEY,
	name 		varchar2(20)
);


CREATE TABLE primary_test2(
	student_id	number(10),
	name 		varchar2(20),
	CONSTRAINT student_pk PRIMARY KEY (student_id)
);

1-3. Foreign Key(외래키)

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

1-4. FK 선언 방법

create table pTable(
	p_pk	  number  primary key
);

create table cTable(
	c_pk      number primary key,
	p_pk	    number,
	constraint fk_name foreign key (p_pk)
	references pTable(p_pk) on delete cascade
);

• 참조할 컬럼과 같은 컬럼이 자식 테이블에 존재해야 한다. 같은 이름을 쓸 필요는 없지만, 관계를 알아보기 쉽게 같은 컬럼명을 사용한다. 그리고 자식 테이블에 값을 먼저 넣을 순 없다.
• 참조되는 컬럼에 데이터가 있어야지만 값을 넣을 수 있다.

1-5. 삭제 옵션

• on delete cascade
  -> 참조되는 부모 테이블의 행에 대한 delete를 허용한다.
  -> 즉, 참조되는 부모 테이블 값이 삭제되면 연쇄적으로 자식 테이블값 역시 삭제된다.

• on delete set null
  -> 참조되는 부모 테이블의 행에 대한 delete를 허용한다.
  -> 이건 cascade와 다르게 부모 테이블의 값이 삭제되면 참조하는 자식 테이블의 값들은 null값으로 설정된다.

  	CREATE TABLE daddy(
  		idx    NUMBER , 
  		mid    NUMBER PRIMARY KEY 
  	);
  
  	CREATE TABLE daughter(
  		idx   NUMBER PRIMARY KEY ,
  		mid   NUMBER,
  		CONSTRAINTS fk_da FOREIGN KEY (mid)
  		REFERENCES DADDY(mid) ON DELETE CASCADE 	
  	);
  
  	INSERT INTO DADDY d VALUES (1, 10);
  	INSERT INTO DADDY d VALUES (2, 20);
  	SELECT * FROM DADDY d ;

-- ORA-02291: integrity constraint (HR.FK_DA) violated - parent key not found

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


DELETE FROM DADDY d WHERE idx = 1;

CREATE TABLE daughter2(
	idx   NUMBER PRIMARY KEY ,
	mid   NUMBER,
	CONSTRAINTS fk2_da FOREIGN KEY (mid)
	REFERENCES DADDY(mid) ON DELETE SET NULL  	
);

INSERT INTO DAUGHTER2 d VALUES (200, 20);
INSERT INTO DAUGHTER2 d VALUES (201, 20);
INSERT INTO DAUGHTER2 d VALUES (202, 20);
SELECT * FROM DAUGHTER2 d ;

DELETE FROM DADDY d WHERE idx = 2;