오라클 제약조건

최주영·2023년 4월 8일

목록 보기

14/22

제약조건

입력 데이터에 문제가 없는지에 대한 검사와 데이터 수정과 삭제 가능 여부 검사 등을 위해 사용
데이터 무결성 보장을 주 목적으로 함
💡 데이터 무결성 : 데이터의 정확성, 유효성, 일관성을 유지하고 보증하는 것
-> 데이터의 잘못된 입력, 수정, 삭제로부터 보호해야함
-> 입력한 데이터와 데이터베이스에 저장된 데이터 일치해야함
종류
(1) : NOT NULL 제약 조건
(2) : UNIQUE -> 유일 키 제약 조건
(3) : PRIMARY KEY -> 기본키 제약 조건
(4) : FOREIGN KEY -> 외래키 제약 조건
(5) : CHECK 제약 조건
제약조건 확인하는 명령어

SELECT *  // 테이블의 제약조건을 볼 수 있음
FROM USER_CONSTRAINTS;

SELECT *  // 컬럼의 제약조건을 볼 수 있음
FROM USER_CONS_COLUMNS;

//위 두개를 조인해서 한번에 보기
SELECT C.CONSTRAINT_NAME, CONSTRAINT_TYPE, C.TABLE_NAME, SEARCH_CONDITION, COLUMN_NAME 
FROM USER_CONSTRAINTS C
    JOIN USER_CONS_COLUMNS CC ON C.CONSTRAINT_NAME = CC.CONSTRAINT_NAME;

제약조건 설정하는 방법 (2가지)
(1) : 컬럼레벨에서 설정 (컬럼 뒤에다 바로 설정)
-> CREATE TABLE 테이블명(컬럼명 자료형 제약조건, 컬럼명2 자료형 제약조건,.....)
(2) : 테이블레벨에서 설정 (맨 마지막에다 설정)
-> CREATE TABLE 테이블명(컬럼명 자료형, 컬럼명2 자료형, 제약조건 설정....)

✅ NOT NULL

지정된 컬럼에 NULL값을 허용하지 않는 것 = 값을 무조건 입력해야함
기본적으로 컬럼에 NOT NULL 지정하지 않을시 NULLABLE (널값 입력 가능)

// ID, PASSWORD는 NULL을 허용하면 안되는 컬럼
// 밑 테이블은 제약조건 걸기
CREATE TABLE NN_MEMBER(
    MEMBER_NO NUMBER,
    MEMBER_ID VARCHAR2(20) NOT NULL, // 컬럼레벨에서 NOT NULL 제약 조건 걸기
    MEMBER_PWD VARCHAR2(20) NOT NULL,
    MEMBER_NAME VARCHAR2(10),
    MEMBER_AGE NUMBER
//    NOT NULL(MEMBER_NO) -> NOT NULL은 테이블레벨에서 설정 불가능하다 
);

INSERT INTO NN_MEMBER VALUES(NULL,NULL,NULL,NULL,NULL); // 제약조건 건 곳에는 널값을 넣을 수 있음

✅ UNIQUE

지정된 컬럼에 중복값을 허용하지 않는 것
컬럼이 유일한 값을 유지해야할 때 사용

CREATE TABLE NQ_MEMBER(
    MEMBER_NO NUMBER,
    MEMBER_ID VARCHAR2(20) UNIQUE,  -- 중복값 허용하지 못하도록 컬럼레벨에서 제약 조건 설정
    MEMBER_PWD VARCHAR2(20) NOT NULL,  -- NULL값 들어가지 못하도록 제약 조건 설정
    MEMBER_NAME VARCHAR2(10),
    MEMBER_AGE NUMBER
);

INSERT INTO NQ_MEMBER VALUES('1','ADMIN','1234','관리자',44);
INSERT INTO NQ_MEMBER VALUES('2','ADMIN','1234','유저1',3); 
// MEMBER_ID 에 UNIQUE제약조건을 걸어놨기때문에 중복값(ADMIN) 삽입 불가능

INSERT INTO NQ_MEMBER VALUES(3,NULL,'1234','유저2',22);  
// UNIQUE 제약조건만 걸었기 때문에 NULL값은 들어감
INSERT INTO NQ_MEMBER VALUES(4,NULL,'4444','유저3',11);
// NULL값은 동등비교(=)가 안되기때문에 NULL값 추가로 들어감

NOT NULL, UNIQUE 지정된 컬럼에 둘 다 제약 조건 거는 방법

