[Oracle]데이터 무결성 제약 조건

2020/04/07 - [HTML/DataBase] - [Oracle]데이터 무결성을 위한 제약조건(CONSTRAINT)

[Oracle]데이터 무결성을 위한 제약조건(CONSTRAINT)

개념 정의 게시물😃

---

데이터 무결성 제약 조건

 

테이블에 유효하지 않은 (부적절한) 데이터가 입력되는 것을 방지하기 위해서 테이블을 생성할 때 각 컬럼에 대해 정의하는 여러가지 규칙.

▼

PRIMARY KEY :

 

-테이블에 기본키를 생성한다.

-PRIMARY KEY 제약조건을 지정한 컬럼은 중복된 데이터를 저장하지 못하도록 제약을 하기 때문에 컬럼에 고유성을 부여한다.

-테이블의 각 행을 식별하기 위한 것으로 기본키에 속하는 컬럼은 NULL과 중복된 값을 모두 허용하지 않는다.

-NOT NULL조건과 UNIQUE 조건을 결합한 형태

 

**

|PRIMARY KEY 지정하기(1)|

--PRIMARY KEY 지정하기(1)

CREATE TABLE DEPT_SECOND(
    dno         NUMBER(2)   CONSTRAINT pk_dept_second PRIMARY KEY,
    dname       VARCHAR2(14),
    loc         VARCHAR2(13)
);

|PRIMARY KEY 지정하기(2)|

--PRIMARY KEY 지정하기(2)

CREATE TABLE DEPT_THIRD(
    dno         NUMBER(2),               
    dname    VARCHAR2(14),
    loc          VARCHAR2(13),
     CONSTRAINT pk_dept_third PRIMARY KEY(dno)
);

dno 컬럼이 PRIMARY KEY 조건에 맞춰 NOT NULL 처리 되었다.

/*결과값이 깔끔하게 정렬되어 보여지는 방법을 모르겠다아....🤔*/

---

NOT NULL :

 

-컬럼에 NULL 값을 포함하지 못하도록 지정, NOT NULL 제약조건을 사용하면 해당 컬럼이 NULL값을 가질 수 없게 된다.

-NOT NULL 제약조건은 컬럼 레벨로만 정의 할 수 있다.

**

CREATE TABLE CUSTOMER(
    id              VARCHAR2(20)    NOT NULL,
    pwd           VARCHAR2(20)    NOT NULL,
    name        VARCHAR2(20)    NOT NULL,
    phone       VARCHAR2(30),    
    address     VARCHAR2(20)
);

 

제약조건을 건 컬럼에는 NOT NULL 값이 잘 들어가있다.

**

| NOT NULL 에서 데이터 삽입하기 |

만약 NOT NULL 제약이 들어간 콜럼에 NULL값을 넣으면 ?

--NOT NULL에서 데이터 삽입하기
INSERT INTO customer
VALUES(NULL,'tiger','스캇','000-1111-2222','서울시');

NULL 값이 들어갈 수 없다는 오류가 뜬다. 제약조건이 제대로 실행된 것

**

 | NOT NULL 에서 중복 값 삽입하기 |

하지만 유일해야하는 ID라는 콜럼값에 NOT NULL 제약조건을 건다면 중복확인을 할 수 없다.

INSERT INTO customer
VALUES('scott','tiger','스캇','000-1111-2222','서울시'); --SCOTT 같은 값을 입력하면

INSERT INTO customer
VALUES('scott','tiger','스캇','000-1111-2222','제주도'); --NOT NULL은 근데 중복이 허용됨 유일한 값이어야는뎅

위처럼 중복된 값이 들어갈 수 있기 때문이다.

 

---

UNIQUE KEY :

 

-UNIQUE 제약 조건은 특정 컬럼에 모든 값이 고유하게 유지되도록 하는 고유키를 생성한다.

-즉, 테이블의 모든 로우에 대해서 유일한 값을 갖도록 한다.

-NULL은 UNIQUE 제약조건에 위반되지 않으므로 NULL 값을 허용한다.

**

CREATE TABLE CUSTOMER(
    id              VARCHAR2(20)   UNIQUE,
    pwd           VARCHAR2(20)    NOT NULL,
    name        VARCHAR2(20)    NOT NULL,
    phone       VARCHAR2(30),    
    address     VARCHAR2(20)
);

 

---

FOREIGN KEY :

 

-테이블에 외래키 제약 조건을 생성한다.

-왜래키 즉, 참조 무결성은 테이블과 테이블 사이의 주종 관계 설정을 위한 제약 조건이다.

-외래키는 부모 테이블의 컬럼 값을 참조하는 자식 테이블의 칼럼이다.

