이론/자바 풀스택 국비수업

220420~21 데이터베이스 컬럼속성

달거북씨 2022. 5. 2. 09:22

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

  • NOT NULL : 널값이 입력되지 못하게 하는 조건
  • UNIQUE : 중복된 값이 입력되지 못하게 하는 조건
  • CHECK : 주어진 값만 허용하는 조건
  • PRIMARY KEY : NOT NULL + UNIQUE의 의미
  • FOREIGN KEY : 외래키. 다른 테이블의 필드(컬럼)을 참조해서 무결성을 검사하는 조건

 

1-1. NOT NULL 

각 테이블 별로 Properties의 Columns을 확인하면 Not Null 여부를 확인할 수 있다.

Not Null이 체크되어 있는 컬럼은 Null 값이 들어가면 에러가 난다.

-- NOT NULL
CREATE TABLE NULL_TEST(
	col1	varchar2(20) NOT NULL,
	col2	varchar2(20) NULL,
	col3	varchar2(30)
);

SELECT * FROM NULL_TEST;

INSERT INTO NULL_TEST(col1, col2) VALUES ('aa', 'bb');
INSERT INTO NULL_TEST(col1) VALUES ('aa2');
-- col1에만 'aa2' 값을 지정하고 col2는 null이 되어도 에러가 나지 않는다.

INSERT INTO NULL_TEST(col2) VALUES ('bb2');
-- col2에만 'bb2' 값을 지정하고 col1 null값을 주면 에러가 난다.
-- 테이블 생성시 col1 컬럼은 NOT NULL 속성을 부여했기 때문이다.

 

1-2. UNIQUE

각 테이블 별로 Properties의 Constraints를 확인하면 속성(Type)을 확인할 수 있다.

 

-- unique
-- unique_test 테이블 생성
CREATE TABLE unique_test(
	col1	varchar2(20)	UNIQUE NOT NULL ,
	col2 	varchar2(20)	UNIQUE ,
	col3	varchar2(20)	NOT NULL ,
	col4 	varchar2(20)	NOT NULL ,
	CONSTRAINTS temp_unique unique(col3, col4)
);

COL1, COL2에 각각, COL3, COL4가 묶여서 UNIQUE 속성이 부여되어 있다. COL1, COL3, COL4는 NOT NULL 속성을 갖고 있다.

-- INSERT
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');

 

INSERT 쿼리 실행 결과

-- 추가 INSERT
-- 오류1. COL1은 UNIQUE 조건이 걸려 있는데 두번째 컬럼과 값이 같다.
INSERT INTO UNIQUE_TEST (col1, col2, col3, COL4)
	VALUES ('aa2', 'bb3', 'cc3', 'dd3');

-- 오류2. COL2는 UNIQUE 조건이 걸려 있는데 두번째 컬럼과 값이 같다.
--INSERT INTO UNIQUE_TEST (col1, col2, col3, COL4)
	VALUES ('aa3', 'bb2', 'cc3', 'dd3');

-- 정상 INSERT
INSERT INTO UNIQUE_TEST (col1, col2, col3, COL4)
	VALUES ('aa3', 'bb3', 'cc3', 'dd3');
    
-- UPDATE 오류 경우
-- 두번째 컬럼에 있는 col1의 값을 첫번째 컬럼에 있는 COL1의 값과 같은 값으로 변경하려고 해서
UPDATE UNIQUE_TEST SET col1 = 'aa' WHERE col2 = 'bb2';


-- UNIQUE와 NULL 값은 상관없다
-- NULL 값이 중복으로 들어가더라도 에러가 나지 않는다.
INSERT INTO UNIQUE_TEST (col1, col3, COL4)
	VALUES ('aa4', 'cc4', 'dd4');
INSERT INTO UNIQUE_TEST (col1, col3, COL4)
	VALUES ('aa5', 'cc5', 'dd5');


-- COL3, COL4 에러
INSERT INTO UNIQUE_TEST (col1, col2, col3, col4)
	values('aa6', 'bb6', 'cc', 'dd');
    
-- 정상 INSERT 
-- unique(col3, col4) : 조합이 유일하면 정상 INSERT 됨
-- 컬럼 안에서 중복되는 것은 상관 없음
INSERT INTO UNIQUE_TEST (col1, col2, col3, col4)
	values('aa6', 'bb6', 'cc1', 'dd');
INSERT INTO UNIQUE_TEST (col1, col2, col3, col4)
	values('aa7', 'bb7', 'cc6', 'dd');