CREATE TABLE NQ_MEMBER2(
    MEMBER_NO NUMBER,
    MEMBER_ID VARCHAR2(20) UNIQUE NOT NULL, -- 동시에 2가지 제약조건 걸기
    MEMBER_PWD VARCHAR2(20) NOT NULL,
    MEMBER_NAME VARCHAR2(10),
    MEMBER_AGE NUMBER
);

테이블 레벨 에서 UNIQUE 제약조건 걸기

CREATE TABLE NQ_MEMBER3(
    MEMBER_NO NUMBER,
    MEMBER_ID VARCHAR2(20) NOT NULL,
    MEMBER_PWD VARCHAR2(20) NOT NULL,
    MEMBER_NAME VARCHAR2(10),
    MEMBER_AGE NUMBER,
    UNIQUE(MEMBER_ID) -- UNIQUE는 테이블레벨에서 설정 가능  = 다수의 컬럼에 UNIQUE 제약조건을 설정할 때 사용
);


// 다수 컬럼에 UNIQUE 제약조건 설정하기
// 다수 컬럼의 값이 일치해야 중복값으로 인식 -> 선언컬럼이 하나의 그룹으로 묶임
CREATE TABLE NQ_MEMBER4(
    MEMBER_NO NUMBER,
    MEMBER_ID VARCHAR2(20) NOT NULL,
    MEMBER_PWD VARCHAR2(20) NOT NULL,
    MEMBER_NAME VARCHAR2(10),
    MEMBER_AGE NUMBER,
    UNIQUE(MEMBER_ID, MEMBER_NAME) // 아이디와 이름 둘다 같아야 유니크 제약조건 걸림! 
    // 아이디만 다르거나 이름만 다르면 다른값으로 인식됨
);

SELECT * FROM NQ_MEMBER4;
INSERT INTO NQ_MEMBER4 VALUES(1,'ADMIN','1234','관리자',44);
INSERT INTO NQ_MEMBER4 VALUES(2,'ADMIN','3333','유저1',33); // 아이디는 같지만 이름이 다르기때문에 다른값이므로 값 삽입 가능
INSERT INTO NQ_MEMBER4 VALUES(3,'ADMIN','4444','관리자',24); // 아이디와 이름이 둘 다 중복되기때문에 값 삽입 불가능

✅ PRIMARY KEY

데이터(ROW) 구분하는 컬럼에 설정하는 제약조건
테이블의 컬럼 중 도메인이 중복값이 없고, NULL값을 허용하지 않을 때 그 컬럼에 설정
NOT NULL과 UNIQUE 제약 조건과, INDEX가 자동으로 설정
일반적으로 한개테이블에 한개 PK를 설정
다수 컬럼에 설정할 수도 있다(복합키)

CREATE TABLE PK_MEMBER
(
    MEMBER_NO NUMBER PRIMARY KEY, //기본키 설정 제약조건 (컬럼 레벨에서 설정)
    MEMBER_ID VARCHAR2(20) UNIQUE NOT NULL,
    MEMBER_PWD VARCHAR2(20) NOT NULL,
    MEMBER_NAME VARCHAR2(10),
    MEMBER_AGE NUMBER
);

CREATE TABLE PK_MEMBER1(
    MEMBER_NO NUMBER,
    MEMBER_ID VARCHAR2(20) UNIQUE NOT NULL,
    MEMBER_PWD VARCHAR2(20) NOT NULL,
    MEMBER_NAME VARCHAR2(10),
    MEMBER_AGE NUMBER,
    PRIMARY KEY(MEMBER_NO)  // 기본키 설정 제약조건 (테이블 레벨에서 설정)
);

// PRIMARY KEY를 다수컬럼에 설정할 수 있다. -> 복합키
CREATE TABLE PK_MEMBER2(
    MEMBER_NO NUMBER, 
    MEMBER_ID VARCHAR2(20),
    MEMBER_PWD VARCHAR2(20) NOT NULL,
    MEMBER_NAME VARCHAR2(10),
    MEMBER_AGE NUMBER,
    PRIMARY KEY(MEMBER_NO, MEMBER_ID)  // 두개의 컬럼을 기본키로 설정
);

