JAVA 풀스택 34일차 - Oracle DB 4

1. 제약 조건 (CONSTRAINT)

데이터 저장 규칙을 설정하는 기능

- 잘못된 데이터 저장 방지

1-1 제약조건 설정 방법

방법	설명
컬럼 레벨	컬럼 작성과 동시에 설정
테이블 레벨	컬럼 작성 후 따로 설정

1-2 컬럼 레벨 제약 조건

컬럼 선언과 함께 제약 조건 작성

CREATE TABLE USER_UNIQUE(
	NO NUMBER,
	ID VARCHAR2(20) UNIQUE,
	PW VARCHAR2(20),
	NAME VARCHAR2(20),
	GENDER CHAR(3),
);

1-3 테이블 레벨 제약 조건

컬럼 작성 후 따로 제약 조건 작성

CREATE TABLE USER_UNIQUE(
	NO NUMBER,
	ID VARCHAR2(20),
	PW VARCHAR2(20),
	NAME VARCHAR2(20),
	GENDER CHAR(3),
	UNIQUE(ID)
);

1-4 UNIQUE 여러 칼럼 설정

두 개 이상의 컬럼을 묶어서 UNIQUE 설정 가능

CREATE TABLE USER_UNIQUE(
	NO NUMBER,
	ID VARCHAR2(20),
	PW VARCHAR2(20),
	NAME VARCHAR2(20),
	GENDER CHAR(3),
	UNIQUE(NO, ID)
);

- NO와 ID 조합이 중복되면 안됨

예시

NO	ID	가능 여부
1	U1	가능
1	U2	가능
2	U1	가능
2	U1	불가능

1-5 제약 조건 이름 설정

제약 조건에 이름 부여 가능

CREATE TABLE CONS_NAME(
	D1 NUMBER CONSTRAINT NN_CONSNAME_D1 NOT NULL,
	D2 NUMBER CONSTRAINT U_CONSNAME_D2 UNIQUE,
	D3 NUMBER,
	CONSTRAINT U_CONSNAME_D3 UNIQUE(D3)
);

1-6 USER_CONSTRAINTS

제약 조건 조회

SELECT *
FROM USER_CONSTRAINTS;

1-7 CHECK 제약 조건

지정한 값만 저장 가능하도록 제한

CREATE TABLE USER_CHECK(
	NO NUMBER,
	ID VARCHAR2(20),
	PW VARCHAR2(20),
	NAME VARCHAR2(20),
	GENDER CHAR(3) CHECK(GENDER IN ('남', '여'))
);

1-8 하나의 컬럼에 여러 제약 조건 설정

CREATE TABLE TEST_CONS(
	DATA NUMBER NOT NULL UNIQUE,
	CONSTRAINT CK CHECK(DATA>0),
	CONSTRAINT CK2 CHECK (DATA<100)
);

2. PRIMARY KEY

기본키 제약 조건

- NOT NULL + UNIQUE

 

- 테이블 행을 구분하는 고유값

- 반드시 값 존재 필요

- 중복 불가능

- 테이블 당 1개만 가능

 

2-1 PRIMARY KEY 설정

CREATE TABLE USER_PK_TABLE(
	NO NUMBER PRIMARY KEY,
	ID VARCHAR2(30) NOT NULL UNIQUE,
	PW VARCHAR2(30) NOT NULL,
);

2-2 PRIMARY KEY 여러 컬럼 설정

CREATE TABLE USER_PK_TABLE(
	NO NUMBER,
	ID VARCHAR2(30) NOT NULL UNIQUE,
	PW VARCHAR2(30) NOT NULL,
	PRIMARY KEY(NO, ID)
);

PRIMARY KEY / UNIQUE 차이

제약 조건	특징
PRIMARY KEY	NOT NULL + UNIQUE
UNIQUE	중복만 금지

3. FOREIGN KEY (외래키)

다른 테이블의 값을 참조하는 제약조건

3-1 FOREIGN KEY 특징

- 참조 테이블에 존재하는 값만 저장 가능

- 다른 테이블과 연결 관계 생성

- 데이터 무결성 유지

3-2 FOREIGN KEY 생성 구조

3-2-1 컬럼 레벨

컬럼명 자료형
REFERENCES 부모테이블(부모컬럼)

CREATE TABLE USER_FOREIGN_KEY(
	NO NUMBER PRIMARY KEY,
	NAME VARCHAR2(20),
	GRADE_CODE NUMBER
	REFERENCES USER_GRADE(GRADE_CODE)
);

3-2-2 테이블 레벨

CONSTRAINT 제약조건명
FOREIGN KEY(현재컬럼)
REFERENCES 부모테이블(부모컬럼)

CREATE TABLE USER_FOREIGN_KEY(
	NO NUMBER PRIMARY KEY,
	ID VARCHAR2(20),
	PW VARCHAR2(20),
	GRADE_CODE NUMBER,
	CONSTRAINT FK_GRADECODE 
	FOREIGN KEY(GRADE_CODE) 
	REFERENCES USER_GRADE(GRADE_CODE)
);

