DDL 테이블

vencott·2021년 5월 19일
0

sinc 인턴교육

목록 보기
6/18

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 인턴십

profile
Backend Developer

0개의 댓글