07-05(월) DDL 제약조건

※ 개발 수업 정리글입니다. 꾸준히 정리할 예정이고 틀린 부분이 있다면 언제든지 댓글 환영입니다.

---

★생성된 제약조건 확인하는 sql★     [ USER_CONSTRAINTS 데이터사전 조회 ]

SELECT * FROM USER_CONSTRAINTS WHERE table_name='대문자테이블명' ;
-여러 테이블을 동시에 확인하고 싶으면 WHERE TABLE_NAME IN ('테이블1', '테이블2') ; 하기

---

< *에 표시할 컬럼>

CONSTRAINT_TYPE   : 제약조건 종류  ex) R , P 이런거..

CONSTRAINT_NAME : 제약조건명      ex) 테이블명_컬럼명_PK

R_CONSTRAINT_NAME : 뭘까 이건..........R인거 보니 참조해오는 테이블 쪽의 제약조건 명인 듯...

---

★ 컬럼의 제약조건 확인하는 sql★    [ USER_CONS_COLUMNS 데이터사전 조회 ]

SELECT * FROM USER_CONS_COLUMNS WHERE TABLE_NAME='대문자테이블명' ;

 

 

DDL테이블생성  CREATE TABLE 

CREATE TABLE [스키마].테이블명 (1개의컬럼명, 데이터타입 [조건]) ;

★자주 쓰이는 꼭 알아야할 테이블생성 데이터 타입★
CHAR(size)	고정길이
VARCHAR2	가변길이
NUMBER(p, s)	가변길이  p:전체자릿수  s:소수점자릿수
DATE	SYSDATE INSERT
ROWID	이런 거 있다는 개념만 잡고 나중에 배움

---

DDL   DEFAULT 옵션 사용

자동으로 기본값이 입력되어 빈칸으로 안 적어도 NULL값이 저장되는걸 방지

CREATE TABLE 테이블명

( 컬럼1 타입(n) DEFAULT 값1 ,

  컬럼2 타입(n) DEFAULT 값2 , .... ) ;

 

---

DDL   제약조건

-문제가 없는 데이터라는 걸 보장 받기 위해서 사용.

-컬럼레벨    : 컬럼 옆에 직접 제약조건을 각각 적음
 테이블레벨 : 컬럼 다 쓰고나서 컬럼들 마지막에 컬럼 순서대로 모아서 적음

-CONSTRAINT 제약조건명 앞에는 순서가 거의 비슷함

  CREATE TABLE 테이블명
  ( 컬럼명1  데이터타입 [컬럼레벨 제약조건 여부], 
    컬럼명2  데이터타입 ,
    컬럼명3  데이터타입 ,  ...
    [테이블레벨 제약조건 여부] ) ;  

★자주 쓰이는 꼭 알아야할 제약조건 타입★
종류	CONSTRAINT_TYPE 값	설명 : 테이블 내에서 해당 컬럼값이 ~
NOT NULL		NULL를 허용하지 않음 ★컬럼레벨만 지원★
UNIQUE	U	존재 안해도 되지만, 유일한 값 =>중복불가, NULL은 가능
PRIMARY KEY 기본키	P	꼭 존재해야 하고,    유일한 값 =>중복불가, NULL도 불가 (NOT NULL + UNIQUE)
FOREIGN KEY 외래키, 참조키	R	다른 테이블의 컬럼값을 참조 ex) DEPT 테이블에 DEPTNO 55가 없는데 참조키로 쓰면 안뜸
CHECK	C (NOT NULL CHECK)	값의 범위나 사용자 조건 지정

---

■ DDL 테이블 생성 시, PRIMARY KEY 제약조건

   -꼭 존재해야 하고, 유일한 값 => 중복불가, NULL불가  (NOT NULL + UNIQUE)

• <컬럼레벨 방식>  ---타입 옆에 이어 쓸 땐, 쉼표 ( , )를 안씀
  CREATE TABLE 테이블명
  ( 컬럼명1  데이터타입 CONSTRAINT 제약조건명 PRIMARY KEY , 
    컬럼명2  데이터타입, ... ) ;
  
  
• <테이블레벨 방식>  ---밑으로 몰아서 적어줄 땐, 꼭 쉼표 ( , )를 써줘야함. 이거 오류 자주내더라..
  CREATE TABLE 테이블명
  ( 컬럼명1  데이터타입, 
    컬럼명2  데이터타입, ...
    CONSTRAINT 제약조건명 PRIMARY KEY(컬럼명1),
    CONSTRAINT 제약조건명 제약조건타입 (컬럼명2), ...) ;  

 

 

