[DB] DDL : CREATE, 제약조건 CHECK

목차

1. CREATE  

2. 제약조건(CONSTAINT)

1) PRIMARY KEY, NOT NULL, UNIQUE, FOREIGN KEY, CHECK

4. DELETE

 

 데이터 딕셔너리란? 
: 자원을 효율적으로 관리하기 위한 다양한 정보를 저장하는 시스템 테이블
데이터 딕셔너리는 사용자가 테이블을 생성하거나 사용자를 변경하는 등의
작업을 할 때 데이터베이스 서버에 의해 자동으로 갱신되는 테이블

참고) User_tables : 자신의 계정이 소유한 객체 등에 관한 정보를 조회 할 수 있는 딕셔너리 뷰
SELECT * FROM USER_TABLES; 으로 결과를 볼 수 있다. 

✍DDL(Data Definition Language)

:  데이터 정의 언어

객체(OBJECT)를 만들고(CREATE), 수정하고(ALTER), 삭제(DROP) 등
데이터의 전체 구조를 정의하는 언어로 주로 DB관리자, 설계자가 사용함

 

오라클에서의 객체 종류

• 테이블(TABLE), 뷰(VIEW), 시퀀스(SEQUENCE), 인덱스(INDEX), 패키지(PACKAGE), 프로시저(PROCEDUAL), 함수(FUNCTION), 트리거(TRIGGER), 동의어(SYNONYM), 사용자(USER)

---

🚩 CREATE

: 테이블이나 인덱스, 뷰 등 다양한 데이터베이스 객체를 생성하는 구문 ⭐   

테이블로 생성된 객체는 DROP 구문을 통해 제거 할 수 있다.    

➡⭐ 데이터 타입, 제약조건 아주 중요!!!

표현식

CREATE TABLE 테이블명(컬럼명 자료형(크기), 컬럼명 자료형(크기), …);

[ 자료형 ] 

1) NUMBER : 숫자형(정수, 실수)

NUMBER[ ( P [ , S ] ) ]
* P : 표현할 수 있는 전체 숫자 자리 수 (1 ~ 38) : 전체 자리 수, 
* S : 소수점 이하 자리 수 (-84 ~ 127) 소수점 이하 자리 수 

➡ NUMBER(7, 1)
➡ NUMBER(7, 3) : 소수점은 3칸 . 한 칸 그러면 정수는 3칸밖에 없는데 실제 값은 5칸이 필요하니 오류가 발생한다.

자바와 같이 사용할 때는 NUMBER를 가장 많이 사용한다. 

 

2) CHAR / VARCHAR  / NVARCHAR

  CHAR(크기) : 고정길이 문자형 (2000BYTE) 

   VARCHAR2(크기) : 가변길이 문자형 (4000 BYTE)

   NVARCHAR2(크기) : 가변길이 문자 개수 자료형

1) CHAR(크기) : 고정길이 문자형 (2000BYTE) 
고정된 길이의 문자열 저장 시 좋다.
ex) 주민등록번호 

참고) 자바에서의 CHAR는 문자 1개 , DB와는 다른 의미이니 주의!

예시) CHAR(10) 컬럼에 'ABC' 3byte 문자열만 저장해도 10byte저장공간을 모두 사용. 
➡ASCII코드는 1byte
== 'ABC' 는 3byte 이지만 빈칸을 고정해서 유지한다.

2) VARCHAR2(크기) : 가변길이 문자형 (4000 BYTE)
고정되지 않은 문자열 저장 시 좋다. 
ex) 아이디, 비밀번호같이 길이가 다를 때 사용

예시) VARCHAR2(10) 컬럼에 'ABC' 3BYTE 문자열만 저장하면 나머지 7byte를 반환함.
➡ CHAR와 다르게 저장된 문자열만큼만 사용한다. == 용량을 아낄 수 있다.

단점 : 속도가 느리다 
ex) 아이디, 비밀번호같이 길이가 다를 때 사용

+ CHAR, VARCHAR2는 문자의 바이트 크기를 기준으로 저장하기 때문에
-> 영어, 숫자, 기본 특수문자(!@#$% 등 키보드에 있는 특수문자) == 1byte 
        나머지 문자(한글, 한자 등) == 3byte 
    ex) 
    ABC ==3
    가나다 == 9
    안녕1 == 7