INSERT INTO PK_MEMBER2 VALUES(1,'USER01','1111','유저1',33); // 갑 삽잆 가능
INSERT INTO PK_MEMBER2 VALUES(2,'USER01','2222','유저2',22); // 값 삽입 가능 (NO와 ID 둘다 동일해야 유니크조건에 걸림)
INSERT INTO PK_MEMBER2 VALUES(1,'USER01','2222','유저2',22); // 값 삽입 불가능  (NO이나 ID 둘중 하나 다르면 다른값으로 인식)
INSERT INTO PK_MEMBER2 VALUES(NULL,'USER01','2222','유저2',22); // NOT NULL 제약조건은 둘중 하나라도 NULL이면 값 삽입 불가능
INSERT INTO PK_MEMBER2 VALUES(1,NULL,'2222','유저2',22); // NOT NULL 제약조건은 둘중 하나라도 NULL이면 값 삽입 불가능
💡💡 테이블레벨 에서는 NOT NULL은 사용 불가능한 이유 
-> NOT NULL은 하나라도 NULL이면 위배되기 때문에 (테이블 레벨에서 묶어서 조건 처리 불가능)

✅ FOREGIN KEY

다른 테이블에 있는 데이터를 가져와 사용하는 것 (참조)
다른테이블 지정된 컬럼은 중복이 있으면 안됨!(UNIQUE 제약조건이나 PK 제약조건이 설정된 컬럼)
참조 관계를 설정하면 부모(참조 되는 테이블)-자식 관계(참조 하는 테이블) 관계가 설정이 됨

CREATE TABLE BOARD( // 게시물 테이블
    BOARD_NO NUMBER PRIMARY KEY, 
    BOART_TITLE VARCHAR2(200) NOT NULL,
    BOART_CONTENT VARCHAR2(3000),
    BOART_WRITER VARCHAR2(10) NOT NULL,
    BOART_DATE DATE
);

CREATE TABLE BOARD_COMMENT( -- 댓글테이블
    COMMENT_NO NUMBER PRIMARY KEY,
    COMMENT_CONTENT VARCHAR2(800),
    COMMENT_WRITER VARCHAR2(10),
    COMMENT_DATE DATE,
    BOARD_REF NUMBER REFERENCES BOARD(BOARD_NO) // 게시물을 연결하는 참조변수
);

INSERT INTO BOARD VALUES(1,'제목1','나는','관리자',SYSDATE);
INSERT INTO BOARD VALUES(2,'제목2','경기도','사용자2',SYSDATE);
INSERT INTO BOARD VALUES(3,'제목3','안양시 ','사용자3',SYSDATE);
INSERT INTO BOARD VALUES(4,'제목4','만안구에산다','사용자4',SYSDATE);


INSERT INTO BOARD_COMMENT VALUES(1,'3번과 연결할래요','커멘트1',SYSDATE,3);
INSERT INTO BOARD_COMMENT VALUES(2,'2번과 연결할래요','커멘트2',SYSDATE,2);
INSERT INTO BOARD_COMMENT VALUES(3,'1번과 연결할래요','커멘트3',SYSDATE,1);
// INSERT INTO BOARD_COMMENT VALUES(3,'4번과 연결할래요','커멘트4',SYSDATE,4);  
// 부모키에 4번이 없기 때문에 오류뜸

SELECT *
FROM BOARD
    JOIN BOARD_COMMENT ON BOARD_NO = BOARD_REF; 
    //  게시물테이블의 게시물넘버와 댓글 테이블의 참조변수와 같은 것끼리 연결
  	//  밑에 사진 예시 참고
    
// FK가 설정된 컬럼에 NULL??? 저장된다. 저장하지 않으려면
// BOARD_COMMENT에 NOT NULL 제약 조건을 넣어줘야함
INSERT INTO BOARD_COMMENT VALUES(4,'NULL들어가니?','최솔',SYSDATE,NULL);  -- 값 저장됨
);

FK를 설정해서 테이블간 관계가 설정이되면 참조되고 있는 부모테이블의 ROW를 함부러 삭제할 수 없다.

DELETE FROM BOARD WHERE BOARD_NO = 3; // BOARD_NO = 3인 값은 참조되고 있기 때문에 삭제 불가능

// FK 설정할 때 삭제에 대한 옵션을 설정할 수 있다.
-- ON DELETE SET NULL : 참조컬럼을 NULL 값으로 수정 * 참조컬럼에 NOT NULL 제약조건이 있으면 안된다.  
// EX) 남은 데이터를 어디서든 써야할때 남김(회원 테이블과),(구매테이블) 관계
-- ON DELETE CASCADE : 참조되는 부모데이터가 삭제되면 값이 삭제버림 
// EX) 댓글테이블과, 게시물테이블 관계

