SQL - 제약 조건

AIR·2024년 1월 17일
oraclesql

제약 조건

오라클에서 사용하는 제약 조건은 테이블의 특정 열에 지정한다.
제약 조건을 지정한 열에 제약 조건에 부합하지 않는 데이터를 저장할 수 없다.
제약 조건 지정 방식에 따라 기존 데이터의 수정이나 삭제 가능 여부도 영향을 받는다.
제약 조건은 데이터 정의어(DDL)에서 활용한다.

오라클 데이터베이스에서 사용하는 제약 조건은 다음과 같다.

  • NOT NULL: 지정한 열에 NULL을 허용하지 않는다. NULL을 제외한 데이터의 중복은 허용된다.
  • UNIQUE: 지정한 열이 유일한 값을 가져야 한다. 단 NULL은 값의 중복에서 제외된다.
  • PRIMARY KEY: 지정한 열이 유일한 값이면서 NULL을 허용하지 않는다. 테이블에 하나만 지정 가능하다.
  • FOREIGN KEY: 다른 테이블의 열을 참조하여 존재하는 값만 입력할 수 있다.
  • CHECK: 설정한 조건식을 만족하는 데이터만 입력 가능하다.

데이터 무결성

데이터 무결성은 데이터베이스에 저장되는 데이터의 정확성과 일관성을 보장한다는 의미이며
이를 위해 항상 유지해야 하는 기본 규칙을 가지고 있다.
제약 조건은 이러한 데이터 무결성을 지키기 위한 안정장치로서 중요한 역할을 한다.

무결성의 종류는 다음과 같다.

  • 영역 무결성(domain integrity): 열에 저장되는 값의 적정 여부를 확인. 자료형, 적절한 형식의 데이터, NULL 여부같은 정해 놓은 범위를 만족하는 데이터임을 규정
  • 개체 무결성(entity integrity): 테이블 데이터를 유일하게 식별할 수 있는 기본키는 반드시 값을 가지고 있어야 하며 NULL이 될 수 없고 중복될 수도 없음을 규정
  • 참조 무결성(referential integrity): 참조 테이블의 외래키 값은 참조 테이블의 기본키로서 존재해야 하며 NULL이 가능

NOT NULL

-- NOT NULL
CREATE TABLE TABLE_NOTNULL
(
    LOGIN_ID  VARCHAR2(20) NOT NULL,
    LOGIN_PWD VARCHAR2(20) NOT NULL,
    TEL       VARCHAR2(20)
);
-- 오류 발생
INSERT INTO TABLE_NOTNULL(LOGIN_ID, LOGIN_PWD, TEL) VALUES ('TEST_ID_01', NULL, '010-1234-5678');
UPDATE TABLE_NOTNULL SET LOGIN_PWD =NULL WHERE LOGIN_ID = 'TEST_ID_01';


-- TEL열은 오류 발생 X
INSERT INTO TABLE_NOTNULL(LOGIN_ID, LOGIN_PWD)
VALUES ('TEST_ID_01', '1234');

SELECT * FROM TABLE_NOTNULL;

-- 제약 조건 이름 직접 지정
CREATE TABLE TABLE_NOTNULL2
(
    LOGIN_ID  VARCHAR2(20)
        CONSTRAINT TBLNN2_LGNID_NN NOT NULL,
    LOGIN_PWD VARCHAR2(20)
        CONSTRAINT TBLNN2_LGNPW_NN NOT NULL,
    TEL       VARCHAR2(20)
);

-- 이미 생성한 테이블에 제약 조건 지정
UPDATE TABLE_NOTNULL SET TEL = '010-1234-5678' WHERE LOGIN_ID = 'TEST_ID_01';
ALTER TABLE TABLE_NOTNULL MODIFY (TEL NOT NULL);
ALTER TABLE TABLE_NOTNULL2 MODIFY (TEL CONSTRAINT TBLNN_TEL_NN NOT NULL);

-- 이미 생성된 제약 조건 이름 변경
ALTER TABLE TABLE_NOTNULL2 RENAME CONSTRAINT TBLNN_TEL_NN TO TBLNN2_TEL_NN;

-- 제약 조건 삭제
ALTER TABLE TABLE_NOTNULL2 DROP CONSTRAINT TBLNN2_TEL_NN;

UNIQUE

-- UNIQUE
CREATE TABLE TABLE_UNIQUE
(
    LOGIN_ID  VARCHAR2(20) UNIQUE,
    LOGIN_PWD VARCHAR2(20) NOT NULL,
    TEL       VARCHAR2(20)
);

SELECT OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME
FROM ALL_CONSTRAINTS
WHERE TABLE_NAME = 'TABLE_UNIQUE';

-- 중복을 허락하지 않는 UNIQUE
INSERT INTO TABLE_UNIQUE(LOGIN_ID, LOGIN_PWD, TEL)
VALUES ('TEST_ID_01', 'PWD01', '010-1234-5678');
SELECT * FROM TABLE_UNIQUE;