(PK 예제) 

create table department3
(deptno number(2), dnaMe varchar2(15), loc varchar2(15),
constraint department3_deptno_pk primary key(DEPTNO, LOC)); --이럴땐 누가 기본키일까?

 

INSERT INTO DEPARTMENT3(DEPTNO,LOC) VALUES(10,'a');
INSERT INTO DEPARTMENT3(DEPTNO,LOC) VALUES(20,'a');
INSERT INTO DEPARTMENT3(DEPTNO,LOC) VALUES(30,'a'); 
--ㄴ여기까지 행 추가가 잘됐단 뜻은 LOC는 기본키가 아니라는것
INSERT INTO DEPARTMENT3(DEPTNO,LOC) VALUES(30,'a'); 
--ㄴ여기서 이걸로 다시 한번 더 했을때, 오류나면 누가 기본키인지 알 수 있고 DEPTNO가 기본키라는거임

 

---

■ DDL 테이블 생성 시, UNIQUE 제약조건

    -존재 안해도 되지만, 유일한 값 => 중복불가, NULL은 가능

• <컬럼레벨 방식>
  CREATE TABLE 테이블명
  ( 컬럼명1  데이터타입 CONSTRAINT 제약조건명 UNIQUE , 
    컬럼명2  데이터타입, ... ) ;
  
  
• <테이블레벨 방식>
  CREATE TABLE 테이블명
  ( 컬럼명1  데이터타입, 
    컬럼명2  데이터타입, ...
    CONSTRAINT 제약조건명  UNIQUE (컬럼명1, 컬럼명2, ...) ) ;  
  

 

(예제) 컬럼레벨로 테이블생성

CREATE TABLE DAPARTMENT4
(DEPTNO NUMBER(2)   CONSTRAINT DEPAERTMENT4_DEPTNO_PK PRIMARY KEY,
DNAME VARCHAR2(15) CONSTRAINT DEPARTMENT4_DNAME_UK UNIQUE,
LOC VARCHAR2(15) );

 

INSERT INTO DEPARTMENT4 (DEPTNO,DNAME,LOC)
VALUES(10,'개발','서울');
INSERT INTO DEPARTMENT4 (DEPTNO,DNAME,LOC)
VALUES(20,'개발','경기');                 --DNAME컬럼은 UNIQUE로 해놔서 중복이 안됨
INSERT INTO DEPARTMENT4 (DEPTNO,DNAME,LOC)
VALUES(30,NULL,'서울');                 --UNIQUE는 중복만 안되고,NULL값은 가능

 

(예제) 테이블레벨로 테이블생성

CREATE TABLE DEPARTMENT5
(DEPTNO NUMBER(2)    CONSTRAINT DEPAERTMENT5_DEPTNO_PK PRIMARY KEY,
DNAME VARCHAR2(15),
LOC VARCHAR2(15),

CONSTRAINT DEPARTMENT5_DNAME_UK UNIQUE(DNAME) );

---

■ DDL 테이블 생성 시, NOT NULL 제약조건

    -NULL를 허용하지 않음

• <컬럼레벨 방식만!!! 가능!!!>
  CREATE TABLE 테이블명
  ( 컬럼명1  데이터타입 CONSTRAINT 제약조건명 NOT NULL , 
    컬럼명2  데이터타입, ... ) ; 

(예제)

CREATE TABLE DEPARTMENT6
(DEPTNO NUMBER(2)    CONSTRAINT DEPAERTMENT6_DEPTNO_PK PRIMARY KEY,
DNAME VARCHAR2(15) CONSTRAINT DEPARTMENT6_DNAME_UK UNIQUE,
LOC VARCHAR2(15)     CONSTRAINT DEPARTMENT6_LOC_NN NOT NULL );

INSERT INTO DEPARTMENT6 (DEPTNO,DNAME,LOC)
VALUES(30,'인사',NULL);          --NOT NULL해놨기 때문에 안들어가짐

---

■ DDL 테이블 생성 시, CHECK 제약조건

   -값의 범위나 조건 지정

   -조건식에 IN이나 부등호나 비교연산자 전부 사용가능

• <컬럼레벨 방식>
  CREATE TABLE 테이블명
  ( 컬럼명1  데이터타입 CONSTRAINT 제약조건명 CHECK (컬럼명1의 조건식) , 
    컬럼명2  데이터타입, ... ) ;
  
  
