[ORACLE] 제약조건 CONSTRAINT

제약조건(CONSTRAINT)

제약조건(CONSTRAINT)

테이블의 속성을 지정할 때 해당 속성에 들어오는 값의 범위나 크기를 제외한 나머지에 대한 조건을 기술하는 것 NOT NULL 제약조건 (필수 입력) : 해당 속성의 값에 NULL값이 들어오면 안 되게끔 하는 조건 형식 : 테이블을 설정할 때 컬럼명 자료형 NOT NULL

데이터 무결성 : 데이터 베이스 내의 데이터에 대한 정확성, 일관성, 유효성, 신뢰성을 보장하기 위해 데이터 변경 혹은 수정 시 여러가지 제한을 두어 데이터의 정확성을 보증하는 것을 말한다. 관계형 괄리시스템(RDBMS)에서는 설계 시 무결성을 고려한 설계로 데이터를 보호할 필요가 있다. **무결 : 결점이나 결함이 없는 상태

--오류순서 자료형 > NOT NULL > CHECK > DEFAULT > PRIMARY

제약조건 (CONSTRAINT) 1) 개체 무결성( Entity integrity) => 기본 키(primary key => UNIQUE  + NOT NULL) ** UNIQUE  : 중복값x, NULL값o ** NOT NULL : NULL값x, 중복값o - 모든 테이블이 기본 키(primary key)로 선택된 필드(column)를 가져야 한다. - 기본 키로 선택된 필드는 고유한 값(Unique-중복된 값을 허용안함)을 가져야 하며, 빈값x(null)을 허용하지 않는다. - UNIQUE 제약 조건 또는  PRIMARY KEY 제약 조건 을 설정하여 테이블의 기본 키나 식별자로 사용되는 열의 무결성을 강제하도록 한다.

2) 참조 무결성 (Reference integrity) => 외래 키 (FOREIGN ) **부모-자식, 주종 관계의 테이블이 존재해야 한다. - 참조관계에 있는 두 테이블의 데이터가 항상 일관된 값을 갖도록 유지되는 것을 말한다. - 외래키와 관련있는 무결성. - 외래키 값은 NULL이거나 참조 릴레이션의 기본키 값과 동일해야함. - 즉 릴레이션(=테이블)은 참조할 수 없는 외래키 값을 가질 수 었다.

3) 도메인 무결성 (Domain integrity) : 열에 대한 무결성을 보장하기 위한 것으로 컬럼의 값이 NULL값을 허용하지 않거나, 데이터 타입이 적절한지 올바른 형식의 데이터가 저장되었는지 등을 확인하는 것이다. 자료형 지정, 체크(CHECK), 디폴드(DEFAULT), NOT NULL 등의 제약(CONSTRAINTS) 들로 이러한 도메인 무결성을 보장할 수 있다. 예를 들어, 생일 날짜 컬럼에 알파벳이 입력되는 경우 도메인 무결성을 위반하는 것이라 볼 수 있다. *도메인 : 속성(Column) 하나가 가질 수 있는 모든 값의 집합.

제약조건 확인
제약조건에 대한 전반적인 데이터를 가지고 있는 데이터사전(데이터 딕셔너리) 테이블 USER_CONSTRAINTS **데이터 딕셔너리 : 메타데이터(데이터를 위한 데이터)를 가지고 있는 테이블 P : PRIMARY KEY (UNIQUE + NOT NULL) R : FOREIGN KEY (REFERENCES) U : UNIQUE C : NOT NULL, CHECK	SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'EMP02'; SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME FROM USER_CONSTRAINTS; DESC USER_CONSTRAINTS;
제약조건 이름 설정	- 제약조건이름( CONSTRAINT_NAME ) 규칙 [테이블명]_[칼럼명]_[제약조건유형] ex)EMP04_EMPID_PK
제약조건 추가, 변경, 삭제
--제약조건 추가 형식 : ADD CONSTRAINT 제약조건이름 제약조건유형(컬럼명) --NOT NULL제약조건은 정식형식으로 기술이 불가하므로 ADD로 추가할 수 없다.	ALTER TABLE DEPT03 ADD CONSTRAINT DEPT03_DEPTNO_PK PRIMARY KEY(DEPTNO);
--제약조건 수정 형식 : MODIFY 컬럼명 추가할 제약조건	ALTER TABLE DEPT03 MODIFY DNAME NOT NULL;
--제약조건 삭제 형식 : DROP CONSTRAINT 제약조건명 ; **NOT NULL은 제약조건명을 자동으로 부여받는 것만 가능하므로, 제약조건 데이터사전이나 제약조건을 검색하여 이름을 찾은 후 삭제 가능.	ALTER TABLE DEPT03 DROP CONSTRAINT DEPT03_DEPTNO_PK;

 

