테이블 생성 & 제약조건(CREATE & CONSTRAINT)

👀 테이블 생성(CREATE)

💁‍♀️ 테이블 생성(CREATE)이란,
관계형 데이터베이스 관리시스템 (RDBMS)의 관리 하에 객체를 생성하는 데이터 정의 언어(DDL) 명령

---

💁‍♀️ DDL(Data Definition Language)이란,
데이터를 정의하는 언어이며 객체(Object)를 만들고(CREATE), 수정하고(ALTER), 삭제(DROP)하는 구문

---

👉 테이블 생성하기

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

CREATE TABLE MEMBER(
  MEMBER_ID VARCHAR2(20),
  MEMBER_PWD VARCHAR2(20),
  MEMBER_NAME VARCHAR2(20)
);

👉 컬럼에 주석달기

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

COMMENT ON COLUMN MEMBER.MEMBER_ID IS '회원아이디';
COMMENT ON COLUMN MEMBER.MEMBER_PWD IS '회원비밀번호';
COMMENT ON COLUMN MEMBER.MEMBER_NAME IS '회원이름';

📌 해당 계정이 보유하고 있는 테이블, 컬럼 조회하기

SELECT
        UT.*
    FROM USER_TABLES UT;
    >>> USER_TABLES : '계정이 가지고 있는 테이블들'의 의미인 시스템 상에서 보유하고 있는 테이블

SELECT
        UTC.*
    FROM USER_TAB_COLUMNS UTC
    WHERE UTC.TABLE_NAME = 'MEMBER';
    >>> USER_TAB_COLUMNS : 시스템 상에서 보유하고 있는 테이블

📌 서브쿼리를 이용한 테이블 생성

💁‍ 컬럼명, 데이터타입, 값이 복사되고, 제약조건은 NOY NULL만 복사됨 (나머지 제약조건은 복사 X)

• CREATE TABLE 새테이블명 AS SELECT 복사할테이블명.* FROM 복사할테이블명;

CREATE TABLE EMPLOYEE_COPY
AS
SELECT
        E.*
    FROM EMPLOYEE E;    
    >>> 새 테이블을 생성하고 AS를 붙인 후 SELECT구문을 적으면 그대로 복사됨
    
SELECT
        EC.*
    FROM EMPLOYEE_COPY EC;
    >>> 똑같은 테이블 확인 가능

---

👀 제약조건(CONSTRAINT)

💁‍♀️ 제약조건(CONSTRAINT)이란,
테이블 작성 시 각 컬럼에 대한 기록에 대해 제약조건을 설정하는 것

• 데이터 무결성 보장이 주 목적
• 입력 데이터에 문제가 없는지에 대한 검사와 데이터의 수정/삭제 가능 여부 검사 등에 사용

제약 조건	설명
NOT NULL	데이터에 NULL 허용 X
UNIQUE	중복된 값 허용 X
CHECK	저장 가능한 데이터 값의 범위나 조건을 지정하여 설정한 값만 허용
PRIMARY KEY	NULL 허용 X. 중복된 값 허용 X. 컬럼의 고유 식별자로 사용하기 위함
FOREIGN KEY	참조되는 테이블의 컬럼의 값이 존재하면 허용

---

📌 해당 계정이 보유하고 있는 제약 조건 조회하기

SELECT
        UC.*
    FROM USER_CONSTRAINTS UC;	>>> 이 테이블에 무슨 제약조건이 있는지 조회

SELECT
        UCC.*
    FROM USER_CONS_COLUMNS UCC;	>>> 각 컬럼별로 무슨 제약조건이 있는지 조회

📌 제약 조건에 이름을 붙여 테이블 생성

'SYS_C008038'등을 대체 할 직접적인 이름 설정 가능

• '컬럼 레벨'에 설정 시, CONSTRAINT 원하는제약조건명 제약조건
• '테이블 레벨'에 설정 시, CONSTRAINT 원하는제약조건명 제약조건(컬럼명)