• <테이블레벨 방식>
  CREATE TABLE 테이블명
  ( 컬럼명1  데이터타입, 
    CONSTRAINT 제약조건 CHECK (컬럼명1의 조건식) 
    컬럼명2  데이터타입, 
    CONSTRAINT 제약조건 CHECK (컬럼명2의 조건식) , ... ) ;  
  

(예제) 컬럼레벨

CREATE TABLE DEPARTMENT7
(DEPTNO NUMBER(2),
DNAME VARCHAR2(15) CONSTRAINT DEPARTMENT7_DNAME_CK CHECK(DNAME IN('개발','인사')), --개발이나 인사만가능
LOC VARCHAR2(15) ) ;

INSERT INTO DEPARTMENT7 (DEPTNO,DNAME,LOC) VALUES(10,'개발','서울');
INSERT INTO DEPARTMENT7 (DEPTNO,DNAME,LOC) VALUES(20,'인사','경기');

--오류 읽기

INSERT INTO DEPARTMENT7 (DEPTNO,DNAME,LOC) VALUES(10,'개발부','서울');

--ㄴcheck constraint ( ~ ) violated

INSERT INTO DEPARTMENT7 (DEPTNO,DNAME,LOC) VALUES(1000,'인사','a');

--ㄴvalue larger than specified precision allowed for this column

 

(예제) 테이블레벨

CREATE TABLE DEPARTMENT8
(DEPTNO NUMBER(2),
DNAME VARCHAR2(15),
LOC VARCHAR2(15),
CONSTRAINT DEPARTMENT8_DNAME_CK CHECK(DNAME IN('개발','인사'))
);

--오류 읽기

INSERT INTO DEPARTMENT8 (DEPTNO,DNAME,LOC) VALUES(30,'개발부','서울');

--ㄴcheck constraint ( ~ ) violated

---

■ DDL 테이블 생성 시, ★FOREIGN KEY 제약조건★

   -다른 테이블의 컬럼값을 참조

   -부모테이블의 행 삭제 시, 문제가 될 수 있음 (아래 테이블 삭제 - CASCADE, SET NULL)

• <컬럼레벨 문법>
  CREATE TABLE 테이블명
  ( 컬럼명1  데이터타입   CONSTRAINT 제약조건명 REFERENCES 부모테이블(참조할컬럼)
    컬럼명2  데이터타입, ... ) ;
• <테이블레벨 문법>
  CREATE TABLE 테이블명
  ( 컬럼명1  데이터타입,
    컬럼명2  데이터타입, ...
    CONSTRAINT 제약조건명 FOREIGN KEY(자식테이블 컬럼명) REFERENCE 부모테이블(참조할컬럼) , .. ) ;

(예제)

컬럼레벨
CREATE TABLE EMP02
(EMPNO NUMBER(4) CONSTRAINT EMP02_EMPNO_PK PRIMARY KEY,
ENAME VARCHAR2(15),
DEPTNO NUMBER(2) CONSTRAINT EMP02_DEPTNO_FK REFERENCES DEPT02(DEPTNO)
);
INSERT INTO EMP02 VALUES(1000,'John',10);
INSERT INTO EMP02 VALUES(2000,'Smith',20);
INSERT INTO EMP02 VALUES(3000,'Sam',NULL);
INSERT INTO EMP02 VALUES(4000,'MiKE',50);--에러뜸
COMMIT;

SELECT TABLE_NAME, CONSTRAINT_TYPE,
    CONSTRAINT_NAME, R_CONSTRAINT_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME IN('DEPT02','EMP02');

 

(예제) 테이블레벨
CREATE TABLE EMP03
(EMPNO NUMBER(4) CONSTRAINT EMP03_EMPNO_PK PRIMARY KEY,
ENAME VARCHAR2(15), DEPTNO NUMBER(2),
CONSTRAINT EMP03_DEPTNO_FK FOREIGN KEY(dEPTNO) REFERENCES DEPT02(DEPTNO)  --DEPTNO의 테이블레벨
);
INSERT INTO EMP03 VALUES(1000,'John',10);
INSERT INTO EMP03 VALUES(2000,'Smith',20);
INSERT INTO EMP03 VALUES(3000,'Sam',NULL);
INSERT INTO EMP03 VALUES(4000,'MiKE',50);--에러뜸
COMMIT;

(예제)

INSERT INTO DEPT02 VALUES(40,'aa','aa');  --②그래서DEPT02부터 부서번호40을 추가해주고
INSERT INTO EMP02 VALUES (100,'aaa',40);  --③EMP02에도 부서번호40을 추가가능
SELECT * FROM EMP02;
DELETE FROM DEPT02 WHERE DEPTNO=40 ;   --①DEPT02를 삭제하면 위에 EMP02아무리 넣으려해도 안됨
ROLLBACK;

 