PRIMARY KEY (UNIQUE + NOT NULL) 제약조건
--한 테이블안에는 반드시 조건 한 개만 존재해야 한다 --컬럼레벨 방식 기술시, 한 개의 컬럼에만 조건 사용가능 --테이블레벨 방식 기술시, 여러 개의 컬럼을 조합(복합키)하여 사용 가능.
- 정식형식 : CONSTRAINT 제약조건이름 제약조건유형 -컬럼 레벨 정의 방법 : 컬럼 안에 제약조건을 같이 기술하는 것	CREATE TABLE DEPT01 (     DEPTNO NUMBER(2) CONSTRAINT DEPT01_DEPTNO_PJ PRIMARY KEY,     DNAME VARCHAR2(15),     LOC VARCHAR2(15) );
- 복합키로 기본키를 지정 : 반드시 테이블 레벨 정의 방법 이용(컬럼 레벨 정의 방법 불가) : 컬럼을 정의하는 부분에 제약조건을 정의하는 방법	CREATE TABLE DEPT02 (     DEPTNO NUMBER(2),     DNAME VARCHAR2(15),     LOC VARCHAR2(15),     CONSTRAINT DEPT02_DNO_DNAM_PK PRIMARY KEY(DEPTNO, DNAME) );

 

CHECK (값의 범위나 조건을 지정) 제약조건
- 입력 값이 조건에 맞지 않으면 DB에서 받지 않는다. 오류 - 입력 값의 범위를 지정 할 수 있다. - 입력할 수 있는 값의 범위를 설정해 줌 - 제약조건값 AAAaaa 대소문자 가림. - 컬럼명 CONSTRAINT [제약조건명] [제약조건] (범위) - 컬럼명 [제약조건명] [제약조건] (범위) 형식 :  CREATE TABLE 테이블명 (     C1 VARCHAR2(20) CONSTRAINT 제약조건명 제약조건,      --제약조건에 이름 지정     C2 VARCHAR2(20) CHECK(값의 범위나 조건지정),      --이름을 지정하지 않는 경우     C3 VARCHAR2(20) ) ;	CREATE TABLE EMP05 (     EMPNO NUMBER(4) PRIMARY KEY,     ENAME VARCHAR2(10) NOT NULL,     GENDER VARCHAR2(1) CHECK (GENDER IN ('M','F'))     SAL NUMBER(7,2) CHECK(SAL >= 100 AND SAL <=1000) ) ; --오류순서 자료형 > NOT NULL > CHECK > DEFAULT > PRIMARY

트랜직션 - commit을 통해 내가 처리한 모든 작업을 한 번에 저장하는 과정

DDL- 자동 COMMIT;

FOREIGN (참조키, 외래키) 제약조건

- 부모의 기본키를 이용하여 자식 테이블에 일반 컬럼이나 기본키로 사용하는 방법 - 반드시 부모의 기본키로만 이용할 수 있다. - 반드시 부모의 기본키 속성(컬럼)안에 들어있는 값만을 사용  (단, NULL은 사용가능) - 부모의 기본키 중복값 사용가능 - 컬럼레벨방식 단축형식 : 자식의컬럼명 자료형 REFERENCES 부모테이블명(부모테이블의 기본키컬럼명) **자료형BYTE는 부모보다 커야함 정식형식 CONSTRAINT [테이블명]_[컬럼명]_[제약조건명칭] FOREIGN KEY (자식테이블에서 부모키와 연결할 컬럼명) REFERENCES 부모테이블명 (부모테이블의 기본키컬럼명); --부모키를 참조하는 자식키가 있는 경우, 부모키가 있는 테이블을 먼저 삭제할 수 없다. 부모테이블 삭제시 DROP TABLE DEPT01 CASCADE CONSTRAINTS; 연관된 거 삭제

CREATE TABLE EMP06 (     EMPNO NUMBER(4) PRIMARY KEY,     ENAME VARCHAR2(10) NOT NULL,     JOB VARCHAR2(9),     DEPTNO NUMBER(4) REFERENCES DEPT01(DEPTNO) ); INSERT ALL INTO DEPT01 VALUES (DEPTNO, DNAME, LOC) SELECT * FROM DEPT; CREATE TABLE EMP07 (     EMPNO NUMBER(4) PRIMARY KEY,     ENAME VARCHAR2(10) NOT NULL,     JOB VARCHAR2(9),     DEPTNO NUMBER(4),     CONSTRAINT EMP07_DEPTNO_FK FOREIGN KEY(DEPTNO) REFERENCES DEPT01 (DEPTNO) );

DEFAULT 제약조건

- 해당 속성(컬럼)에 아무런 데이터를 넣지 않는 경우 - 기본값을 설정할 수 있는 조건 - 형식 : 컬럼명 자료형 DEFAULT 해당 자료형에 맞는 값 ex) 컬럼명 자료형 DEFAULT 0  컬럼명 자료형 DEFAULT '가' 컬럼명 자료형 DEFAULT SYSDATE **조건 : 반드시 제약조건 중 가장 처음에 기술해야 한다. UNIQUE NOT NULL or NOT NULL UNIQUE