CREATE TABLE BOARD_COMMENT2( -- 댓글테이블
    COMMENT_NO NUMBER PRIMARY KEY,
    COMMENT_CONTENT VARCHAR2(800),
    COMMENT_WRITER VARCHAR2(10),
    COMMENT_DATE DATE,
    BOARD_REF NUMBER REFERENCES BOARD(BOARD_NO) ON DELETE SET NULL // 게시물을 연결하는 참조변수
);    
  
INSERT INTO BOARD VALUES(6,'냉무',NULL,'유병승',SYSDATE);
INSERT INTO BOARD_COMMENT2 VALUES(8,'SET NULL','유병승',SYSDATE,6);
DELETE FROM BOARD WHERE BOARD_NO = 6;
// 위 결과로 BOARD 테이블의 BOARD_NO 가 6번인 로우는 삭제되며 
// BOARD_COMMENT2의 테이블에서는 BOARD_REF가 NULL인 상태로만 바뀜 (로우 자체가 삭제되지 X)
// 밑에 사진참고
    
    
CREATE TABLE BOARD_COMMENT3( -- 댓글테이블
    COMMENT_NO NUMBER PRIMARY KEY,
    COMMENT_CONTENT VARCHAR2(800),
    COMMENT_WRITER VARCHAR2(10),
    COMMENT_DATE DATE,
    BOARD_REF NUMBER REFERENCES BOARD(BOARD_NO) ON DELETE CASCADE // 같이삭제됨
);    

INSERT INTO BOARD VALUES(6,'냉무',NULL,'유병승',SYSDATE);
INSERT INTO BOARD_COMMENT3 VALUES(8,'SET NULL','유병승',SYSDATE,6);
DELETE FROM BOARD WHERE BOARD_NO = 6;   
// 위 결과로 BOARD 테이블의 BOARD_NO 가 6번인 로우는 삭제되며 
// BOARD_COMMENT2의 테이블에 로우 값도 아예 삭제된다

✅ CHECK

컬럼에 지정한 값만 저장할 수 있게 하는 제약조건 (컬럼 레벨에서만 가능)

CREATE TABLE PERSON(
    NAME VARCHAR2(20),
    AGE NUMBER CHECK(AGE > 0) NOT NULL, // 나이가 0살 초과인 값만 저장됨
    GENDER VARCHAR2(5) CHECK(GENDER IN('남','여')) // 남과 여만 저장됨
);

✅ DEFAULT

기본 값 설정해주는 명령어
DEFAULT와 제약조건이 같이 있을 경우에는 DEFAULT가 우선수위 더 먼저

CREATE TABLE DEFAULT_TEST(
    TEST_NO NUMBER PRIMARY KEY,
    TEST_DATE DATE DEFAULT SYSDATE,
    TEST_DATA VARCHAR2(20) DEFAULT '기본값'
);
INSERT INTO DEFAULT_TEST VALUES(1,DEFAULT,DEFAULT);  -> 1,오늘날짜,기본값 으로 저장됨
// 추가할 값이 없을때 DEFAULT 예약어를 사용해서 기본값을 넣을 수 있음
INSERT INTO DEFAULT_TEST VALUES(2,'23/02/04','데이터');
INSERT INTO DEFAULT_TEST(TEST_NO) VALUES(3); // TEST_NO값만 넣고 나머지는 DEFAULT 값으로

✅ CONSTRAINT

제약 조건 설정 이름 정하기
기본방식으로 제약조건을 설정하면 SYS00000으로 자동으로 설정

