CREATE - 테이블 생성
테이블 생성
- CREATE TABLE table_name(
- column_name datatype [DEFAULT expr][column_constraint] [, ...]
테이블 컬럼 이름은 문자로 시작, 30자 이하
제약조건
데이터 무결성: 데이터베이스에 저장되어 있는 데이터가 손상되거나 원래의 의미를 잃지 않고 유지하는 상태
데이터 무결성 제약조건: 데이터 무결성을 보장하기 위해 오라클에서 지원하는 방법
- UNIQUE
- 해당 컬럼 또는 컬럼 조합 값이 유일하도록 함
- PRIMARY KEY
- 해당 행을 유일하게 식별할 수 있도록 함
- REFERENCES
- 해당 컬림이 참조하고 있는 테이블(부모 테이블)의 특정 컬럼 값들과 일치하거나 또는 NULL이 되도록 보장함
- CHECK
- 해당 컬럼에 특정 조건을 항상 만족시키도록 함
NOT NULL + UNIQUE = Primary key
REFERENCES = Foreign key
NOT NULL
NULL이 되지 않게
// NOT NULL
-- 컬럼 레벨의 제약조건CREATE TABLE TEST_TBL(
ID VARCHAR2(20) CONSTRAINT TEST_TBL_NN NOT NULL,
PWD VARCHAR2(20)
);
-- 테이블 레벨의 제약조건CREATE TABLE TEST_TBL(
ID VARCHAR2(20),
PWD VARCHAR2(20),
CONSTRAINT TEST_TBL_NN NOT NULL (ID)-- NOT NULL은 테이블 레벨의 제약 불가
);만든 테이블에 데이터를 넣으려면? --> 데이터 조작어(DML)
DML(INSERT, UPDATE, DELETE)
- INSERT
- 데이터 삽입
- INSERT INTO table_name ( [column1, column2, ...] ) VALUES(value1, value2);
- INSERT INTO table_name AS SUBQUERY;
- 테이블의 구조를 만들고 SUBQEURY의 결과로 바로 데이터를 초기화
- 데이터 삽입
- UPDATE
- 데이터 수정
- DELETE
- 데이터 삭제
// INSERT INTO
-- 모든 컬럼에 INSERTINSERT INTO TEST_TBL VALUES('JSLIM', 'JSLIM');
-- NOT NULL 제약조건이 있으므로 오류INSERT INTO TEST_TBL(PWD) VALUES('JSLIM');
-- ORA-01400: cannot insert NULL into ("HR"."TEST_TBL"."ID") (0 rows affected)UNIQUE
중복을 허용하지 않는다
PRIMARY KEY
UNIQUE + NOT NULL 의미
테이블 당 1개만 생성 가능
// PRIMARY KEY
CREATE TABLE TEST_DEPT(
DEPT_ID NUMBER PRIMARY KEY,
DEPT_NAME VARCHAR2(20) NOT NULL
);REFERENCES
외래 키를 명시
// REFERENCES
-- 컬럼 레벨의 제약조건CREATE TABLE TEST_EMP(
EMP_ID NUMBER PRIMARY KEY,
SALARY NUMBER,
DEPT_ID NUMBER REFERENCES TEST_DEPT(DEPT_ID)
);
-- 테이블 레벨의 제약조건CREATE TABLE TEST_EMP(
EMP_ID NUMBER PRIMARY KEY,
SALARY NUMBER,
DEPT_ID NUMBER,
FOREIGN KEY (DEPT_ID) REFERENCES TEST_DEPT(DEPT_ID)
);부모 테이블의 삭제
자식이 참조하고 있는 부모 테이블은 삭제할 수 없다
CASCADE CONSTRAINT 조건을 통해 삭제 가능
자식이 참조하고 있는 부모 테이블을 삭제하면 자식 테이블에선 관계만 끊어진다
// 부모 테이블 DROP
-- DROP 불가DROP TABLE TEST_DEPT;
-- DROP 가능DROP TABLE TEST_DEPT CASCADE CONSTRAINT;CHECK
특정 조건 명세
// CHECK
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)
);PRIMARY KEY
PRIMARY KEY는 두 번 쓸 수 없다
Column 레벨이 아니라 Table 레벨에서 COMPOSIT 사용
전이된 외래 키가 기본키로 사용될 때
// Composit Primary key
-- Column 레벨CREATE TABLE TEST_ORDERDETAIL(
ORDERNO VARCHAR2(50) REFERENCES TEST_ORDERS(ORDERNO),
PNO VARCHAR2(50) REFERENCES TEST_PRODUCTS(PNO),
QTY NUMBER,
PRIMARY KEY(ORDERNO, PNO)
);
-- Table 레벨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)
);COMPOSIT FOREIGN KEY
COMPOSIT을 통해 만들어진 기본키를 자식 테이블에서 외래 키로 사용할 때, 절대로 Column 레벨에서 따로 제약조건을 걸면 안 되고 Table 레벨에서 한꺼번에 해줘야 함(COMPOSIT을 통해 하나가 된 키이기 때문에)
// Composit 외래키
-- COMPOSIT된 외래키는 Column 레벨에서 따로 제약하면 오류CREATE TABLE TEST_ODD(
NUM NUMBER PRIMARY KEY,
ORDERNO VARCHAR2(20) REFERENCES TEST_ORDERDETAIL(ORDERNO),
PNO VARCHAR2(20) REFERENCES TEST_ORDERDETAIL(ORDERNO)
);
-- Table 레벨에서 제약CREATE TABLE TEST_ODD(
NUM NUMBER PRIMARY KEY,
ORDERNO VARCHAR2(20),
PNO VARCHAR2(20),
FOREIGN KEY(ORDERNO, PNO) REFERENCES TEST_ORDERDETAIL(ORDERNO, PNO)
);FOREIGN KEY: DELETION OPTION
- ON DELETE SET NULL
- 참조 컬럼 값이 삭제될 때, FOREIGN KEY 컬럼 값을 NULL로 변경
- ON DELETE CASCADE
- 참조 컬럼 값이 삭제될 때, FOREIGN KEY 컬럼 값도 함께 삭제
제약조건 예제
// 제약조건 전체
CREATE TABLE CONSTRAINT_EMP(
EID CHAR(3) CONSTRAINT PKEID PRIMARY KEY,
ENAME VARCHAR2(2) CONSTRAINT NENAME NOT NULL,
ENO CHAR(2) CONSTRAINT NENO NOT NULL CONSTRAINT UENO UNIQUE,
EMAIL VARCHAR2(2) CONSTRAINT UEMAIL UNIQUE,
PHONE VARCHAR2(12),
HIRE_DATE DATE DEFAULT SYSDATE,
JID CHAR(2) CONSTRAINT FKJID REFERENCES JOB ON DELETE SET NULL,
SALARY NUMBER,
BONUS_PCT NUMBER,
MARRIAGE CHAR(1) DEFAULT 'N' CONSTRAINT CHK CHECK(MARRIAGE IN('Y', 'N')),
MID CHAR(3) CONSTRAINT FKMID REFERENCES CONSTRAINT_EMP ON DELETE SET NULL,
DID CHAR(2),
CONSTRAINT FKDID FOREIGN KEY (DID) REFERENCES DEPARTMENT ON DELETE CASCADE
);SUBQUERY
CREATE TABLE table_name [(column_name [DEFAULT expr][, ...])]
AS subquery;
테이블을 생성하고, 서브 쿼리 실행 결과가 자동으로 입력됨
제약조건 서브 쿼리에서 사용한 대상 컬럼들의 NOT NULL 조건들은 반영되나
나머지 제약조건들은 반영 안 되므로 테이블 생성 시점에 테이블 생성 구문에서 제약해준다
단, 외래 키 컬럼은 생성되는 시점에서 제약할 수 없다
또는 ALTER를 통해 테이블 수정하며 제약조건
서브 쿼리를 통한 테이블 생성은 물리적인 공간을 차지하므로 현업에서 잘 사용 X
- -> 물리적인 공간을 차지하지 않는 뷰를 사용
별칭은 테이블 생성 구문에서 만들거나 서브 쿼리 SELECT 절에서 정의
// SUBQUERY
-- 테이블 생성 구문에서 별칭 지정CREATE TABLE TABLE_SUBQUERY (EID, ENAME, SALARY, DNAME, JTITLE)
AS SELECT EMP_ID
, EMP_NAME
, SALARY
, DEPT_NAME
, JOB_TITLE
FROM EMPLOYEE
LEFT JOIN DEPARTMENT USING (DEPT_ID)
LEFT JOIN JOB USING (JOB_ID);
-- 테이블 생성 시점에 제약조건 추가CREATE TABLE TABLE_SUBQUERY
( EID PRIMARY KEY,
ENAME,
SALARY CHECK(SALARY > 200000),
DNAME,
JTITLE NOT NULL )
AS SELECT EMP_ID
, EMP_NAME
, SALARY
, DEPT_NAME
, JOB_TITLE
FROM EMPLOYEE
LEFT JOIN DEPARTMENT USING (DEPT_ID)
LEFT JOIN JOB USING (JOB_ID);ALTER - 테이블 수정
컬럼 / 제약조건 / 테이블 이름을 수정
- ALTER TABLE table_name
- ADD
- MODIFY
- DROP
- RENAME
- RENAME old_table_name TO new_table_name
- ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name
- 테이블 레벨의 제약조건 생성과 문법이 동일하다(REFERENCES만
테이블 레벨의 제약조건 생성과 문법이 동일하다
- ex) reference만 쓰는 게 아니라 FOREIGN KEY까지 써준다
외부에서 MODIFY를 통해 제약조건을 추가하는 것은 테이블 레벨로 취급
NOT NULL은 테이블 레벨에서 제약 불가이므로 ADD가 아니라 MODIFY 사용
// NOT NULL 제약조건 추가
CREATE TABLE EMP3 AS SELECT * FROM EMPLOYEE;
ALTER TABLE EMP3
ADD PRIMARY KEY(EMP_ID)
ADD UNIQUE (EMP_NO)
MODIFY HIRE_DATE NOT NULL;DROP - 테이블 삭제
DROP TABLE table_name [CASCADE CONSTRAINTS];
출처: SHINSEGAE I&C 인턴십