INSERT INTO TABLE_UNIQUE(LOGIN_ID, LOGIN_PWD, TEL)
VALUES ('TEST_ID_02', 'PWD02', '010-2345-6789');

-- UNIQUE 중복은 허용하지 않지만 NULL 저장은 가능
INSERT INTO TABLE_UNIQUE(LOGIN_ID, LOGIN_PWD, TEL)
VALUES (NULL, 'PWD01', '010-2345-6789');

-- 제약 조건 이름 직접 지정
CREATE TABLE TABLE_UNIQUE2
(
    LOGIN_ID  VARCHAR2(20)
        CONSTRAINT TBLUNQ2_LGNID_UNQ UNIQUE,
    LOGIN_PWD VARCHAR2(20)
        CONSTRAINT TBLUNQ2_LGNPW_NN NOT NULL,
    TEL       VARCHAR2(20)
);
SELECT OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME
FROM ALL_CONSTRAINTS
WHERE TABLE_NAME = 'TABLE_UNIQUE2';

-- 이미 생성한 테이블에 제약 조건 지정
UPDATE TABLE_UNIQUE SET TEL = NULL;
ALTER TABLE TABLE_UNIQUE MODIFY (TEL UNIQUE);
SELECT * FROM TABLE_UNIQUE;

-- 제약 조건 이름 직접 지정하거나 바꾸기
ALTER TABLE TABLE_UNIQUE2
    MODIFY (TEL CONSTRAINT TBLUNQ_TEL_UNQ UNIQUE);

ALTER TABLE TABLE_UNIQUE2 RENAME CONSTRAINT TBLUNQ_TEL_UNQ TO TBLUNQ2_TEL_UNQ;

-- 제약 조건 삭제
ALTER TABLE TABLE_UNIQUE2 DROP CONSTRAINT TBLUNQ2_TEL_UNQ;

PRIMARY KEY

기본 키 제약 조건은 UNIQUE와 NOT NULL 제약 조건의 특성을 모두 가지는 제약 조건이다.
NULL아닌 유일한 값을 가지므로 주민등록번호나 사원 번호같이 테이블의 각 행을 식별하는 데 사용된다.
기본키는 테이블에 하나밖에 지정할 수 없으며 특정 열을 기본키로 지정하면 자동으로 인덱스가 만들어진다.

-- PRIMARY KEY

-- 제약 조건 지정
CREATE TABLE TABLE_PK
(
    LOGIN_ID  VARCHAR2(20) PRIMARY KEY,
    LOGIN_PWD VARCHAR2(20) NOT NULL,
    TEL       VARCHAR2(20)
);

SELECT OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME
FROM ALL_CONSTRAINTS WHERE TABLE_NAME LIKE 'TABLE_PK%';

-- PRIMARY KEY를 통해 자동 생성된 INDEX 확인
SELECT INDEX_NAME, TABLE_OWNER, TABLE_NAME
FROM ALL_INDEXES
WHERE TABLE_NAME LIKE 'TABLE_PK%';

-- 제약 조건 직접 지정
CREATE TABLE TABLE_PK2
(
    LOGIN_ID  VARCHAR2(20)
        CONSTRAINT TBLPK2_LGNID_PK PRIMARY KEY,
    LOGIN_PWD VARCHAR2(20)
        CONSTRAINT TBLPK2_LGNPW_NN NOT NULL,
    TEL       VARCHAR2(20)
);

-- CREATE문에서 제약 조건을 지정하는 다른 방식
-- 인라인 제약 조건 정의(열 레벨): 지금까지 사용한 방식
-- 아웃오브라인 제약 조건 정의(테이블 레벨): NOT NULL 제약 조건을 제외한 제약 조건 지정 가능
CREATE TABLE TABLE_NAME(
    COL1 VARCHAR2(20),
    COL2 VARCHAR2(20),
    COL3 VARCHAR2(20),
    PRIMARY KEY (COL1),
    CONSTRAINT CONSTRAINT_NAME UNIQUE (COL2)
)

FOREIGN KEY

외래키는 서로 다른 테이블 간 관계를 정의하는 데 사용하는 제약 조건이다.

-- FOREIGN KEY

-- EMP 테이블과 DEPT 테이블의 제약 조건 확인
SELECT OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, R_OWNER, R_CONSTRAINT_NAME
FROM ALL_CONSTRAINTS
WHERE TABLE_NAME IN ('EMP', 'DEPT');

-- EMP 테이블의 DEPTNO 열은 DEPT 테이블의 DEPTNO 열을 참조
-- EMP 테이블의 DEPTNO 열에는 DEPT 테이블의 DEPTNO 열에 존재하는 값과 NULL만 저장 가능