CREATE TABLE MEMBER_TEST(
    MEMBER_NO NUMBER CONSTRAINT MEMBER_NO_PK PRIMARY KEY, // 제약조건 이름 변경함 -> MEMBER_NO_PK
    MEMBER_ID VARCHAR2(20) CONSTRAINT MEMBER_ID_UQ UNIQUE NOT NULL,
    MEMBER_PWD VARCHAR2(20) CONSTRAINT MEMBER_PWD_NN NOT NULL,
    CONSTRAINT COMPOSE_UQ UNIQUE(MEMBER_NO, MEMBER_ID) // 테이블레벨에서 이름 바꾸는 방법

SELECT *
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'MEMBER_TEST';  // 밑에 사진 참고

✅ SELECT 문을 이용하여 테이블 복사

CREATE TABLE EMP_COPY
AS SELECT * FROM EMPLOYEE; // EMPLOYEE 모든 데이터를 복사하여 EMP_COPY 테이블을 새로 생성하면서 안으로 복사

CREATE TABLE EMP_SAL // 테이블 복사생성
AS SELECT E.*, (SELECT AVG(SALARY) FROM EMPLOYEE WHERE DEPT_CODE = E.DEPT_CODE) AS SAL_DEPT_AVG
FROM EMPLOYEE E JOIN DEPARTMENT ON DEPT_ID = DEPT_CODE;

CREATE TABLE EMP_SAL2 // 빈테이블만 생성
AS SELECT E.*, (SELECT AVG(SALARY) FROM EMPLOYEE WHERE DEPT_CODE = E.DEPT_CODE) AS SAL_DEPT_AVG
FROM EMPLOYEE E JOIN DEPARTMENT ON DEPT_ID = DEPT_CODE WHERE 1=2;

모든 개념을 적용한 예제문제 풀이를 통해서 복습해보자!

-- TEST_MEMBER 테이블
-- MEMBER_CODE(NUMBER) - 기본키 -- 회원전용코드
-- MEMBER_ID (varchar2(20) ) - 중복금지, NULL값 허용금지 -- 회원 아이디
-- MEMBER_PWD (char(20)) - NULL 값 허용금지 -- 회원 비밀번호
-- MEMBER_NAME(nchar(10)) - 기본값 '아무개' -- 회원 이름
-- MEMBER_ADDR (char(50)) - NULL값 허용금지 -- 회원 거주지
-- GENDER (varchar2(5)) - '남' 혹은 '여'로만 입력 가능 -- 성별
-- PHONE(varchar2(20)) - NULL 값 허용금지 -- 회원 연락처
-- HEIGHT(NUMBER(5,2) - 130이상의 값만 입력가능 -- 회원키
CREATE TABLE TEST_MEMBER(
    MEMBER_CODE NUMBER CONSTRAINT PK_MEMBER_CODE PRIMARY KEY,
    MEMBER_ID VARCHAR2(20) NOT NULL UNIQUE,
    MEMBER_PWD CHAR(20) NOT NULL,
    MEMBER_NAME NCHAR(10) DEFAULT '아무개',
    MEMBER_ADDR CHAR(50) NOT NULL,
    GENDER VARCHAR2(5) CHECK(GENDER IN('남','여')),
    PHONE VARCHAR2(20) NOT NULL,
    HEIGHT NUMBER(5,2) CHECK(HEIGHT > 130)
);
COMMENT ON COLUMN TEST_MEMBER.MEMBER_CODE IS '회원 전용코드';  -- 커멘트 달기
COMMENT ON COLUMN TEST_MEMBER.MEMBER_ID IS '회원 아이디';
COMMENT ON COLUMN TEST_MEMBER.MEMBER_PWD IS '회원 비밀번호';
COMMENT ON COLUMN TEST_MEMBER.MEMBER_NAME IS '회원 이름';
COMMENT ON COLUMN TEST_MEMBER.MEMBER_ADDR IS '회원 거주지';
COMMENT ON COLUMN TEST_MEMBER.GENDER IS '성별';
COMMENT ON COLUMN TEST_MEMBER.PHONE IS '회원 연락처';
COMMENT ON COLUMN TEST_MEMBER.HEIGHT IS '회원 키';

CREATE TABLE TB_BOOK_TRANSLATOR(
    BOOK_NO VARCHAR2(10) CONSTRAINT FK_BOOK_TRANSLATOR_01 REFERENCES TB_BOOK(BOOK_NO) NOT NULL,
    WRITER_NO VARCHAR2(10) CONSTRAINT FK_BOOK_TRANSLATOR_02 REFERENCES TB_WRITER(WRITER_NO) NOT NULL,
    TRANS_LANG VARCHAR2(60),
    CONSTRAINT PK_BOOK_TRANSLATOR PRIMARY KEY(BOOK_NO, WRITER_NO)
);
// 기본키는 BOOK_NO 와 WRITER_NO가 한쌍이며
// BOOK_NO와 WRITER_NO에 각각 NOT NULL 조건이 있으므로, 그 컬럼들은 NULL 값이 들어갈 수 없음

최주영

우측 상단 햇님모양 클릭하셔서 무조건 야간모드로 봐주세요!!

오라클 제약조건

오라클

오라클 서브쿼리

오라클 DML (INSERT, UPDATE, DELETE)

0개의 댓글