CREATE TABLE CONS_NAME(
 TEST_DATA1 VARCHAR2(20) CONSTRAINT NN_TEST_DATA1 NOT NULL, 
 TEST_DATA2 VARCHAR2(20) CONSTRAINT UN_TEST_DATA2 UNIQUE,
 TEST_DATA3 VARCHAR2(30),
 CONSTRAINT UN_TEST_DATA3 UNIQUE(TEST_DATA3) 
 >>> 테이블에 입력하는 제약조건명은 이렇게 작성
);

📌 제약 조건명을 이용해서 제약 조건 검색

이 테이블, 컬럼에 어떤 제약 조건이 위배되었는지 확인 할 수 있는 구문

SELECT
        UCC.TABLE_NAME "테이블명"
    ,   UCC.COLUMN_NAME	"컬럼명"
    ,   UC.CONSTRAINT_TYPE	"제약 조건의 타입"
    FROM USER_CONSTRAINTS UC
    ,   USER_CONS_COLUMNS UCC
    WHERE UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME
    AND UCC.CONSTRAINT_NAME = 'SYS_C008030';
    							>>> '오류 코드' 입력
                                >>> 제약 조건명을 부여했다면, '제약 조건명' 입력

---

📍 제약 조건의 종류

1) NOT NULL
2) UNIQUE
3) CHECK
4) PRIMARY KEY
5) FOREIGN KEY

---

👉 NOT NULL

💁‍♀️ NOT NULL이란,
해당 컬럼에 반드시 값이 기록되어야하는 경우 사용하는 제약 조건

• 삽입 or 수정 시 NULL 값을 허용하지 않도록 하며 '컬럼 레벨'에서 제한
• '테이블 레벨'에서 사용 X

[1] 컬럼 레벨에 NOT NULL 제약 조건을 설정하면서 테이블 생성

CREATE TABLE USER_NOTNULL (
  USER_NO NUMBER NOT NULL,	>>>  필수 정보이니 꼭 값을 넣어야하는 컬럼에 NOT NULL
  USER_ID VARCHAR2(20) NOT NULL,
  USER_PWD VARCHAR2(30) NOT NULL,
  USER_NAME VARCHAR2(30) NOT NULL,
  GENDER VARCHAR2(10),
  PHONE VARCHAR2(30),
  EMAIL VARCHAR2(50)
);

[2] 만들어진 USER_NOTNULL 테이블의 제약 조건 검색(조회)

SELECT
        UC.*
    ,   UCC.*
    FROM USER_CONSTRAINTS UC
    JOIN USER_CONS_COLUMNS UCC ON(UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME)
    WHERE UC.TABLE_NAME = 'USER_NOTNULL';

[3] USER_NOTNULL 테이블의 제약 조건 테스트 (NULL값인 행 삽입)

INSERT INTO 테이블명 (컬럼 명칭, ...) VALUES (값, ...)

INSERT
  INTO USER_NOTNULL 
(
  USER_NO -- 컬럼 명칭 서술
, USER_ID
, USER_PWD
, USER_NAME
, GENDER
, PHONE
, EMAIL
)
VALUES
(        >>> NOT NULL 제약 조건 컬럼에 NULL값 입력
  NULL   -- NULL을 ("C##EMPLOYEE"."USER_NOTNULL"."USER_NO") 안에 삽입할 수 없습니다
, NULL   -- NULL을 ("C##EMPLOYEE"."USER_NOTNULL"."USER_ID") 안에 삽입할 수 없습니다
, NULL   -- ''
, NULL   -- ''
, NULL   >>> 정상적으로 행 삽입 완료 (이 컬럼에는 NOT NULL 제약 조건이 없으므로)
, '010-1234-5678'
, 'hong123@greedy.com'
);

---

👉 UNIQUE

💁‍♀️ UNIQUE란,
컬럼의 입력 값에 대해 중복을 제한하는 제약 조건

• '컬럼 레벨', '테이블 레벨'에 모두 설정 가능
• 아이디 등의 컬럼은 중복을 허용하면 안되므로 UNIQUE 제약조건이 필요

1) 컬럼 레벨에서의 UNIQUE 제약 조건 설정

[1] UNIQUE 제약 조건을 '컬럼 레벨'에서 설정한 USER_UNIQUE 테이블 생성