CREATE TABLE DEPT07 (     DEPTNO NUMBER(2) DEFAULT 0 PRIMARY KEY,     DNAME VARCHAR2(5 CHAR) NOT NULL,     LOC VARCHAR2(15),     LOCID NUMBER(1) DEFAULT 0 ); INSERT INTO DEPT07 VALUES (NULL, '테스트', '테스트',5); --NOT NULL INSERT INTO DEPT07(DNAME, LOC, LOCID) VALUES ('테스트', '테스트',5); INSERT INTO DEPT07(DEPTNO, DNAME, LOC, LOCID) VALUES (1, '테스트', '테스트',NULL); INSERT INTO DEPT07(DEPTNO, DNAME, LOC ) VALUES (2, '테스트', '테스트');

CREATE TABLE DEPT07 (
    DEPTNO NUMBER(2) DEFAULT 0 PRIMARY KEY,
    DNAME VARCHAR2(5 CHAR) NOT NULL,
    LOC VARCHAR2(15),
    LOCID NUMBER(1) DEFAULT 0
);

INSERT INTO DEPT07 VALUES (NULL, '테스트', '테스트',5); --NOT NULL
INSERT INTO DEPT07(DNAME, LOC, LOCID) VALUES ('테스트', '테스트',5);
INSERT INTO DEPT07(DEPTNO, DNAME, LOC, LOCID) VALUES (1, '테스트', '테스트',NULL);
INSERT INTO DEPT07(DEPTNO, DNAME, LOC ) VALUES (2, '테스트', '테스트');​

 

키의 종류
1) 슈퍼키 (Super Key) - 슈퍼 키는 테이블에 존재하는 필드들의 부분집합으로써, 유일성을 만족해야 한다.	ex) [학생정보테이블] - 종테이블 학번 - 기본키 아이디 - 대체키 닉네임 - 대체키 학부아이디 - 외래키 학과아이디 - 외래키 [학부정보테이블] - 주테이블 학부아이디111~999 - 주_기본키 학부명 :공학부 [학과정보테이블] - 주테이블 학과아이디1111~9999 - 주_기본키 학과명 : 수학과
2) 후보 키 (Candidate key) - 후보 키는 이름에서도 나타나듯이 기본 키가 될 수 있는 후보. 테이블에 존재하는 전체 필드의 부분집합으로써, 유일성(uniqueness)과 최소성(irreducibility)을 만족해야 한다. 유일성은 모든 레코드에서 해당 필드에 중복된 값이 나타나지 않는다는 속성이며, 최소성은 최소한의 필드로 레코드를 유일하게 구별할 수 있어야 한다는 속성이다.
3) 기본 키 (Primary key) - 테이블에서 특정 레코드를 구별하기 위해 후보 키 중에서 선택된 고유한 식별자 - 후보키와 같이 유일성과 최소성에 대한 속성을 가져야 하며 NULL값을 가질 수 없다 . - 기본 키를 설정할 때는 아래와 같은 두 가지 사항을 고려하는 것이 좋다. --값이 자주 변경되지 않는 것을 선택 : 기본 키로 선택된 필드는 값이 변경될 때마다 해당 값이 유일한지, NULL값은 아닌지를 검사해야 하기 때문에 값이 자주 변경되지 않는 필드를 기본 키로 설정하는 것이 바람직 하다. --값이 단순한 것을 선택 : 기본키로 선택된 필드에 저장되는 데이터가 많은 자릿수를 갖는 정수거나 많은 문자를 포함하는 문자열이라면, 기본 키를 비교할 때 그만큼 많은 비용이 소모될 것이다. 그러므로, 짧고 단순한 형식의 데이터를 저장하는 필드를 기본 키로 설정하는 것이 DBMS의 성능 측면에서 유리하다.
4) 대체 키 (Alternate key) - 후보 키 중에서 기본 키로 선택된 것들을 제외한 모든 키
5) 복합 키 (Composite key) - 두 개 이상의 필드를 포함하는 키 - 앞에서 예를 들은 { id, dept_id }와 같은 키는 복합키의 한 예시이다.
6) 외래 키 (Foreign key) - 한 테이블의 키 중에서 다른 테이블의 레코드를 유일하게 식별할 수 있는 키 - 한 테이블의 레코드에서 다른 테이블의 레코드를 참조하기 위해 이용
하나의 필드 또는 전체 필드의 부분집합으로 구성된다. 중복된 값과 NULL값을 가질 수 있다. 반드시 참조되는 테이블에서 유일한 값을 갖는 필드를 참조해야한다. 예를 들어, 외래키는 참조되는 테이블의 기본 키를 참조할 수도 있다. 참조되는 테이블에 존재하는 값만을 가져야 한다. NULL값은 실제로 존재하는 값은 아니지만, 많은 데이터베이스에서 NULL값은 예외로 한다.