-- unique 추가 예제
CREATE TABLE unique_test2(
	col1	varchar2(20),
	col2	varchar2(20),
	CONSTRAINTS temp_unique2 unique(col1, col2)
);

SELECT * FROM unique_test2;

INSERT INTO UNIQUE_TEST2 (col1, col2)
	VALUES ('aa', 'bb');
INSERT INTO UNIQUE_TEST2 (col1, col2)
	VALUES ('aa', 'cc');
INSERT INTO UNIQUE_TEST2 (col1, col2) 
	VALUES ('aa', 'dd');
INSERT INTO UNIQUE_TEST2 (col1, col2)
	VALUES ('aa', 'ee');
-- COL1의 컬럼값은 계속 중복이지만, COL1, COL2의 조합이 중복이 아니기 때문에 모두 OK

 

1-3. CHECK

-- 테이블 생성
CREATE TABLE check_test(
	gender	varchar2(10)	NOT NULL
	CONSTRAINTS CHECK_gender CHECK (gender IN('남성', '여성'))
);

GENDER 컬럼은 '남성'과 '여성'만 들어가야 하며, NULL 값이 없어야 한다.

-- 정상 INSERT
INSERT INTO CHECK_TEST VALUES ('남성');

-- 에러 : 남성 또는 여성으로 입력되게끔 해놨으므로 M은 들어갈 수 없다.
-- INSERT INTO CHECK_TEST VALUES ('M');

 

 

1-4. 기본키(Primary Key)

기본키 역시 기본적인 제약조건들은 테이블을 생성할 때 같이 정의한다.

테이블 당 하나만 정의 가능하다(두 개 이상의 PI는 조합키/복합키라고 불린다. 묶어서 하나로 본다).

주키 / 기본키 / 식별자 / PK 등으로 불린다.

PK는 NOT NULL + UNIQUE의 기능을 가지고 있다.

자동으로 INDEX가 생성이 되는데, 이는 검색 키로서 검색 속도를 향상시킨다.

 

PK 선언방법

  • 테이블 생성 시 설정
-- 인라인
CREATE TABLE TESTTABLE(
		col1 	varchar2(10) primary key,
);

CREATE TABLE TESTTABLE(
		col1	varchar2(10) constraints pk이름 primary key
);
    
-- 아웃라인
CREATE TABLE TESTTABLE(
		col1	varchar2(10),
       	 	col2	varchar2(10),
		constraints pk제약조건이름 primary key(col1)
);
  • 테이블 생성 후 설정
ALTER TABLE 테이블명 ADD CONSTRAINTS 제약조건명 PRIMARY KEY (컬럼명)

 

예제

-- PK
-- 인라인
CREATE TABLE PRIMERY_KEY(
	STUDENT_ID	NUMBER(10) PRIMARY KEY,
	NAME		VARCHAR2(20)
);

SELECT * FROM PRIMERY_KEY ;

-- 아웃라인
CREATE TABLE PRIMARY_KEY2(
	STUDENT_ID	NUMBER(10),
	NAME		VARCHAR2(20),
	CONSTRAINTS STUDENT_PK PRIMARY KEY(STUDENT_ID)
);

SELECT * FROM PRIMARY_KEY2 pk ;

-- 테이블 생성 후 기본키를 생성하는 방법
CREATE TABLE PRIMARY_KEY3(
	STUDENT_ID	NUMBER(10),
	NAME		VARCHAR2(20)
);

SELECT * FROM PRIMARY_KEY3;

ALTER TABLE PRIMARY_KEY3 
ADD CONSTRAINTS STUDENT_PK3 PRIMARY KEY(STUDENT_ID);

PRIMEREY_KEY 테이블의 PK
PRIMARY_KEY2 테이블의 PK
PRIMERY_KEY3 테이블의 PK

 

1-5. FOREIGN KEY (외래키)

외부키, 외래키, 참조키, 외부식별자,FK로 불린다.

FK가 정의된 테이블은 자식 테이블이라 칭한다.

참조되는 테이블, 즉 PK가 있는 테이블을 부모 테이블이라 한다.

부모 테이블의 PK컬럼에 존재하는 데이터만 자식 테이블에 입력할 수 있다.

부모 테이블은 자식의 데이터나 테이블이 삭제된다고 영향을 받지 않는다.

참조하는 데이터 컬럼과 데이터 타입이 반드시 일치해야 한다. 

 

FK 선언방법

  • 테이블 생성 시 설정
CREATE TABLE pTable(
	pPK	NUMBER(10)		PRIMARY KEY
);

