[SQL] 테이블 생성(CREATE) 및 제약 조건(Constraint)

• CREATE
• COMMENT
• 제약조건(NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK 

 

 

CREATE(테이블 생성)

 

표현식

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

 

-- 테이블 생성 예시
CREATE TABLE MEMBER(
    MEMBER_ID VARCHAR2(20),
    MEMBER_PWD VARCHAR2(20),
    MEMBER_NAME VARCHAR2(20)
);

 위 예시는 MEMBER_ID, MEMBER_PWD, MEMBER_NAME의 칼럼을 갖는 MEMBER 테이블 생성하였다.

---

 

칼럼에 주석 달기

 

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 '회원이름';

 

 

---

 

제약조건(Constraint)

• 테이블 작성 시 각 칼럼에 대해 값 기록에 대한 제약조건을 설정할 수 있다.
• 데이터 무결성 보장을 목적으로 한다.
• 입력/수정하는 데이터에 문제가 없는지 자동으로 검사하는 목적
• PRIMARY KEY, NOT NULL, UNIQUE, CHECK, FOREIGN KEY

 

NOT NULL 제약 조건

 

• 해당 칼럼에 반드시 값이 기록 되어야 하는 경우에 사용
• 삽입/수정 시 NULL 값을 허용하지 않는다.
• 컬럼 레벨에서 설정 가능

 

CREATE TABLE USER_NOTNULL(
    USER_NO NUMBER NOT NULL,
    USER_ID VARCHAR2(20) NOT NULL,
    EMAIL VARCHAR2(50)
);

 

 USER_NO, USER_ID, USER_PWD, USER_NAME은 NULL 값을 허용하지 않는 제약조건이 있다. 따라서 데이터 삽입 시 NOT NULL 제약조건이 걸린 칼럼들은 데이터 값을 넣어줘야 한다. 

 

-- USER_ID 칼럼의 값이 NULL이므로 오류가 발생
INSERT INTO USER_NOTNULL(
    USER_NO
   ,USER_ID
   ,EMAIL
)
VALUES(
    1
   ,NULL
   ,'hong123@greedy.com'
);

 

---

 

 

UNIQUE 제약 조건

 

• 칼럼의 입력 값에 대해 중복을 제한하는 제약조건
• 컬럼 레벨에서 설정 가능
• 테이블 레벨에서 설정 가능
• 제약조건 위배 - 무결성 제약 조건

 

-- UNIQUE 제약 조건 컬럼 레벨 설정
CREATE TABLE USER_UNIQUE(
    USER_NO NUMBER,
    USER_ID VARCHAR2(20) UNIQUE NOT NULL,
    USER_PWD VARCHAR2(30) NOT NULL,
    USER_NAME VARCHAR2(30)
);

-- UNIQUE 제약 조건 테이블 레벨 설정
CREATE TABLE USER_UNIQUE(
    USER_NO NUMBER,
    USER_ID VARCHAR2(20) NOT NULL,
    USER_PWD VARCHAR2(30) NOT NULL,
    USER_NAME VARCHAR2(30),
    
    UNIQUE(USER_ID)		-- 어떤 컬럼에 제약조건을 걸지 명시해줘야 한다.
                        	-- UNIQUE(USER_ID, USER_ID) 처럼 여러개 컬럼을 한번에 제약조건을 걸 수 있다.
);

 USER_ID는 중복된 데이터 값이 들어오는 것을 제한하는 제약조건이 있다. 따라서 데이터 삽입시 USER_ID가 중복이 안되도록 쿼리문을 작성하여야 한다. (위 코드는 NOT NULL, UNIQUE 총 2개의 제약조건이 걸려있다.)

-- 해당 쿼리를 2번 실행하면 무결성 제약 조건에 위배된다.
INSERT INTO USER_UNIQUE(
    USER_NO
  , USER_ID
  , USER_PWD
  , USER_NAME
)
VALUES(
    1
  , 'user01'
  , 'pass01'
  , '홍길동'
);

 여기서 주의할 점이 있다. 2개 이상의 칼럼을 묶어서 하나의 테이블 레벨로 제약조건을 걸 때 해당 칼럼들이 모두 같아야 제약조건이 실행된다. 

 

 UNIQUE(USER_ID, USER_NO) -> ('USER01', 'USER01') 삽입되었을 시 ('USER02', 'USER01') 삽입 가능

---

 

 

CHECK 제약조건 

 

• 칼럼에 기록되는 값에 조건 설정을 할 수 있다
• CHECK(칼럼명 비교연산자 비교값)
• 비교 값은 리터럴만 사용할 수 있다. (변하는 값이나 함수 사용 X)
• 칼럼 레벨, 테이블 레벨 모두 설정 가능
• 제약조건 위배 - 체크 제약조건

 

CREATE TABLE USER_CHECK(
    USER_NO NUMBER,
    USER_ID VARCHAR2(20) UNIQUE,
    USER_PWD VARCHAR2(30) NOT NULL,
    GENDER VARCHAR2(10) CHECK(GENDER IN ('남', '여'))
);

 GENDER 칼럼은 CHECK 제약조건으로 인해 값이 '남', '여'만 삽입될 수 있다.

INSERT INTO USER_CHECK(
    USER_NO
  , USER_ID
  , USER_PWD
  , GENDER
)
VALUES(
    1
  , 'user01'
  , 'pass01'
  , '남'
);

---

 

 

PRIMARY KEY(기본키) 제약조건

 

• 테이블에서 한 행의 정보를 찾기 위해 사용할 칼럼을 의미한다.
• 테이블에 대한 식별자 역할을 한다.
• NOT NULL + UNIQUE 제약조건과 같다.
• 한 테이블당 한 개만 설정할 수 있다.
• 칼럼 레벨, 테이블 레벨에서 설정 가능
• 기본키는 컬럼 1개로 설정할 수도 있고, 여러 개의 칼럼을 묶어서 복합키로 설정할 수 있다.

 

-- 컬럼 레벨에서 PK 설정
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)
);