-- 참조 대상 테이블 생성
CREATE TABLE DEPT_FK
(
    DEPTNO NUMBER(2)
        CONSTRAINT DEPTFK_DEPTNO_PK PRIMARY KEY,
    DNAME  VARCHAR2(14),
    LOC    VARCHAR2(13)
);

-- DEPT_FK 테이블의 DEPTNO 열을 참조하는 FOREIGN KEY 제약 조건 지정
CREATE TABLE EMP_FK
(
    EMPNO    NUMBER(4)
        CONSTRAINT EMPFK_EMPNO_PK PRIMARY KEY,
    ENAME    VARCHAR2(10),
    JOB      VARCHAR2(9),
    MGR      NUMBER(2),
    HIREDATE DATE,
    SAL      NUMBER(7, 2),
    COMM     NUMBER(7, 2),
    DEPTNO   NUMBER(2)
        CONSTRAINT EMPFK_DEPTNO_FK REFERENCES DEPT_FK (DEPTNO)
);

-- DEPT_FK 테이블에 데이터가 아직 없는 상태이기 때문에 EMP_FK 테이블의 DEPTNO에 데이터를 추가하면 오류가 난다.
INSERT INTO EMP_FK
VALUES (9999, 'TEST_MNAME', 'TEST_JOB', NULL, TO_DATE('2001/01/01', 'YYYY/MM/DD'), 3000, NULL, 10);

INSERT INTO DEPT_FK
VALUES (10, 'TEST_DNAME', 'TEST_LOC');

SELECT * FROM EMP_FK;

-- FOREIGN KEY로 참조 행 데이터 삭제
-- DEPT_FK 테이블에 10번 부서 데이터가 저장되어 있고 EMP_FK 테이블에는 이 10번 부서를 참조하는 데이터가 있다.
-- 이 경우에 DEPT_FK 테이블의 DEPTNO열에 저장된 10번 부서 데이터는 삭제할 수 없다.
DELETE FROM DEPT_FK WHERE DEPTNO = 10;

-- 제약 조건을 처음 지정할 때 다음과 같이 추가 옵션을 지정
-- 열 데이터 삭제 시 이 데이터를 참조하고 있는 데이터도 함께 삭제
-- CONSTRAINT [제약 조건 이름] REFERENCES 참조 테이블(참조할 열) ON DELETE CASCADE

-- 열 데이터 삭제 시 이 데이터를 참조하는 데이터를 NULL로 수정
-- CONSTRAINT [제약 조건 이름] REFERENCES 참조 테이블(참조할 열) ON DELETE SET NULL

CHECK

CHECK 제약 조건은 열에 저장할 수 있는 값의 범위 또는 패턴을 정의할 때 사용

-- CHECK

-- 테이블 생성 시 CHECK 제약 조건 설정
CREATE TABLE TABLE_CHECK
(
    LOGIN_ID  VARCHAR2(20)
        CONSTRAINT TBLCK_LOGINID_PK PRIMARY KEY,
    LOGIN_PWD VARCHAR2(20)
        CONSTRAINT TBLCK_LOGINPW_CK CHECK ( LENGTH(LOGIN_PWD) > 3 ),
    TEL       VARCHAR2(20)
);

-- CHECK 제약 조건에 맞는 예
INSERT INTO TABLE_CHECK VALUES ('TEST_ID', '1234', '010-1234-5678');
SELECT * FROM TABLE_CHECK;

-- CHECK 제약 조건 확인
SELECT OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME
FROM ALL_CONSTRAINTS
WHERE TABLE_NAME LIKE 'TABLE_CHECK';

DEFAULT

-- DEFAULT

-- 테이블 생성 시 DEFAULT 제약 조건 설정
CREATE TABLE TABLE_DEFAULT
(
    LOGIN_ID  VARCHAR2(20)
        CONSTRAINT TBLCK2_LOGINID_PK PRIMARY KEY,
    LOGIN_PWD VARCHAR2(20) DEFAULT '1234',
    TEL       VARCHAR2(20)
);

-- 기본값 확인
INSERT INTO TABLE_DEFAULT VALUES ('TEST_ID', NULL, '010-1234-5678');
INSERT INTO TABLE_DEFAULT (LOGIN_ID, TEL) VALUES ('TEST_ID2', '010-1234-5678');
SELECT * FROM TABLE_DEFAULT;

-- 제약 조건 비활성화, 활성화
-- ALTER TABLE 테이블 이름
-- DISABLE [NOVALIDATE / VALIDATE] CONSTRAINT 테이블 이름;
-- ALTER TABLE 테이블 이름
-- ENABLE [NOVALIDATE / VALIDATE] CONSTRAINT 테이블 이름;
profile
AIR
백엔드
이전 포스트

SQL - 객체 종류

다음 포스트

SQL - 사용자, 권한, 롤 관리

0개의 댓글