3) NVARCHAR2(크기) : 가변길이 문자 개수 자료형
 길이는 문자의 개수, 최대 저장 용량은  4000BYTE

DATE  : 날짜 타입

TIMESTAMP : DATE + 밀리세컨드 단위까지 추가   
BLOB : 대용량 이진 데이터 (4GB) - 파일 저장용(이미지, 동영상, 음악 등등)
CLOB : 대용량 문자 데이터 (4GB) - 문자 저장용 (책 등)

 

---

1. 테이블 생성하기

 

⭐ 테이블 

: 행(row)과 열(column)으로 구성되는 가장 기본적인 데이터베이스 객체
데이터 베이스 내에서 모든 데이터는 테이블을 통해서 저장된다.

표현식

CREATE TABLE 테이블명 (
        컬럼명 자료형(크기), 
        컬럼명 자료형(크기),
        ...);

➡ 크기: 문자는 몇글자까지 들어오게할건지 지정

예제1. id, password, name, 가입일/ MEMBER 테이블 생성

CREATE TABLE MEMBER(
    MEMBER_ID VARCHAR2(30),
    MEMBER_PWD VARCHAR2(30),
    MEMBER_NAME VARCHAR2(30),
    ENROLL_DATE DATE DEFAULT SYSDATE
);

➡ 컬럼명, 자료형(가변길이 1.5~2배 여유롭게 설정)

➡  VARCHAR2(30) : 가변길이 문자열 30byte

한국 사이트의 경우 이름이 고정적이니 NVARCHAR2 또는 VARCHAR2 다 사용 가능하다. 

👉 DEFAULT

: 컬럼 기본 값 
INSERT, UPDATE 시 해당 컬럼에 아무 값도 입력되지 않거나, DEFAULT로 작성된 경우 DEFAULT 오른쪽에 작성된 값을 삽입한다.

 

2.  컬럼에 주석 달기

표현식

COMMENT ON COLUMN 테이블명.컬럼명 IS '주석내용';

예제1.
COMMENT ON COLUMN MEMBER.MEMBER_ID IS '회원 아이디';
COMMENT ON COLUMN MEMBER.MEMBER_PWD IS '회원 비밀번호';
COMMENT ON COLUMN MEMBER.MEMBER_NAME IS '회원 이름';
COMMENT ON COLUMN MEMBER.ENROLL_DATE IS '회원 가입일';

 

🔎 USER_TABLES : 사용자가 작성한 테이블을 확인 하는 뷰

     ex) SELECT * FROM USER_TABLES;

🔎 DESC문 : 테이블의 구조를 표시

     ex) DESC 테이블명;

---

⭐  제약 조건(CONSTRAINTS)  ⭐

사용자가 원하는 조건의 데이터만 유지하기 위해서 특정 컬럼에 설정하는 제약.
데이터 무결성 보장을 목적으로 한다.

👉데이터 무결성
: 저장된 데이터의 정확성과 일관성을 보증하는 것== 중복 데이터 X, NULL X

 

참고)

제약 : 강제적인 약속 (무조건)
규약 : 규칙에 대한 약속 (강제성이 없기 때문에 무조건 지켜지지 않을 수도 있다)

 

[ 특징 ]

• 입력 데이터에 문제가 없는지 자동으로 검사하는 목적
• 데이터의 수정/삭제 가능여부 검사등을 목적으로 함  (참조 무결성) --> 제약조건을 위배하는 DML 구문은 수행할 수 없음!
  

[ 제약 조건 확인 ]

1) USER_CONSTRAINTS : 사용자가 작성한 제약조건을 확인 하는 딕셔너리 뷰 

참고) 어떤 제약조건을 위배했는지 알 수 있다. 

2) USER_CONS_COLUMNS : 제약조건이 걸려 있는 컬럼을 확인 하는 딕셔너리 뷰 

 

🔎 컬럼 레벨 제약 조건:  테이블 생성 시 컬럼 옆에 작성하는 방법

🔎 테이블 레벨 제약 조건: 테이블 작성 끝나는 부분( 닫힘 괄호) 바로 위쪽에 작성

 

⭐ 제약조건 종류 (5가지) ⭐