-- 테이블 레벨에서 PK 설정 (복합키로 설정)
CREATE TABLE USER_PRIMARYKEY2(
    USER_NO NUMBER,
    USER_ID VARCHAR2(20),
    USER_PWD VARCHAR2(30) NOT NULL,
    USER_NAME VARCHAR2(30)
    
    CONSTRAINT PK_USER_NO2 PRIMARY KEY(USER_NO, USER_ID)
);

---

 

 

FOREIGN KEY(외부 키/외래 키) 제약조건

 

칼럼 레벨

[CONSTRAINT 이름] REFERENCES 참조할테이블명 [(참조할컬럼)] ON [삭제룰]

테이블 레벨

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

 

• 참조(REFERENCES) 된 다른 테이블에서 제공하는 값만 사용할 수 있다
• 참조 무결성을 위배하지 않게 하기 위해 사용
• FOREIGN KEY 제약조건에 의해 테이블 간의 관계가 형성됨
• 제공되는 값 외에는 NULL을 사용할 수 있다.
• 참조 테이블의 참조 칼럼명이 생략이 된 경우는 참조 테이블의 PK로 설정된 칼럼이
  자동으로 참조 칼럼이 된다.
• 참조될 수 있는 칼럼은 PK 칼럼과, UNIQUE 칼럼만 외래키로 참조할 수 있다.

 

-- 참조될 예시 테이블 생성(부모 테이블)
CREATE TABLE USER_GRADE(
    GRADE_CODE NUMBER PRIMARY KEY,
    GRADE_NAME VARCHAR2(30) NOT NULL
);

-- 다른 테이블을 참조할 예시 테이블 생성(자식 테이블)
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),
    PHONE VARCHAR2(30),
    EMAIL VARCHAR2(50),
    GRADE_CODE NUMBER,
    CONSTRAINT FK_GRADE_CODE FOREIGN KEY(GRADE_CODE) REFERENCES USER_GRADE (GRADE_CODE)
);

 

 부모 테이블의 칼럼인 GRADE_CODE의 값이 다음과 같다고 하자

 

 

 자식 테이블의 컬럼인 GRADE_CODE는 10, 20, 30, NULL 이외에 값이 들어오면 무결성 제약조건을 위배하게 되어 다음과 같은 오류 메시지가 나타난다.

 

 

 자식 테이블의 GRADE_CODE 값을 50으로 INSERT했을 때 나타나는 오류 메시지다. 오류가 난 이유는 부모 테이블의 GRADE_CODE 칼럼 값은 { 10, 20, 30 } 밖에 없는데 그 이외의 값인 50을 삽일 하려고 하여 오류가 발생한 것이다.

---

 

 

외래키 삭제 옵션

 

• 부모 테이블의 데이터 삭제 시 자식 테이블의 데이터를
  어떤 식으로 처리할 것인지에 대한 내용 설정
• ON DELETE RESTRICT
  - 부모 테이블의 칼럼 삭제 시 자식 테이블에서 참조하는 값일 경우 해당 칼럼의 값을 삭제 못한다.
  - 기본 설정
• ON DELETE SET NULL
  - 부모 키를 삭제 시 자식 키를 NULL로 변경한다.
• ON DELETE CASCADE
  - 부모 키를 삭제 시 자식 키를 가진 행도 함께 삭제

 

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),
    PHONE VARCHAR2(30),
    EMAIL VARCHAR2(50),
    GRADE_CODE NUMBER,
    CONSTRAINT FK_GRADE_CODE FOREIGN KEY(GRADE_CODE) REFERENCES USER_GRADE (GRADE_CODE) ON DELETE SET NULL
);

---

 

 

제약 조건에 이름 설정

 

-- 제약 조건에 이름 설정
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
       UC.*
  FROM USER_CONSTRAINTS UC
 WHERE TABLE_NAME = 'CONS_NAME';

 

 

 

---

 

출처: https://jddng.tistory.com/200 [IT 개발자들의 울타리:티스토리]