[SQL]제약조건

/*★★★제약조건★★★
NOT NULL : 널값을 허용하지 않음
UNIQUE : 유일한 값
PRIMARY KEY : 기본키
FOREIGN KEY : 외래키(참조)
CHECK : 조건식 만족 데이터만 입력
*/

CREATE TABLE TABLE_NOTNULL(
LOGIN_ID VARCHAR(20) NOT NULL,
LOGIN_PWD VARCHAR(20) NOT NULL,
TEL VARCHAR(20));

INSERT INTO TABLE_NOTNULL (LOGIN_ID,LOGIN_PWD,TEL)
VALUES('TEST_ID_01','ACB',NULL);

/*14-6제약조건 이름 지정 */
CREATE TABLE TABLE_NOTNULL2(
LOGIN_ID VARCHAR(20) CONSTRAINT TBLNN2_LGNID_NN NOT NULL,
LOGIN_PWD VARCHAR(20) CONSTRAINT TBLNN2_LGNPW_NN NOT NULL,
TEL VARCHAR(20));

 

 

/*14-7 이미 생성된 TEL열에 NOT NULL 제약조건 추가*/
SELECT * FROM TABLE_NOTNULL;
ALTER TABLE TABLE_NOTNULL MODIFY(TEL NOT NULL);

 

/*14-9*/
SELECT OWNER,CONSTRAINT_NAME,
CONSTRAINT_TYPE,TABLE_NAME
FROM USER_CONSTRAINTS;

 

/*14-10*/
SELECT * FROM TABLE_NOTNULL2;
DESC TABLE_NOTNULL2;

ALTER TABLE TABLE_NOTNULL2
MODIFY(TEL CONSTRAINT TBLNN_TEL_NN NOT NULL);

 

/*14-12 이미 생성된 제약조건 이름변경(RENAME .. TO)*/
ALTER TABLE TABLE_NOTNULL2 RENAME CONSTRAINT TBLNN_TEL_NN TO TBLNN2_TEL_NN;

 

/*14-13 제약조건 삭제*/
ALTER TABLE TABLE_NOTNULL2 DROP CONSTRAINT TBLNN2_TEL_NN;

 

/*UNIQUE문 중복되지 않는 값 제약조건*/
CREATE TABLE TABLE_UNIQUE(
LOGIN_ID VARCHAR2(20) UNIQUE,
LOGIN_PWD VARCHAR2(20) NOT NULL,
TEL VARCHAR2(20)
);

DESC TABLE_UNIQUE;

 

CREATE TABLE TABLE_UNIQUE2(
LOGIN_ID VARCHAR2(20) CONSTRAINT TBLUNQ2_LGNID_UNQ UNIQUE,
LOGIN_PWD VARCHAR2(20) CONSTRAINT TBLUNQ2_LGNID_NN NOT NULL,
TEL VARCHAR2(20)
);

 

SELECT OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME
FROM USER_CONSTRAINTS WHERE TABLE_NAME LIKE 'TABLE_UNIQUE%';

 

/*14-23제약조건 추가(값이 이미 있으면 실패) 그래서 널로 만들어주고 추가*/
ALTER TABLE TABLE_UNIQUE
MODIFY(TEL UNIQUE);

UPDATE TABLE_UNIQUE SET TEL=NULL;

 

/*PRIMARY KEY(유일하게 하나만 있는 값(UUNIQUE + NOTNULL)*/
CREATE TABLE TABLE_PK(
LOGIN_ID VARCHAR2(20) PRIMARY KEY,
LOGIN_PWD VARCHAR2(20) NOT NULL,
TEL VARCHAR2(20)
);

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

 

/*14-31)PRIMARY KEY로 자동생성된 INDEX 확인*/
SELECT INDEX_NAME,TABLE_OWNER,TABLE_NAME FROM USER_INDEXES WHERE TABLE_NAME LIKE 'TABLE_PK%';

 

/*<14-5>FOREIGN KEY(외래키)*/
SELECT OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,R_OWNER,R_CONSTRAINT_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME IN('EMP','DEPT');

 