PRIMARY KEY, NOT NULL, UNIQUE, CHECK, FOREIGN KEY

---

🚩 NOT NULL 

해당 컬럼에 반드시 값이 기록되어야 하는 경우 사용한다.  == NULL이 들어올 수 없다
삽입/수정시 NULL값을 허용하지 않도록 컬럼레벨에서 제한

표현식

CREATE TABLE  테이블명 (
컬럼명 자료형(크기) CONSTRAINTS 제약 조건명 NOT NULL,
컬럼명 자료형(크기) CONSTRAINTS 제약 조건명 NOT NULL
);

➡ 제약 조건명 미지정 시 결과 : SYS_XXXXXX
=> 되도록이면 지정해주는게 좋다

ex) 데이터에 NULL 추가 시 에러❗
INSERT INTO 테이블명 VALUES(NULL , '데이터');

➡ NOT NULL 제약 조건에 위배되어 오류 발생

➡  해당 테이블 제약조건에서 컬럼명의 제약조건을 확인 할 수 있다. 

ex) "컬럼명" IS NOT NULL 

---

🚩 UNIQUE 

컬럼에 입력 값에 대해서 중복 제한하는 제약조건 == 유일하다
컬럼레벨에서 설정 가능, 테이블 레벨에서 설정 가능
단, UNIQUE 제약 조건이 설정된 컬럼에 NULL 값은 중복 삽입 가능.

표현식

컬럼 레벨
컬럼명 자료형(크기) UNIQUE ➡ 제약 조건명 X
컬럼명 자료형(크기) CONSTRAINT 제약 조건명 UNIQUE  ➡ 제약 조건명 O

테이블 레벨 
UNIQUE(컬럼명) ➡  제약 조건명 X
CONSTRAINT 제약 조건명 UNIQUE(컬럼명) ➡ 제약 조건명 O

➡  데이터 INSERT 시 중복 값이 있는 경우 UNIQUE 제약 조건에 의해 행이 삽입되지 않는다.

➡ UNIQUE 제약조건은 NULL 값 중복 가능 → 테이블 생성 시 컬럼 레벨에 NOT NULL 지정하면 해결된다.

 

1) UNIQUE 복합키

두 개 이상의 컬럼을 묶어 하나의 UNIQUE 제약조건을 설정한다.
복합키 설정은 테이블 레벨에서만 가능하다!

테이블 레벨
UNIQUE (컬럼명 1, 컬럼명 2)

➡  여러 컬럼을 묶어서 UNIQUE 제약 조건이 설정되어 있으면 두 컬럼이 모두 중복되는 값일 경우에만 오류 발생

---

🚩 PRIMARY KEY(기본키) 

테이블에서 한 행의 정보를 찾기위해 사용할 컬럼을 의미
테이블에 대한 식별자(IDENTIFIER) 역할을 함

⭐ 한 테이블당 한 개만 설정할 수 있다.
컬럼레벨, 테이블레벨 둘 다 설정 가능
한 개 컬럼에 설정할 수도 있고, 여러개의 컬럼을 묶어서 설정할 수 있다.(== 복합키)

PK == 기본키 == 주식별자

👉 식별자
: NOT NULL(NULL X) + UNIQUE 제약조건(중복 X) 

EX) ID, 닉네임

표현식

컬럼 레벨
컬럼명 자료형(크기) PRIMARY KEY ➡ 제약 조건명 X
컬럼명 자료형(크기) CONSTRAINT 제약 조건명 PRIMARY KEY  ➡  제약 조건명 O

테이블 레벨 
PRIMARY KEY(컬럼명) ➡ 제약 조건명 X
CONSTRAINT 제약 조건명 PRIMARY KEY(컬럼명) ➡ 제약 조건명 O

➡  데이터 INSERT 시 기본키가 중복되면 유일성 위배,  NULL이어도 오류가 난다. 

 

1) PRIMARY KEY 복합키

복합키 설정은 테이블 레벨에서만 가능

테이블 레벨 
CONSTRAINT 제약 조건명 PRIMARY KEY(컬럼명1, 컬럼명2) ➡ 제약 조건명 O

---

🚩 FOREIGN KEY(외부키 / 외래키)