(예제)

INSERT INTO EMP (EMPNO,ENAME,DEPTNO)
VALUES(9000,'John',50);

SELECT TABLE_NAME, CONSTRAINT_TYPE,
CONSTRAINT_NAME, R_CONSTRAINT_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME IN('DEPT','EMP');

CREATE TABLE DEPT02
(DEPTNO NUMBER(2) CONSTRAINT DEPT02_DEPTNO_PK PRIMARY KEY,
DNAME VARCHAR2(15),
LOC VARCHAR2(15)
);

INSERT INTO DEPT02 VALUES(10,'인사','서울');
INSERT INTO DEPT02 VALUES(20,'개발','광주');
INSERT INTO DEPT02 VALUES(30,'관리','부산');
INSERT INTO DEPT02 VALUES(40,'영업','경기');
COMMIT;

 

(예제) <500이하값만 입력 가능하게

CREATE TABLE SAWON_7
( S_NO NUMBER(2),
  S_NAME VARCHAR2(10),
  S_SAL NUMBER(10)
  CONSTRAINT SAWON_7_S_SAL_CK
  CHECK (S_SAL<500));
INSERT INTO SAWON_7 VALUES(1,'홍길동',600);  --오류남
INSERT INTO SAWON_7 VALUES(1,'홍길동',400);

 

---

DDL테이블 삭제   DROP TABLE 

• DROP TABLE 테이블명 [ CASCADE CONSTRAINTS ] ;
  참조키는 자동삭제가 안되기 때문에, SQL마지막에 CASCADE와 SET NULL 추가로 삭제가능
• 테이블 복구
  FLASHBACK TABLE 테이블명 TO BEFORE DROP ; 
  ㄴ뭐여 ㅋㅋ우린 복구가 안됨...암튼 위에 DROP TABLE만 잘 알고 있으면 됨

※※※계정은 삭제해도 [ SYS-다른 사용자-계정 ]에 가면 남아있으니까 이것까지 삭제해줘야함※※※

 

DELETE FROM DEPT02
WHERE DEPTNO=10;
CREATE TABLE EMP02
(EMPNO NUMBER(4) CONSTRAINT EMP02_EMPNO_PK PRIMARY KEY,
ENAME VARCHAR2(15),
DEPTNO NUMBER(2) 
CONSTRAINT EMP02_DEPTNO_FK REFERENCES DEPT02(DEPTNO)
ON DELETE CASCADE
);

 

 

(그냥PPT 예제 DEFAULT)

CREATE TABLE DEF_TABLE
(NUM NUMBER(2), WRITEDAY DATE );
CREATE TABLE DEF_TABLE2
(NUM NUMBER(2), WRITEDAY DATE DEFAULT SYSDATE );

INSERT INTO DEF_TABLE(NUM) VALUES(1);
INSERT INTO DEF_TABLE2(NUM) VALUES(1);
INSERT INTO DEF_TABLE2(NUM,WRITEDAY) VALUES(1, DEFAULT);

 

(그냥PPT 예제 CTAS씨탁스)

CREATE TABLE DEPTB(NO, NAME)
AS
SELECT DEPTNO, DNAME FROM DEPT;
SELECT COUNT(*) FROM DEPTB;

 

 

※※FOREIGN KEY 참조무결성 제약조건※※

- 부모테이블의 행 삭제 시, 문제가 될 수 있어서 아래 2가지 방법으로 해결

DELETE FROM DEPT02 WHERE DEPTNO=10;  --오류뜸

 

1.  ~  ON DELETE CASCADE ; 
   - 부모테이블의 행이 삭제되면, 자식테이블의 행도 연쇄 삭제
   
   (예제)
   DELETE FROM DEPT02
   WHERE DEPTNO=10;
   CREATE TABLE EMP02
   (EMPNO NUMBER(4) CONSTRAINT EMP02_EMPNO_PK PRIMARY KEY,
   ENAME VARCHAR2(15),
   DEPTNO NUMBER(2) 
   CONSTRAINT EMP02_DEPTNO_FK REFERENCES DEPT02(DEPTNO)
   ON DELETE CASCADE
   );
   
   