CREATE TABLE USER_UNIQUE (
  USER_NO NUMBER, 
  USER_ID VARCHAR2(20) UNIQUE NOT NULL, 
  >>> '고유값'이니 중복되면 안되는 컬럼에 UNIQUE (다른 제약조건 옆에 나열 가능)
  USER_PWD VARCHAR2(30) NOT NULL,
  USER_NAME VARCHAR2(30) NOT NULL,
  GENDER VARCHAR2(10),
  PHONE VARCHAR2(30),
  EMAIL VARCHAR2(50)
);

[2] USER_UNIQUE 테이블에 USER_ID로 동일한 값이 삽입 불가능한지 테스트

INSERT
  INTO USER_UNIQUE 
  >>> 두 번째 실행시, '무결성 제약 조건(C##EMPLOYEE.SYS_C008030)에 위배됩니다' 오류
  >>> UNIQUE한 USER_ID컬럼에 이미 user01가 존재하므로 오류 발생
(
  USER_NO 
, USER_ID
, USER_PWD
, USER_NAME
, GENDER
, PHONE
, EMAIL
)
VALUES
(
  1         
, 'user01'
, 'pass01'
, '홍길동'
, '남'
, '010-1234-5678'
, 'hong123@greedy.com'
);

2) 테이블 레벨에서의 UNIQUE 제약 조건 설정

[1] UNIQUE 제약 조건을 '테이블 레벨'에서 설정한 USER_UNIQUE2 테이블 생성

CREATE TABLE USER_UNIQUE2 (
  USER_NO NUMBER, 
  USER_ID VARCHAR2(20) NOT NULL,
  USER_PWD VARCHAR2(30) NOT NULL,
  USER_NAME VARCHAR2(30) NOT NULL,
  GENDER VARCHAR2(10),
  PHONE VARCHAR2(30),
  EMAIL VARCHAR2(50),
  UNIQUE(USER_ID)
  >>> 테이블 레벨에서 UNIQUE를 설정했을 때, '제약 조건 대상 컬럼'을 소괄호 안에 표시
  >>> '컬럼 레벨'에서 설정한 USER_UNIQUE 테이블과 완벽히 같은 생성 구문
);

[2] 두 개 이상의 컬럼을 묶어서 하나의 UNIQUE 제약 조건을 설정 (테이블 레벨에서만 설정 가능)

CREATE TABLE USER_UNIQUE3 (
  USER_NO NUMBER, 
  USER_ID VARCHAR2(20) NOT NULL,
  USER_PWD VARCHAR2(30) NOT NULL,
  USER_NAME VARCHAR2(30) NOT NULL,
  GENDER VARCHAR2(10),
  PHONE VARCHAR2(30),
  EMAIL VARCHAR2(50),
  UNIQUE(USER_NO, USER_ID) >>> 컬럼을 묶어서 UNIQUE 제약 조건 검
);

[3] USER_UNIQUE3 테이블에 USER_NO, USER_ID에 대해 중복 값 입력이 불가한지 테스트

INSERT
  INTO USER_UNIQUE3 >>> 두 번째 실행시, '무결성 제약 조건(C##EMPLOYEE.SYS_C008034)에 위배됩니다' 오류
(
  USER_NO 
, USER_ID
, USER_PWD
, USER_NAME
, GENDER
, PHONE
, EMAIL
)
VALUES
(			>>> USER_NO 및 USER_ID 둘 중에 하나라도 바꾸면 삽입 가능		   
  1         >>> BUT, 두 컬럼의 값이 전과 같은 상태에서 한번 더 행 삽입 시도시, 오류 발생
, 'user01'  >>> (묶어서 UNIQUE 제약 조건을 걸었으므로)
, 'pass01'
, '홍길동'
, '남'
, '010-1234-5678'
, 'hong123@greedy.com'
);

---

👉 CHECK

💁‍♀️ CHECK란,
컬럼에 기록되는 값에 조건 설정을 할 수 있는 제약 조건

• CHECK(컬럼명 비교연산자 비교값)
• 비교값은 리터럴만 사용가능, 변하는 값이나 함수는 사용 X

1) 컬럼 레벨에서의 CHECK 제약 조건 설정

[1] CHECK 제약 조건을 '컬럼 레벨'에서 설정한 USER_CHECK 테이블 생성