3-2-3 예시

부모 테이블

CREATE TABLE USER_GRADE(
	GRADE_CODE NUMBER PRIMARY KEY,
	GRADE_NAME VARCHAR2(20)
);

자식 테이블

CREATE TABLE USER_FOREIGN_KEY(
	NO NUMBER PRIMARY KEY,
	GRADE_CODE NUMBER,
	CONSTRAINT FK_GRADECODE FOREIGN KEY(GRADE_CODE) REFERENCES USER_GRADE(GRADE_CODE)
);

4. 삭제 옵션

부모 데이터 삭제 시 자식 처리 방법 설정

4-1 ON DELETE SET NULL

CREATE TABLE USER_FOREIGN_KEY(
	NO NUMBER PRIMARY KEY,
	ID VARCHAR2(20),
	PW VARCHAR2(20),
	GRADE_CODE NUMBER,
	CONSTRAINT FOREIGN KEY(GRADE_CODE) 
	REFERENCES USER_GRADE(GRADE_CODE) ON DELETE SET NULL
);

- 부모 삭제 시 참조 중인 자식 값은 NULL로 변경

4-2 ON DELETE CASCADE

부모 삭제 시 자식도 함께 삭제

CREATE TABLE USER_FOREIGN_KEY(
	NO NUMBER PRIMARY KEY,
	ID VARCHAR2(20),
	PW VARCHAR2(20),
	GRADE_CODE NUMBER,
	CONSTRAINT FOREIGN KEY(GRADE_CODE) 
	REFERENCES USER_GRADE(GRADE_CODE) ON DELETE CASCADE
);

- 부모 삭제 시 관련 자식 행도 같이 삭제

5. 서브쿼리로 테이블 생성

CREATE TABLE EMP_COPY
AS SELECT * FROM EMPLOYEE;

- 컬럼명, 자료형, 데이터, 제약 조건 복사

6. 구조만 복사

CREATE TABLE EMP_COPY2
AS SELECT * FROM EMPLOYEE
WHERE 1=2;

- 조건이 거짓이라 데이터는 복사되지 않고 구조만 복사됨

7. 특정 컬럼만 복사

CREATE TABLE EMP_COPY3
AS SELECT EMP_ID,
	   EMP_NAME "NAME",
	   SALARY "급여"
FROM EMPLOYEE;

8. DEFALUT

기본값 설정

CREATE TABLE DEFAULT_TABLE(
	COL1 VARCHAR2(30) DEFAULT '빈 값',
	COL2 DATE DEFAULT SYSDATE
);

INSERT INTO DEFAULT_TABLE VALUES(DEFAULT, DEFAULT);

- DEFAULT로 지정된 값이 저장됨

9. ALTER TABLE

테이블 수정 명령어

ALTER TABLE 테이블명
수정내용;

9-1 제약 조건 추가

9-1-1 PRIMARY KEY 추가

ALTER TABLE EMPLOYEE
ADD PRIMARY KEY(EMP_ID);

9-1-2 FOREIGN KEY 추가

ALTER TABLE EMPLOYEE
ADD FOREIGN KEY(DEPT_CODE)
REFERENCES DEPARTMENT;

9-1-3 UNIQUE 추가

ALTER TABLE EMPLOYEE
ADD UNIQUE(EMP_NO);

9-1-4 CHECK 추가

ALTER TABLE EMPLOYEE
ADD CHECK(SALARY > 0);

9-1-5 NOT NULL 추가

ALTER TABLE EMPLOYEE
MODIFY EMP_NAME NOT NULL;

10. 제약조건 조회

SELECT *
FROM USER_CONSTRAINTS;

11. USER_CONS_COLUMNS

제약조건 칼럼 조회

SELECT *
FROM USER_CONS_COLUMNS;

12. 제약조건 삭제

ALTER TABLE 테이블명
DROP CONSTRAINT 제약조건명;

13.  DML(Data Manipulation Language)

데이터 조작 언어

명령어	의미
INSERT	추가
UPDATE	수정
DELETE	삭제
SELECT	조회

13-1 INSERT

새로운 행 추가

13-1-1 특정 칼럼만 INSERT

INSERT INTO EMPLOYEE(
	EMP_ID,
	EMP_NAME
)
VALUES(
	500,
	'레몬팡'
);

13-1-2 전체 칼럼 INSERT

INSERT INTO EMPLOYEE
VALUES(
	600,
	'애플',
	...
);

13-2 COMMIT 

DB 저장 반영

COMMIT;

- 지금까지의 변경사항 저장

13-3 INSERT + SUBQUERY

조회 결과를 INSERT 가능

INSERT INTO EMP_01
(
	SELECT EMP_ID,
		   EMP_NAME,
		   DEPT_TITLE
	FROM EMPLOYEE
	LEFT JOIN DEPARTMENT
	ON(DEPT_CODE = DEPT_ID)
);