/*14-38 FOREIGN키가 참조하는 열에 존재하지 않는 데이터 입력하기-->오류: 부모키가 없습니다(50번 부서가 없음)*/
INSERT INTO EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
VALUES (9999,'홍길동','CLERK','7788',TO_DATE('2017/11/11','YYYY/MM/DD'),1200,NULL,50);
INSERT INTO EMP_TEMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
VALUES (9999,'홍길동','CLERK','7788',TO_DATE('2017/11/11','YYYY/MM/DD'),1200,NULL,20);
SELECT * FROM EMP_TEMP;

 

/*DEPT_FK,EMP_FK테이블 생성*/
CREATE TABLE DEPT_FK(
DEPTNO NUMBER(2) CONSTRAINT DEPTFK_DEPTNO_PK PRIMARY KEY,
DNAME VARCHAR2 (14),
LOC VARCHAR2(13));

CREATE TABLE EMP_FK(
EMPNO NUMBER(4) CONSTRAINT EMPFK_EMPNO_PK PRIMARY KEY,
ENAME VARCHAR2 (10),
JOB VARCHAR2 (9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
CONSTRAINT EMPFK_DEPTNO_FK
REFERENCES DEPT_FK(DEPTNO)

ON DELETE CASCADE /*참조하는 것도 함께 삭제*/
/*ON DELETE SET NULL : 삭제할때 참조하는 데이터를 NULL로 수정*/
);

 

/*EMP_FK 테이블에 데이터 삽입-->오류남 아무것도없어서 그래서 밑에서 먼저 삽입*/
INSERT INTO EMP_FK(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
VALUES (9999,'TESE_DNAME','TEST_JOB',NULL,TO_DATE('2001/01/01','YYYY/MM/DD'),3000,NULL,10);
SELECT * FROM DEPT_FK;
SELECT * FROM EMP_FK;

/*먼저*/
INSERT INTO DEPT_FK VALUES (10,'TEST_DNAME','TEST_LOC');

/*DEPT_FK 데이터 삭제-->안됨 참조하는 EMP_FK가 있음*/
DELETE FROM DEPT_FK WHERE DEPTNO=10;

 

/*삭제하려면 (CASCADE,SET NULL)
1.현재 삭제하려는 열 값을 참조하는 데이터를 먼저삭제한다.
2.삭제하려는 열 값을 참조하는 데이터를 수정
3.현재 삭제하려는 열을 참조하는 자식 테이블의 FOREIGN KET 제약조건을 해제*/
DROP TABLE EMP_FK;
DROP TABLE DEPT_FK;

 

/*<14-6> CHECK: 데이터 패턴,범위 정의*/
CREATE TABLE TABLE_CHECK(
LOGIN_ID VARCHAR2(20) CONSTRAINT TBLCK_LOGINID_PK PRIMARY KEY,
LOGIN_PWD VARCHAR2(20) CONSTRAINT TBLCK_LOGINPWD_CK CHECK(LENGTH(LOGIN_PWD)>3),
TEL VARCHAR2(20)
);
DESC TABLE_CHECK;

 

/*CHECK제약조건이 맞지않는 예-->3자리가 넘지않기때문 */
INSERT INTO TABLE_CHECK
VALUES('TEST_ID','123','010-1234-5678');

 

/*정상적 예*/
INSERT INTO TABLE_CHECK
VALUES('TEST_ID','1234','010-1234-5678');

DESC TABLE_CHECK;

SELECT OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME
FROM USER_CONSTRAINTS WHERE TABLE_NAME LIKE 'TABLE_CHECK';

 

/*<14-7>DEFAULT : 기본 값을 정함*/
CREATE TABLE TABLE_CHECK(
LOGIN_ID VARCHAR2(20) CONSTRAINT TBLCK_LOGINID_PK PRIMARY KEY,
LOGIN_PWD VARCHAR2(20) CONSTRAINT TBLCK_LOGINPWD_CK
CHECK(LENGTH(LOGIN_PWD)>3),
TEL VARCHAR2(20) DEFAULT '010-0000-0000'
);

 

제약조건 비활성화,활성화 DISABLE,ENABLE*/