이론/자바 풀스택 국비수업
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)
);

-- 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
-- 오류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('남성', '여성'))
);

-- 정상 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);



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


외래키 삭제
- 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