2.  ~  ON DELETE SET NULL ;
   - 부모테이블의 행이 삭제되면, 자식테이블의 컬럼값을 NULL로 설정
   
   (예제)
   CREATE TABLE EMP02
   (EMPNO NUMBER(4) 
   CONSTRAINT EMP02_EMPNO_PK PRIMARY KEY,
   ENAME VARCHAR2(15),
   DEPTNO NUMBER(2) 
   CONSTRAINT EMP02_DEPTNO_FK REFERENCES DEPT02(DEPTNO) ON DELETE SET NULL
   );

※※※테이블 완전삭제 : DROP TABLE 테이블명 PURGE ;※※※

 

 

점심먹고 왔다눙

★★★★★★★★★★웤샵인듯?★★★★★★★★★★

 

---

DDL테이블 변경   ALTER TABLE 

테이블구조 변경 실습을 위한 EMP04테이블 생성

CREATE TABLE EMP04 AS SELECT* FROM EMP; 

■ 컬럼추가
ALTER TABLE 테이블명 ADD ( 컬럼명1 데이터타입 , 컬럼명2 데이터타입, ...) ;
ex) ALTER TABLE EMP04 ADD (EMAIL VARCHAR2(10),ADDRESS VARCHAR2(20));

■ 컬럼변경
ALTER TABLE 테이블명 MODIFY ( 컬럼명1 데이터타입 , 컬럼명2 데이터타입, ...) ;
ㄴ
ex) ALTER TABLE EMP04 MODIFY (EMAIL VARCHAR2(40) );

■ 컬럼삭제
ALTER TABLE 테이블명 DROP ( 컬럼명 ) ; 
(책예제)  ALTER TABLE EMP04 DROP(EMAIL);

 

 

■ 제약조건 추가

• <NOT NULL 제약조건 추가>
  ALTER TABLE 테이블명 MODIFY ( 컬럼명 테이터타입 [CONSTRAINT 제약조건명] NOT NULL) ;
  ┌아래 형식은 테이블레벨로 제약조건을 설정하는 문법이기 때문에
  │컬럼레벨만 가능한 'NOT NULL' 제약조건은 이걸 쓸 수가 없어서 MODIFY를 써줘야함
• <그 외 제약조건 추가>
  ALTER TABLE 테이블명 ADD [CONSTRAINT 제약조건명] 그외제약조건타입(컬럼명) ;

 

(책예제) CREATE TABLE DEPT03
(DEPTNO NUMBER(2),
 DNAME VARCHAR2(15),
 LOC VARCHAR2(15)  );  --->전부 제약조건 없이 컬럼 설정하고 만듦
 
ALTER TABLE DEPT03 ADD CONSTRAINT DEPT03_DEPTNO_PK PRIMARY KEY(DEPTNO);

 

■ 제약조건 삭제

ALTER TABLE 테이블명 DROP PRIMARY KEY(컬럼명)    --원래 뒤에 제약조건명 써야하는데

                                         UNIQUE KEY (컬럼명)    --기본키,유니크키는 생략가능
                                         CONSTRAINT 제약조건명 [CASCADE] ; --제약조건명을 써서 삭제

 

 

CREATE TABLE DEPT03
(DEPTNO NUMBER(2),
 DNAME VARCHAR2(15),
 LOC VARCHAR2(15)
 );
 
ALTER TABLE DEPT03
ADD CONSTRAINT DEPT03_DEPTNO_PK PRIMARY KEY(DEPTNO);

ALTER TABLE DEPT03
DROP PRIMARY KEY;

CREATE TABLE DEPT04
AS
SELECT * FROM DEPT
WHERE 1=2;

 

CREATE TABLE EMP05
AS
SELECT*FROM EMP;
SELECT*FROM EMP05;
ALTER TABLE DEPT04 ADD CONSTRAINT DEPT04_DEPTNO_PK PRIMARY KEY (DEPTNO);
ALTER TABLE EMP05 ADD CONSTRAINT EMP05_DEPTNO_FK FOREIGN KEY(DEPTNO)
REFERENCES DEPT04(DEPTNO) ON DELETE SET NULL;  ---여기 책에 없음 다시 보고 다시 해보ㄱㅣ

 

------------------여까지 한듯? 중간에 녹화 못하긴 했는데 교재예제한거같으니까 교재예제 1번씩만 해보기

 

 

■ 제약조건 활성화 / 비활성화

ALTER TABLE 테이블명

DISABLE | ENABLE CONSTRAINT 제약조건명 [CASCADE] ;

-기존엔 STATUS 가

-비활성화 SQL 실행하면?

 

 

 

CASCADE 옵션

ALTER TABLE 테이블명 DROP ~ CASCADE;