[SQL] 9. DDL

# DDL

CREATE TABLE table_name (column_name ... ) ;
//구조만 만든다.
CREATE TABLE table_name AS subquery ;
//구조를 만들고 데이터 초기화

 

# 제약조건

데이터 무결성: 데이터베이스에 저장되어 있는 데이터가 손상되거나 원래의 의미를 잃지 않고 유지하는 상태

데이터 무결성 제약조건: 데이터의 무결성을 보장하기 위해 오라클에서 지원하는 방법

( NOT NULL, UNIQUE, PRIMARY KEY, REFERENCES, CHECK )

---

# NOT NULL

//NOT NULL
//컬럼 레벨의 제약조건
CREATE TABLE TEST_TBL1(
	ID 	VARCHAR(20) NOT NULL , 
	PWD VARCHAR(20)
);

//이것도 가능
CREATE TABLE TEST_TBL1(
	ID 	VARCHAR(20) CONSTRAINT TEST_TBL_NN NOT NULL , 
	PWD VARCHAR(20)
);

//테이블 레벨의 제약조건
//그러나 NOT NULL은 테이블 레벨로 불가
CREATE TABLE TEST_TBL2(
	ID 	VARCHAR(20) , 
	PWD VARCHAR(20) ,
	NOT NULL (ID)
);

//확인
SELECT	*	
FROM	TEST_TBL1;

//DROP
DROP TABLE TEST_TBL;

 

- DML: INSERT, UPDATE, DELETE

INSERT INTO TABLE_NAME( [COLUMN, COLUMN] ) VALUES(VALUE, VALUE) ;
INSERT INTO TABLE_NAME AS SUBQUERY ; 

INSERT INTO TEST_TBL1 VALUES('SIJUNE', 'SIJUNE');
//데이터 삽입 성공
INSERT INTO TEST_TBL1(PWD) VALUES('SIJUNE');
//오류, NOT NULL 때문에

SELECT	*	
FROM	TEST_TBL1;

---

# PRIMARY KEY : NOT NULL + UNIQUE

CREATE TABLE TEST_DEPT(
	DEPT_ID 	NUMBER PRIMARY KEY,
	DEPT_NAME	VARCHAR2(20) NOT NULL
);

// 1. 컬럼 레벨의 외래키 칼럼
CREATE TABLE TEST_EMP(
	EMP_ID		NUMBER 	PRIMARY KEY,
	SALARY		NUMBER,
	DEPT_ID		NUMBER 	REFERENCES TEST_DEPT(DEPT_ID)
);

// 2. 테이블 레벨의 외래키 칼럼
CREATE TABLE TEST_EMP(
	EMP_ID		NUMBER 	PRIMARY KEY,
	SALARY		NUMBER,
	DEPT_ID		NUMBER,
	FOREIGN KEY (DEPT_ID) REFERENCES TEST_DEPT(DEPT_ID)
);

DROP TABLE TEST_DEPT
//오류, 참조관계가 있기 때문에

//부모를 삭제시킬 수 있다.
DROP TABLE TEST_DEPT CASCADE CONSTRAINT;

SELECT * FROM TEST_DEPT; //부모는 삭제
SELECT * FROM TEST_EMP; //자식은 그대로

//COMPOSIT PRIMARY KEY, REFERENCES
CREATE TABLE TEST_ORDERS(
	ORDERNO		VARCHAR2(50) PRIMARY KEY,
	ADDRESS		VARCHAR2(50),
	STATUS		VARCHAR2(50) CHECK (STATUS IN('상품준비중', '배송중', '배송완료'))
);
CREATE TABLE TEST_PRODUCTS(
	PNO		VARCHAR2(50) PRIMARY KEY,
	PNAME	VARCHAR2(50) NOT NULL,
	COST 	NUMBER	CHECK (COST > 100 )
);

//복합키가 기본키
//1. 컬럼 레벨
CREATE TABLE TEST_ORDERDETAIL(
	ORDERNO		VARCHAR2(50) REFERENCES TEST_ORDERS (ORDERNO),
	PNO			VARCHAR2(50) REFERENCES TEST_PRODUCTS (PNO),
	QTY			NUMBER,
	PRIMARY KEY(ORDERNO, PNO) 
);
//지우고 다시 생성
DROP TABLE TEST_ORDERDETAIL;

//2. 테이블 레벨
CREATE TABLE TEST_ORDERDETAIL(
	ORDERNO		VARCHAR2(50),
	PNO			VARCHAR2(50),
	QTY			NUMBER,
	PRIMARY KEY(ORDERNO, PNO),
	FOREIGN KEY (ORDERNO) REFERENCES TEST_ORDERS(ORDERNO),
	FOREIGN KEY (PNO) REFERENCES TEST_PRODUCTS(PNO)
);

//복합키가 외래키
CREATE TABLE TEST_ODD(
	NUM			NUMBER	PRIMARY KEY,
	ORDERNO		VARCHAR2(50), 
	PNO			VARCHAR2(50),
	FOREIGN KEY (ORDERNO, PNO) REFERENCES TEST_ORDERDETAIL(ORDERNO, PNO)
);

• ON DELETE SET NULL: 부모 레코드가 삭제될 때, 자식 레코드를 NULL로 만들겠다.
• ON DELETE CASCADE: 부모 레코드가 삭제될 때, 자식 레코드도 같이 삭제하겠다.

---

# CHECK 연산

데이터 입력시 체크

---

# 총정리

//총정리
CREATE TABLE CONSTRAINT_EMP(
	EID			CHAR(3) PRIMARY KEY,
	ENAME		VARCHAR(20) NOT NULL,
	ENO			CHAR(14)	NOT NULL UNIQUE,
	EMAIL		VARCHAR2(25) UNIQUE,
	PHONE		VARCHAR2(12),
	HIRE_DATE	DATE	DEFAULT	SYSDATE,
	JID			CHAR(2)	REFERENCES JOB ON DELETE SET NULL,
	SALARY		NUMBER,
	BONUS_PCT	NUMBER,
	MARRIAGE 	CHAR(1) DEFAULT 'N' CHECK (MARRIAGE IN ('Y', 'N')),
	MID			CHAR(3)	REFERENCES CONSTRAINT_EMP ON DELETE	SET NULL,
	DID			CHAR(2),
	FOREIGN KEY (DID) REFERENCES DEPARTMENT ON DELETE CASCADE
);