참조(REFERENCES)된 다른 테이블의 컬럼이 제공하는 값만 사용할 수 있다.
FOREIGN KEY제약조건에 의해서 테이블간의 관계(RELATIONSHIP)가 형성된다.
제공되는 값 외에는 NULL을 사용 가능

다른테이블에 있는 특정컬럼을 참조한다.

관계 == JOIN의 기준 컬럼

 

1) 컬럼 레벨일 경우

컬럼명 자료형(크기) [CONSTRAINT 제약조건명] REFERENCES 참조할 테이블명 [(참조할 컬럼)] [삭제룰]

2) 테이블 레벨일 경우

[CONSTRAINT 제약조건명] FOREIGN KEY (적용할 컬럼명) REFERENCES 참조할 테이블명 [(참조할 컬럼)] [삭제룰]

👉 참조할 컬럼 == 참조될 수 있는 컬럼
: PRIMARY KEY컬럼과, UNIQUE 지정된 컬럼만 외래키로 사용 가능
참조할 테이블의 참조할 컬럼명이 생략이 되면, PRIMARY KEY로 설정된 컬럼이 자동 참조할 컬럼이 된다.

 

• 참조당하는 테이블인 부모 테이블에 없는 값을 INSERT 시 외래키 제약 조건에 위배

 

1. FOREIGN KEY 삭제 옵션 

부모 테이블의 데이터 삭제 시 자식 테이블의 데이터를 
어떤식으로 처리할 지에 대한 내용을 설정할 수 있다.

• ON DELETE SET NULL: 없어지면 NULL로 값을 바꾸는 NULL변경 옵션
• ON DELETE CASCADE : 부모가 사라지면 그 값을 참조하는 행 삭제
• ON DELETE RESTRICTED : 삭제 불가 (기본 값)

*참조할 테이블명== 부모 테이블명 

 참조될 수 있는 컬럼은 PRIMARY KEY컬럼과, UNIQUE 지정된 컬럼만 외래키로 사용할 수 있음
--참조할 테이블의 참조

➡ 참조할 컬럼명을 따로 안 쓰면 부모 테이블의 PK 기본키를 참조한다.

 

1) ON DELETE RESTRICTED

삭제 제한, 기본으로 지정되어 있어 별도로 작성하지 않아도 된다.
FOREIGN KEY로 지정된 컬럼에서 사용되고 있는 값일 경우 제공하는 컬럼의 값은 삭제하지 못한다.
== 외래키로 참조되지 않는 컬럼의 값은 삭제가 가능하다 

부모 테이블의 컬럼 값을 삭제하려고 할 때 자식 테이블이 해당 값을 기록하고 있어서 삭제 불가

 

2) ON DELETE SET NULL

부모키 삭제 시 자식키를 NULL로 변경하는 옵션으로
테이블을 만들때 삭제 옵션을 추가해서 만든다.

자식 테이블 생성 시 테이블 레벨에 
적용할 컬럼명 자료형 REFERENCES 부모 테이블명 (참조할 컬럼) ON DELETE SET NULL

➡ 참조할 테이블(부모 테이블) 에서 삭제가 되면 해당 컬럼값을 NULL로 바꾼다.

 

3) ON DELETE CASCADE

부모 컬럼을 삭제하면
해당 값을 사용하던  자식 테이블의 행도 삭제 된다.

자식 테이블 생성시 테이블 레벨에 

적용할 컬럼명 자료형,
REFERENCES 부모 테이블명 (참조할 컬럼) ON DELETE CASCADE

 

ex)

회원 탈퇴 시 해당 회원이 작성한 글을 같이 지울 것이다 - ON DELETE CASCADE

회원은 탈퇴해도 글은 남겨두겠다면 - ON DELETE SET NULL

 

---

RDBMS(관계형 데이터베이스)의 데이터 무결성을 위한 제약조건들

NOT NULL : 컬럼 값 NULL X

UNIQUE : 같은 컬럼의 값 중 중복 X

PRIMARY KEY : NOT NULL + UNIQUE (테이블에서 각 행 구분 역할 == 식별자)

FOREIGN KEY : 다른 테이블의 컬럼 값 만을 참조하여 사용 (관계 형성하고,  JOIN에 활용할 수 있다.)

 

CHECK은 확인하는 제약조건으로 RDBMS의 데이터 무결성과 관련 없다. 

---