-부모키(참조키)는 자식 테이블에서 참조하는 부모 테이블의 컬럼이다. 부모키가 되기 위한 컬럼은 반드시 부모테이블의 기본키나 유일키로 설정되어 있어야 하다.

-참조되는(부모키) 테이블에 컬럼 값이 항상 존재해야 한다.

**

| FOREIGN KEY 지정하기 |

--왜래키 제약조건 지정하기
CREATE TABLE EMP_SECOND(
        eno         NUMBER(4)               CONSTRAINT  emp_second_eno_pk       PRIMARY KEY,
        ename    VARCHAR2(10),
        job         VARCHAR2(9),
        dno         NUMBER(2)               CONSTRAINT  emp_second_dno_fk       REFERENCES      department
);

외래키는 다음과 같이 "REFERENCES  부모테이블이름" 을 적어 지정한다. 

여기서 department 테이블의 dno콜럼의 자료는 

10~40까지인데  INSERT를 사용하여 해당되지 않는 값을 넣는다면

INSERT INTO emp_second
VALUES(8000,'박가연','학생',50); --department에 있는 dno는 10~40까지 밖에없으므로 오류

즉, 외래키를 지정하고 부모테이블에 해당되는 값이 들어가있지 않으면 부모테이블에서 값을 찾을 수 없다는 오류가 발생한다.

 

---

CHECK :

 

-컬럼에서 저장 가능한 데이터 값의 범위나 조건을 지정하여 설정한 값만을 허용한다.

-CURRVAL, NEXTVAL, ROWNUM과 같은 의사 칼럼이나 SYSDATE, USER 와 같은 함수에는 사용할 수 없다.

**

| CHECK 제약 조건 지정하기 |

CREATE TABLE EMP_SECOND(
        eno         NUMBER(4)               CONSTRAINT  emp_second_eno_pk       PRIMARY KEY,
        ename    VARCHAR2(10),
        salary      NUMBER(7,2)            CONSTRAINT emp_second_salary_min   CHECK(salary>0)
);
COMMIT;

CHECK 제약조건은 CHECK명령어 다음 범위를 지정해준다.

만약 범위에 해당하지 않는 값을 INSERT하려한다면

INSERT INTO emp_second
VALUES(8000,'펭수',-200);

CHECK 제약조건에 의해 값을 삽입할 수 없다는 오류가 발생한다.

---

DEFAULT :

DEFAULT은 아무런 값을 입력 하지 않았을 때 DEFAULT 제약의 값이 입력된다.

**

| DEFAULT 제약 조건 지정하기 |

CREATE TABLE EMP_SECOND(
        eno         NUMBER(4)               CONSTRAINT  emp_second_eno_pk       PRIMARY KEY,
        ename    VARCHAR2(10),
        salary      NUMBER(7,2)            DEFAULT 3000
);
COMMIT;

salary 콜럼 값을 3000을 디폴트 값으로 설정한뒤

salary 값 없이 데이터를 입력하면 

INSERT INTO emp_second
(eno, ename)
VALUES(8000,'펭수');

데이터를 따로 입력하지 않았지만 3000이라는 값이 들어가있다.

단, 콜럼이름과 value 값을 명시적으로 지정해줘한다.

---

|제약 조건 이름 지정하기|

CONSTRAINT

제약 조건 이름을 직접 지정하기 위해서는 CONSTRAINT 라는 키워드와 함께 제약 조건 이름을 기술한다.

 column_name data_type CONSTRNAINT constraint_name CONSTRAINT_TYPE

제약조건 이름은 테이블명, 컬럼명, 제약조건 유형을 조합하여 명령한다. 

EX) [테이블명]_[컬럼명]_[제약조건유형] pk_

이름 짓기는 마음대로되 의미있게 짓는 것이 좋다.😃

**

CREATE TABLE CUSTOMER(
    id              VARCHAR2(20)   CONSTRAINT customer_id_uk UNIQUE,
    pwd           VARCHAR2(20)   CONSTRAINT customer_pwd_nn NOT NULL,
    name        VARCHAR2(20)   CONSTRAINT customer_name_nn NOT NULL,
    phone       VARCHAR2(30),    
    address     VARCHAR2(20)
);
COMMIT;

**

|제약조건 확인하기|

제약조건을 확인해보는 쿼리는 다음과 같다.

--제약조건확인하기
SELECT table_name, constraint_name
FROM user_constraints
WHERE table_name IN('CUSTOMER');

테이블이름과 제약조건을 콜럼으로 설정한후 

USER_constraints 데이터 사전을 이용해서 테이블 이름이 'CUSTOMER'인 테이블을 조건으로 검색한 것.

 

---

[2020/04/22-수요일(29일차)]