CREATE TABLE cTable(
	pPK	NUMBER(10),
	col1	VARCHAR2(10),
	CONSTRAINTS fk_code FOREIGN KEY(pPK)
		REFERECES pTable(pPK) ON DELETE CASCADE		
);

	CONSTRAINTS [제약조건명] FOREIGN KEY([컬럼명])
		REFERECES [참조할 테이블명] ([참조할 컬럼])
		[ON DELETE CASCADE || ON DELETE SET NULL]
  • 테이블 생성 후 설정
ALTER TABLE [테이블명]
ADD CONSTRAINTS [외래키 이름] FOREIGN KEY([참조컬럼])
REFERENCES [참조 테이블명]([참조컬럼])

 

예제

-- FK
-- 테이블 생성과 외래키 지정 동시에
CREATE TABLE foreign_key(
	department_id	number(4),
	CONSTRAINTS dept_fk FOREIGN KEY (department_id)
	REFERENCES departments (department_id)
);

-- 테이블 생성 후 외래키 지정하는 방법
CREATE TABLE foreign_key2(
	department_id	number(4)
);

ALTER TABLE FOREIGN_KEY2 
ADD CONSTRAINTS dept_fk2 FOREIGN KEY (department_id)
REFERENCES departments (department_id)
;

FOREIGN_KEY 테이블은 DEPARTMENTS 테이블의 DEPARMENT_ID를 FK로 갖는다.
FOREIGN_KEY2 테이블은 DEPARTMENTS 테이블의 DEPARMENT_ID를 FK로 갖는다.

 

외래키 삭제

  • ON DELETE CASCADE
    : 참조되는 부모 테이블의 행에 대한 DELETE를 허용한다.
      즉, 참조되는 부모 테이블 값이 삭제되면 연쇄적으로 자식 테이블 값 역시 삭제된다.
  • ON DELETE SET NULL
    : 참조되는 부모 테이블의 행에 대한 DELETE를 허용한다.
      이건 CASCADE와 다르게 부모 값이 삭제되면서 해당 참조하는 자식 테이블의 값들은 NULL값으로 설정된다.

 

예제

-- mID를 PK로 갖는 DADDY 테이블 생성
CREATE TABLE daddy(
	idx	number(10),
	mID	number(10) PRIMARY key
);

-- idx를 PK로 갖고 mID를 FK로 갖는 KF_DA를 포함한 DAUGHTER 테이블 생성
-- 외래키로 갖은 부모테이블의 컬럼이 삭제되면 자식테이블의 값도 삭제되도록 설정
CREATE TABLE daughter(
	idx	number(10) PRIMARY KEY,
	mID NUMBER(10),
	CONSTRAINTS FK_DA FOREIGN KEY(mID)
	REFERENCES daddy(mID) ON DELETE CASCADE
);

-- DADDY 테이블에 컬럼 INSERT
INSERT INTO daddy VALUES (1, 10);
INSERT INTO daddy VALUES (2, 20);
INSERT INTO daddy VALUES (3, 30);

SELECT * FROM daddy;

-- DAUGHTER 테이블에 컬럼 INSEERT
INSERT INTO daughter VALUES (100, 10);
INSERT INTO daughter VALUES (101, 10);
INSERT INTO daughter VALUES (102, 20);

SELECT * FROM DAUGHTER d ;

-- DADDY 테이블에서 인덱스 2번 삭제
DELETE FROM daddy WHERE idx = 2;

-- 오류 : 부모테이블에서 mID를 찾을 수 없어 제약조건 오류
INSERT INTO daughter VALUES (103, 30);

-- idx를 PK로 갖고, DADDY 테이블의 mID를 FK로 갖는 DAUTHER2 테이블 생성 
-- ON DELETE SET NULL 방식으로 삭제
CREATE TABLE daughter2(
	idx	number(10) PRIMARY KEY,
	mID NUMBER(10),
	CONSTRAINTS FK_DA2 FOREIGN KEY(mID)
	REFERENCES daddy(mID) ON DELETE SET NULL 
);

SELECT * FROM daughter2;

-- DADDY 테이블 인덱스 1번 삭제
DELETE FROM daddy WHERE idx = 1;

-- DAUGHTER2 테이블에 컬럼 INSERT
INSERT INTO daughter2 VALUES (100, 10);
INSERT INTO daughter2 VALUES (101, 10);
INSERT INTO daughter2 VALUES (102, 30);
-- 오류 나는 것 없음
728x90