🚩 CHECK

컬럼에 기록되는 값에 조건 설정을 할 수 있다.

주의❗ 비교값은 리터럴만 사용할 수 있고, 변하는 값이나 함수는 사용하지 못한다. 

표현식

CHECK (컬럼명 비교연산자 비교값)

 

예제) A, B를 확인

컬럼 레벨
컬럼명 자료형(크기)  CHECK ( 컬럼명  IN ('A', 'B')) ➡ 제약 조건명 X
컬럼명 자료형(크기)  CONSTRAINT 컬럼명 이름 CHECK ( 컬럼명  IN ('A', 'B')) ➡ 제약 조건명 O

테이블 레벨
CHECK ( 컬럼명 IN ('A', 'B')) ➡ 제약 조건명 X
CONSTRAINT 컬럼명 이름 CHECK ( 컬럼명  IN ('A', 'B'))➡ 제약 조건명 O

➡ 비교할 값이 두 개이니 IN

➡ 컬럼에 CHECK 제약조건으로 'A' 또는 'B'만 기록 가능한데 'A1'같이 조건 이외의 값이 들어오면 에러 발생

 

 

1) CHECK 제약 조건은 범위로도 설정 가능

CREATE TABLE CHECK_TEST (
    TEST_NO NUMBER PRIMARY KEY,
    INPUT_NUMBER NUMBER CHECK (INPUT_NUMBER > 0)
);

➡ INPUT_NUMBER 컬럼에는 0보다 큰 수만 삽입 가능

➡ INPUT_NUMBER NUMBER CHECK (INPUT_NUMBER > 0 ANDINPUT_NUMBER < 10) 처럼

BETWEEN A AND B도 가능

---

🚩 TRUNCATE

Table 테이블명이(가) 잘렸습니다.

➡ ROLLBACK으로 복구  불가능

---

🚩 SUBQUERY를 이용한 테이블 생성 

컬럼명, 데이터 타입, 값이 복사되고, 제약조건은 NOT NULL 만 복사

 

1) 테이블 전체 복사

RESULT SET을 기준으로 복사 되어서
컬럼명, 데이터 타입, NULLABLE 까지는 다 동일하게 복사
DATA_DEFALUT, COMMENTS는 안된다.

CREATE TABLE 새로운 테이블명
AS SELECT * FROM 복사할 테이블명;

 

2) JOIN 후 원하는 컬럼만 테이블로 복사

CREATE TABLE 새로운 테이블명 AS 
SELECT 컬럼명1, 컬럼명2, 컬럼명3, 컬럼명4, 컬럼명5
FROM 복사할 테이블명
LEFT JOIN DEPARTMENT ON (컬럼명 = 컬럼명)
JOIN 테이블명 USING(컬럼명 );

➡ 왼쪽 테이블을 기준으로 null값이 포함되게 JOIN하려면 LEFT JOIN

 

3) 테이블 복사 시 데이터는 제거  (테이블의 틀만 복사)

CREATE TABLE새로운 테이블명 AS
SELECT * FROM 복사할 테이블명
WHERE 1 = 0 ;

➡ 조건을 틀리게 해 데이터는 복사되지 않고 틀(컬럼)만 복사한다. 
주로 쓰는 조건 WHERE 1 = 0으로, 무조건  FALSE인 상황이기때문에 데이터가 조회되지 않는다.

---

🚩 제약조건 추가

ALTER(바꾸다, 변조하다) : 객체의 구조를 변경하는 DDL

- ALTER TABLE 테이블명 ADD [CONSTRAINT 제약조건명] PRIMARY KEY(컬럼명)

- ALTER TABLE 테이블명 ADD [CONSTRAINT 제약조건명] 
  FOREIGN KEY(컬럼명) REFERENCES 참조 테이블명(참조컬럼명)
--> 참조 테이블의 PK를 기본키를 FK로 사용하는 경우 참조컬럼명 생략 가능

- ALTER TABLE 테이블명 ADD [CONSTRAINT 제약조건명] UNIQUE(컬럼명)
- ALTER TABLE 테이블명 ADD [CONSTRAINT 제약조건명] CHECK(컬럼명 비교연산자 비교값)
- ALTER TABLE 테이블명 MODIFY 컬럼명 NOT NULL;