CREATE TABLE USER_CHECK (
  USER_NO NUMBER, 
  USER_ID VARCHAR2(20) UNIQUE,
  USER_PWD VARCHAR2(30) NOT NULL,
  USER_NAME VARCHAR2(30),
  GENDER VARCHAR2(10) CHECK(GENDER IN ('남', '여')),
  						-- : 오직 남, 여만 값으로 들어갈 수 있음
  PHONE VARCHAR2(30),
  EMAIL VARCHAR2(50)
);

[2] USER_CHECK테이블의 GENDER에 대해 '남' OR '여' 외에 입력이 불가한지 테스트

INSERT
  INTO USER_CHECK 
(
  USER_NO 
, USER_ID
, USER_PWD
, USER_NAME
, GENDER
, PHONE
, EMAIL
)
VALUES
(
  3         
, 'user03'  
, 'pass03'
, '선덕여왕'
, '여자' >>> 남 or 여가 아니면, '체크 제약조건(C##EMPLOYEE.SYS_C008043)이 위배되었습니다' 오류
, '010-1234-5678'
, 'hong123@greedy.com'
);

2) 테이블 레벨에서의 CHECK 제약 조건 설정

[1] CHECK 제약 조건을 '테이블 레벨'에서 설정한 TEST_CHECK 테이블 생성

CREATE TABLE TEST_CHECK(
  TEST_NUMBER NUMBER,
  CONSTRAINT CK_TEST_NUMBER CHECK(TEST_NUMBER > 0)
  
  >>> 제약 조건명은 'CK_TEST_NUMBER'로 짓고 
  >>> TEST_NUMBER는 0이상이어야한다는 CHECK 제약 조건 설정
);

[2] TEST_CHECK 테이블에 삽입 테스트

INSERT
  INTO TEST_CHECK
(
  TEST_NUMBER
)
VALUES
(
  -10
  >>> 0보다 작은 값의 삽입을 시도할 시, '체크 제약조건(C##EMPLOYEE.CK_TEST_NUMBER)이 위배되었습니다' 오류
);

---

👉 PRIMARY KEY

💁‍♀️ PRIMARY KEY란,
테이블에서 한 행의 정보를 찾기 위해 사용할 컬럼을 의미하며, 테이블에 대한 식별자 역할

• NOT NULL + UNIQUE의 의미
• '기본키'/'복합키'
• 한 테이블 당 하나만 설정 가능
• '컬럼 레벨', '테이블 레벨' 둘 다 설정 가능

1) 컬럼 레벨에서의 PRIMARY KEY 제약 조건 설정

[1] PRIMARY KEY 제약 조건을 '컬럼 레벨'에서 설정한 USER_PRIMARYKEY 테이블 생성

CREATE TABLE USER_PRIMARYKEY (
  USER_NO NUMBER CONSTRAINT PK_USER_NO PRIMARY KEY, 
  USER_ID VARCHAR2(20) UNIQUE,
  USER_PWD VARCHAR2(30) NOT NULL,
  USER_NAME VARCHAR2(30),
  GENDER VARCHAR2(10) CHECK(GENDER IN ('남', '여')),
  PHONE VARCHAR2(30),
  EMAIL VARCHAR2(50)
);

[2] PRIMARY KEY의 NOT NULL, UNIQUE 테스트

INSERT
  INTO USER_PRIMARYKEY
(
  USER_NO 
, USER_ID
, USER_PWD
, USER_NAME
, GENDER
, PHONE
, EMAIL
)
VALUES
(
  1      >>> NULL을 넣을 경우, 'NULL을 ("C##EMPLOYEE"."USER_PRIMARYKEY"."USER_NO") 안에 삽입할 수 없습니다' 오류
         >>> 1을 두 번 넣을 경우, '무결성 제약 조건(C##EMPLOYEE.PK_USER_NO)에 위배됩니다' 오류
, 'user01'  
, 'pass01'
, '홍길동'
, '남' 
, '010-1234-5678'
, 'hong123@greedy.com'
);

2) 테이블 레벨에서의 PRIMARY KEY 제약 조건 설정

[1] PRIMARY KEY 제약 조건을 '테이블 레벨'에서 설정한 USER_PRIMARYKEY2 테이블 생성 (복합키)

CREATE TABLE USER_PRIMARYKEY2 (
  USER_NO NUMBER, 
  USER_ID VARCHAR2(20),
  USER_PWD VARCHAR2(30) NOT NULL,
  USER_NAME VARCHAR2(30),
  GENDER VARCHAR2(10) CHECK(GENDER IN ('남', '여')),
  PHONE VARCHAR2(30),
  EMAIL VARCHAR2(50),
  CONSTRAINT PK_USER_NO2 PRIMARY KEY(USER_NO, USER_ID) >>> 묶어서 PRIMARY KEY 설정
);

[2] PRIMARY KEY의 NOT NULL, UNIQUE 테스트

INSERT
  INTO USER_PRIMARYKEY2
(
  USER_NO 
, USER_ID
, USER_PWD
, USER_NAME
, GENDER
, PHONE
, EMAIL
)
VALUES
(
  1             >>> USER_NO나 USER_ID 둘 중 하나라도 값이 바뀌면 OK, 
  				>>> but, 둘 다 같으면 '무결성 제약 조건(C##EMPLOYEE.PK_USER_NO2)에 위배됩니다' 오류
, 'user01'  
, 'pass01'
, '홍길동'
, '남' 
, '010-1234-5678'
, 'hong123@greedy.com'
);

---

👉 FOREIGN KEY

💁‍♀️ FOREIGN KEY란,
참조(REFERENCES)된 다른 테이블에서 제공하는 값만 사용 가능하게 하는 제약 조건

• 참조 무결성을 위배하지않기 위해서 사용
• FOREIGN KEY 제약 조건에 의해서 테이블 간의 관계가 형성되며 제공되는 값 외에는 NULL 사용 가능
• 컬럼 레벨인 경우,
  • 컬럼명 자료형(크기) [CONSTRAINT 제약조건명] REFERENCES 참조테이블명 [(참조컬럼명)] [삭제옵션] => [ ]은 생략가능
• 테이블 레벨인 경우,
  • [CONSTRAINT 제약조건명] FOREIGN KEY(적용컬럼명) REFERENCES 참조테이블명 [(참조컬럼명)] [삭제옵션] => [ ]은 생략가능
• 참조테이블의 참조컬럼명이 생략되면 PROMARY KEY로 설정된 컬럼이 자동으로 참조 컬럼이 됨
• PROMARY KEY컬럼과 UNIQUE로 지정된 컬럼만 참조될 수 있음 (참조될 컬럼은 꼭 특별해야하므로)

[1] PROMARY KEY가 설정된 컬럼을 포함한 USER_GRADE 테이블 생성 후, 데이터 삽입

CREATE TABLE USER_GRADE(
  GRADE_CODE NUMBER PRIMARY KEY,
  GRADE_NAME VARCHAR2(30) NOT NULL
);

INSERT
  INTO USER_GRADE
(
  GRADE_CODE, GRADE_NAME
)
VALUES
(
  10, '일반회원'
);

INSERT
  INTO USER_GRADE
(
  GRADE_CODE, GRADE_NAME
)
VALUES
(
  20, '우수회원'
);

INSERT
  INTO USER_GRADE
(
  GRADE_CODE, GRADE_NAME
)
VALUES
(
  30, '특별회원'
);

[2] 위 USER_GRADE 테이블의 GRADE_CODE를 참조하는 FOREIGNKEY(외래키) 설정한 USER_FOREIGNKEY 테이블 생성

CREATE TABLE USER_FOREIGNKEY (
  USER_NO NUMBER PRIMARY KEY, 
  USER_ID VARCHAR2(20) UNIQUE,
  USER_PWD VARCHAR2(30) NOT NULL,
  USER_NAME VARCHAR2(30),
  GENDER VARCHAR2(10) CHECK(GENDER IN ('남', '여')),
  PHONE VARCHAR2(30),
  EMAIL VARCHAR2(50),
  GRADE_CODE NUMBER,
  CONSTRAINT FK_GRADE_CODE FOREIGN KEY(GRADE_CODE) REFERENCES USER_GRADE(GRADE_CODE)
  													>>> FOREIGNKEY(외래키) 설정
);

[3] 여러가지의 데이터를 삽입

✅ 참조하는 GRADE_CODE의 값 중 하나인 10입력 (삽입 O)

INSERT
  INTO USER_FOREIGNKEY
(
  USER_NO , USER_ID, USER_PWD, USER_NAME, GENDER, PHONE, EMAIL, GRADE_CODE
)
VALUES
(
  1, 'user01'  , 'pass01', '홍길동', '남' , '010-1234-5678', 
  'hong123@greedy.com', 10
);

✅ FOREIGN KEY는 NULL값을 허용 (삽입 O)

INSERT
  INTO USER_FOREIGNKEY
(
  USER_NO , USER_ID, USER_PWD, USER_NAME, GENDER, PHONE, EMAIL, GRADE_CODE
)
VALUES
(
  4, 'user04'  , 'pass04', '선덕여왕', '여' , '010-1235-5678'
, 'SUN123@greedy.com', NULL
						>>> NULL값은 허용됨 (FOREIGN KEY와는 상관X)
);

❌ 부모키가 없어 외래키 제약 조건 위반 (삽입 X)

INSERT
  INTO USER_FOREIGNKEY
(
  USER_NO , USER_ID, USER_PWD, USER_NAME, GENDER, PHONE, EMAIL, GRADE_CODE
)
VALUES
(
  5, 'user05'  , 'pass05', '신사임당', '여' , '010-1234-5678'
, 'shin123@greedy.com', 50
						>>> 50입력 시, 
                        >>> '무결성 제약조건(C##EMPLOYEE.FK_GRADE_CODE)이 위배되었습니다- 부모 키가 없습니다' 오류
     					>>> GRADE_CODE에 10, 20, 30만 설정해뒀기 때문
);

---

👀 삭제 옵션 (DELETE RULL)

💁‍♀️ 삭제 옵션 (DELETE RULL)이란,
부모 테이블의 데이터 삭제 시, 자식 테이블의 데이터를 어떤 식으로 처리할 것인지에 대해 설정하는 것

---

📍 DELETE 옵션의 종류

1) ON DELETE RESTRICT
2) ON DELETE SET NULL
3) ON DELETE CASCADE

---

👉 ON DELETE RESTRICT

💁‍♀️ ON DELETE RESTRICT이란,
FOREIGN KEY로 지정된 컬럼에서 사용 되고 있는 값일 경우, 제공하는 컬럼의 값은 삭제 불가

• 삭제 기본 지정 룰
• 자식 레코드로 사용되지 않는 값은 삭제 가능

[1] 자식 레코드로 사용되지 않는 값은 삭제 가능

DELETE
  FROM USER_GRADE
  WHERE GRADE_CODE = 20; 
  >>> 20은 참조되고있지않으므로 삭제 완료

👉 ON DELETE SET NULL

💁‍♀️ ON DELETE SET NULL이란,
부모 키가 삭제될 시, 자식 키를 NULL로 변경하는 옵션

[1] 삭제 제한이 걸려있지 않아 자식 레코드가 있더라도 삭제가 수행

DELETE
  FROM USER_GRADE2
  WHERE GRADE_CODE = 10; -- 삭제 완료

SELECT
        UG.*
    FROM USER_GRADE2 UG; -- 삭제 후 10이라는 코드를 가진 행이 삭제되어있음

[2] 대신 삭제된 값을 참조할 수는 없으므로 NULL 값으로 변경

SELECT
        UF.*
    FROM USER_FOREIGNKEY2 UF; -- 조회 시, 확인 가능

👉 ON DELETE CASCADE

💁‍♀️ ON DELETE CASCADE란,
부모 키가 삭제될 시, 자식 키를 가진 행도 함께 삭제

[1] 삭제 제한이 걸려있지 않아 자식 레코드가 있더라도 삭제 가능

DELETE
  FROM USER_GRADE3
  WHERE GRADE_CODE = 10; -- 삭제 완료

SELECT
        UG.*
    FROM USER_GRADE3 UG;

[2] 대신 삭제된 값을 참조할 수 없으므로 자식 테이블의 해당 행이 함께 삭제됨

SELECT
        UF.*
    FROM USER_FOREIGNKEY3 UF; -- 행 2개가 삭제되어 나머지 2개만 남아있는 것 확인 가능

---