[sql] 11. DDL (CREATE TABLE) | 제약조건

1. DDL - 데이타 정의어

• DATA DEFINITION LANGUAGE : 데이타 정의어
• 데이타베이스의 객체( 테이블 , 뷰 , 시퀀스 , 인덱스 , 동의어 , 사용자 등)을 관리하기 위한 명령

 

 

 

 

 

2. 테이블(TABLE)

1) 개념

• 데이타베이스에서 데이타(행)을 저장하기 위한 가장 기본적인 객체
• 📊테이블 생성 : 테이블 속성(ATTRIBUTE)의 집합 생성

2) 형식

CREATE TABLE 테이블명 (컬럼명 자료형[(크기)] [DEFAULT 기본값] [컬럼제약조건],
	컬럼명 자료형[(크기)] [DEFAULT 기본값] [컬럼제약조건], ...
	[,테이블 제약조건])

 

식별자 작성 규칙

• 테이블명 , 컬럼명 , 별칭 , 라벨명 등
• 영문자로 시작되며 1~30 범위의 문자들로 구성
• A~Z , 0~9 , _ , $ , # 문자들을 조합하여 작성
• 대소문자 미구분 : 스네이크 표기법을 사용하는 것을 권장
• 영문자외 다른 문자 사용 가능 - 비권장
• 키워드로 식별자를 선언할 경우 에러 발생 - " " 안에 표현하면 가능 하지만 비권장

자료형(DATATYPE)

• 컬럼에 저장 가능한 값을 표현하기 위한 키워드

숫자형

• NUMBER[(전체자릿수,소숫점자릿수)]
• 반드시 크기를 줄 필요는 없음!

문자형

• CHAR(크기)
  • 크기 - 1 ~ 2000(BYTE) >> 고정 길이 (무조건 크기만큼 할당)
  • 단점 - 불필요하게 낭비, 장점 - 빠름
• VARCHAR2(크기)
  • 크기- 1 ~ 4000(BYTE) >> 가변 길이 (최대가 될 경우가 크기만큼)
•  LONG
  • 최대 2GBYTE까지 저장가능 >> 가변 길이
  • 테이블에 하나의 컬럼에만 설정 가능하며 정렬 불가능

• CLOB(CHARLONGBYTE)
  • 최대 4GBYTE까지 저장가능 >> 가변 길이
  • 인코딩 처리된 문자값이 저장된 텍스트 파일을 저장하기 위한 자료형 : 속도느려서 안씀
• BLOB(BYTELONGBYTE)
  • 최대 4GBYTE까지 저장가능 >> 가변 길이
  • 원시값이 저장된 일반(이진) 파일을 저장하기 위한 자료형 : 속도느려서 안씀

날짜형

• DATE - 날짜와 시간
• TIMESTAMP - 초(MS)단위시간

3) 기본값 설정 없이 테이블 생성

--1)
-- 기본값 설정 없이 테이블 생성
-- 😋SALESMAN 테이블 생성
-- 속성 : 사원번호(숫자형), 사원이름(문자형), 입사일(날짜형)
CREATE TABLE SALESMAN(NO NUMBER(4), NAME VARCHAR2(20), STARTDATE DATE);

-- 😋SALESMAN 테이블 생성되었는지 확인
-- 딕셔너리(DICTIONARY) : 시스템 정보를 제공하기 위한 가상의 테이블(뷰)
-- => USER_DICTIONARY : (일반사용자)에게만 제공하는 시스템 정보
-- => DBA_DICTIONARY : (관리자)에게만 제공하는 시스템 정보
-- => ALL_DICTIONARY : (모든사용자)에게 제공하는 시스템 정보


--USER_OBJECTS : 현재 접속 사용자의 객체 정보를 제공하는 딕셔너리
SELECT OBJECT_NAME FROM USER_OBJECTS WHERE OBJECT_TYPE='TABLE';
--USER_TABLES : 현재 접속 사용자의 테이블 정보를 제공하는 딕셔너리 - 권장
SELECT TABLE_NAME FROM USER_TABLES;
--USER_TABLES 딕셔너리 대신 동의어(SYNINYM)로 TABS 제공 - 권장
SELECT TABLE_NAME FROM TABS;



--2)
-- 😋SALESMAN테이블 구조 확인
DESC SALESMAN;

1) 딕셔너리 이용

2) 테이블 구조 확인

 

--3)
-- 😋SALESMAN 테이블에 행 삽입
--테이블 생성 시 제약조건을 설정하지 않은 경우 컬럼의 어떤 값을 전달하든 삽입 가능
-- => 데이타 무결성 위반 가능ㅜㅜㅜ
INSERT INTO SALESMAN VALUES(1000,'홍길동','00/04/18');
INSERT INTO SALESMAN VALUES(1000,'전우치','10/10/10');
SELECT * FROM SALESMAN;
COMMIT;

--4)
-- 😋SALESMAN 테이블에 행 삽입
-- => 컬럼을 생략하여 삽입 처리 : 생략된 컬럼에는 기본값(NULL)이 자동 전달
-- => 테이블 생성 시 컬럼 기본값을 설정하지 않은 경우 자동으로 NULL이 기본값으로 자동 설정
INSERT INTO SALESMAN(NO,NAME) VALUES(2000,'임꺽정');
SELECT * FROM SALESMAN;
COMMIT;

 

3)

4)

4) 기본값 설정한 테이블 생성

-- 기본값 설정한 테이블 생성 : 특별한 경우 아니면 테이블 생성 시 컬럼에 기본값 설정 안함
-- 😍MANAGER 테이블 생성
-- 속성: 사원번호(숫자형), 사원이름(문자형), 입사일(날짜형-기본값:현재), 급여(숫자형-기본값:1000)
CREATE TABLE MANAGER(NO NUMBER(2),
	NAME VARCHAR2(20),
    STARTDATE DATE DEFAULT SYSDATE,
    PAY NUMBER DEFAULT 1000
);



--1)
-- 😍MANAGER 테이블 목록 및 구조 확인
SELECT TABLE_NAME FROM USER_TABLES;
--2)
DESC MANAGER;
--3)
-- 📕USER_TAB_COLUMNS : 테이블의 컬럼 정보를 제공하는 딕셔너리
SELECT COLUMN_NAME, DATA_DEFAULT FROM USER_TAB_COLUMNS WHERE TABLE_NAME='MANAGER';

 

1) 딕셔너리 이용

2) 테이블 구조 확인

3) 딕셔너리 이용

--4)
-- 😍MANAGER 테이블 행 삽입
-- => 컬럼 생략 : 생략된 컬럼에는 기본값이 전달되어 삽입 처리
INSERT INTO MANAGER(NO,NAME) VALUES(1000,'홍길동');
-- => DEFAULT키워드를 사용하여 기본값을 전달받아 삽입 처리
INSERT INTO MANAGER VALUES(2000,'임꺽정',DEFAULT,DEFAULT);
SELECT * FROM MANAGER;
COMMIT;

 

 

 

 

 

 

 

 

3. 제약조건(CONSTRAINT)

• 컬럼에 비정상적인 값이 저장되는 것을 방지하기 위한 기능 - 데이타 무결성 유지
• 컬럼에 부여된 제약조건은 삭제 가능하지만, 변경은 불가능!!!

1) 컬럼 수준의 제약조건

• 테이블의 속성 선언시 컬럼에 제약조건 설정
• C(CHECK)
• U(UNIQUE)
• P(PRIMARY KEY) PK : 관계맺고 데이타 무결성 유지를 위해 - PK제약조건 될 수 있으면 설정권장
• C(NOTNULL)

2) 테이블 수준의 제약조건

• 테이블 선언시 테이블의 특정 컬럼에 제약조건 설정
• C(CHECK)
• U(UNIQUE)
• P(PRIMARY KEY) PK : 관계맺고 데이타 무결성 유지를 위해 - PK제약조건 될 수 있으면 설정권장
• R(REFERENCE)-(FOREIGN KEY) FK : 관계맺고 데이타 무결성 유지를 위해  - FK까지 설정 안하는 경우 많음

📕제약조건 딕셔너리

-- 📕USER_CONSTRAINTS : 테이블에 설정된 제약조건을 제공하는 딕셔너리

DESC USER_CONSTRAINTS;

	  이름                  널?       유형
	  ------------------- -------- --------------
	  OWNER                        VARCHAR2(128)
	  CONSTRAINT_NAME     NOT NULL VARCHAR2(128)📕
	  CONSTRAINT_TYPE              VARCHAR2(1)📕
	  TABLE_NAME          NOT NULL VARCHAR2(128)
(C)   SEARCH_CONDITION             LONG📕
	  SEARCH_CONDITION_VC          VARCHAR2(4000)
	  R_OWNER                      VARCHAR2(128)
(R)   R_CONSTRAINT_NAME            VARCHAR2(128)📕
	  DELETE_RULE                  VARCHAR2(9)
	  STATUS                       VARCHAR2(8)
	  DEFERRABLE                   VARCHAR2(14)
	  DEFERRED                     VARCHAR2(9)
	  VALIDATED                    VARCHAR2(13)
	  GENERATED                    VARCHAR2(14)
	  BAD                          VARCHAR2(3)
	  RELY                         VARCHAR2(4)
	  LAST_CHANGE                  DATE
	  INDEX_OWNER                  VARCHAR2(128)
	  INDEX_NAME                   VARCHAR2(128)
	  INVALID                      VARCHAR2(7)
	  VIEW_RELATED                 VARCHAR2(14)
	  ORIGIN_CON_ID                NUMBER
      
      
      
      
-- 📕사용한 딕셔너리
-- CONSTRAINT_NAME : 제약조건을 구분하기 위한 이름(식별자)
-- R_CONSTRAINT_NAME : 참조하는 부모 테이블의 컬럼에 설정된 PRIMAY KEY 제약조건의 이름
-- => 제약조건의 이름을 설정하지 않으면 SYS_XXXXXXX 형식으로 자동 설정

-- CONSTRAINT_TYPE : 제약조건의 종류
-- => C(CHECK) | U(UNIQUE) | P(PRIMARY KEY)| R(REFERENCE) - FOREIGN KEY

-- SEARCH_CONDITION : CHECK 제약조건으로 설정된 조건식

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, SEARCH_CONDITION
FROM USER_CONSTRAINTS WHERE TABLE_NAME='SAWON2';

3) CHECK (C)

• 컬럼값에 대한 조건을 제공하여 조건에 맞는 값만 저장되도록 설정하는 제약조건
• [컬럼수준] & [테이블수준]의 제약조건 가능
• INSERT & UPDATE 시 영향

--1) 
-- 🔒CHECK 제약조건 없이 테이블 생성한다면?
-- SAWON1 테이블 생성 - 사원번호(숫자형), 사원이름(문자형), 급여(숫자형)
CREATE TABLE SAWON1(NO NUMBER(4), NAME VARCHAR2(20), PAY NUMBER);
-- SAWON1 테이블에 행 삽입
INSERT INTO SAWON1 VALUES(1000,'홍길동',8000000);
INSERT INTO SAWON1 VALUES(2000,'임꺽정',800000); --잘못된 값을 저장하고 커밋까지 해버림ㅠ
SELECT * FROM SAWON1;
COMMIT;



--2)
-- 🔒[컬럼수준]의 CHECK 제약조건
--CHECK 제약조건을 설정하는 컬럼만으로 CHECK 제약조건의 조건식 작성(즉, PAY만 가지고 조건식 만들기 가능)
--SAWON2 테이블 생성 - 사원번호(숫자형), 사원이름(문자형), 급여(숫자형 - 최소급여:5000000)
CREATE TABLE SAWON2(NO NUMBER(4),NAME VARCHAR2(20),PAY NUMBER CHECK(PAY>=5000000));
--(PAY와 NAME 함께 가지고 조건식 만들기 불가능)
--CREATE TABLE SAWON2(NO NUMBER(4), NAME VARCHAR2(20),PAY NUMBER CHECK(NAME<>'홍길동' AND PAY>=5000000)); --ERROR

--제약조건 확인
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, SEARCH_CONDITION
FROM USER_CONSTRAINTS WHERE TABLE_NAME='SAWON2';
--SAWON2 테이블에 행 삽입
INSERT INTO SAWON2 VALUES(1000,'홍길동',8000000);
INSERT INTO SAWON2 VALUES(2000,'임꺽정',800000); --ERROR, CHECK 제약조건 위반
SELECT * FROM SAWON2;
COMMIT;




-- 🔒제약조건 설정 시 제약조건명 부여 권장
-- 제약조건을 설정할 경우 제약조건 관리를 효율적으로 하기 위해 제약조건의 이름을 명시하는 것을 권장
-- 🔒형식
컬럼명 자료형[(크기)] CONSTRAINT 제약조건명 제약조건



--3)
-- 🔒[컬럼수준]의  CHECK 제약조건 + [제약조건명 부여]
--SAWON3 테이블 생성 - 사원번호(숫자형), 사원이름(문자형), 급여(숫자형 - 최소급여:5000000)
CREATE TABLE SAWON3(NO NUMBER(4),NAME VARCHAR2(20),PAY NUMBER CONSTRAINT SAWON3_PAY_CHECK CHECK(PAY>=5000000));
--제약조건 확인
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, SEARCH_CONDITION FROM USER_CONSTRAINTS WHERE TABLE_NAME='SAWON3';



--4)
-- 🔒[테이블 수준]의  CHECK 제약조건
-- 모든 컬럼을 사용하여 CHECK 제약조건의 조건식 작성 가능 (컬럼만들고 나서 마지막에 제약조건 부여)
-- SAWON4 테이블 생성 - 사원번호(숫자형), 사원이름(문자형), 급여(숫자형 - 최소급여:5000000)
CREATE TABLE SAWON4(NO NUMBER(4), NAME VARCHAR2(20), PAY NUMBER, CONSTRAINT SAWON4_PAY_CHECK CHECK(PAY>=5000000));
--(PAY와 NAME 함께 가지고 조건식 만들기 가능)
CREATE TABLE SAWON4(NO NUMBER(4), NAME VARCHAR2(20), PAY NUMBER, CONSTRAINT SAWON4_PAY_CHECK CHECK(NAME<>'홍길동' AND PAY>=5000000)); --OK
--제약조건 확인
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, SEARCH_CONDITION FROM USER_CONSTRAINTS WHERE TABLE_NAME='SAWON4';

1)

2)

2)

3)

4)

4) NOT NULL

• NULL을 허용하지 않는 제약조건
• 컬럼에 반드시 값이 저장되도록 설정하는 제약조건
• [컬럼 수준]의 제약조건만 가능!!
• INSERT & UPDATE 시 영향

--1)
-- 🔒NOT NULL 제약조건 없이 테이블 생성한다면?
-- DEPT1 테이블 생성 - 부서번호(숫자형), 부서이름(문자형), 부서위치(문자형)
CREATE TABLE DEPT1(DEPTNO NUMBER(2), DNAME VARCHAR2(12), LOC VARCHAR2(11));
DESC DEPT1;
--DEPT1테이블에 행 삽입
INSERT INTO DEPT1 VALUES(10,'총무부','서울시');
INSERT INTO DEPT1 VALUES(20,NULL,NULL); --명시적 NULL사용
INSERT INTO DEPT1(DEPTNO) VALUES(30); --묵시적 NULL사용
SELECT * FROM DEPT1;
COMMIT;



--2)
--🔒[컬럼수준]의  NOT NULL 제약조건 (컬럼수준의 제약조건만 가능)
--DEPT2 테이블 생성 - 부서번호(숫자형-NOT NULL), 부서이름(문자형-NOT NULL), 부서위치(문자형-NOT NULL)
CREATE TABLE DEPT2(DEPTNO NUMBER(2) CONSTRAINT DEPT2_DEPTNO_NN NOT NULL,
                    DNAME VARCHAR2(12) CONSTRAINT DEPT2_DNAME_NN NOT NULL,
                    LOC VARCHAR2(11) CONSTRAINT DEPT2_LOC_NN NOT NULL);
                    
--DEPT2테이블에 행 삽입
INSERT INTO DEPT2 VALUES(10,'총무부','서울시');
--NOT NULL 제약조건이 설정된 컬럼에 NULL을 전달할 경우 에러 발생
INSERT INTO DEPT2 VALUES(20,NULL,NULL);  --ERROR
--생략된 컬럼의 기본값이 NULL인 경우 생략 컬럼에 NOT NULL 제약조건이 설정되어 있으면 에러 발생
INSERT INTO DEPT2 (DEPTNO) VALUES(30);  --ERROR
SELECT * FROM DEPT2;
COMMIT;



--3)
-- DESC 명령을 가지고 NOT NULL 제약조건 확인 가능!! 유일함
DESC DEPT2;



--4)
-- 제약조건 확인 : NOT NULL 제약조건의 종류는 C로 표현됨
SELECT CONSTRAINT_NAME , CONSTRAINT_TYPE, SEARCH_CONDITION
FROM USER_CONSTRAINTS WHERE TABLE_NAME='DEPT2';

1)

2)

3)

4)

5) UNIQUE (U) VS PK

• 중복된 컬럼값 저장을 방지하기 위한 제약조건
• [컬럼수준] & [테이블수준]의 제약조건 가능
• 테이블의 여러 컬럼에 설정 가능
• NULL 허용
• INSERT & UPDATE 시 영향

--1)
--🔒UNIQUE 제약조건 없이 테이블 생성한다면?
--USER1 테이블 생성 - 아이디(문자형), 이름(문자형), 전화번호(문자형)
CREATE TABLE USER1(ID VARCHAR2(20), NAME VARCHAR2(30), PHONE VARCHAR2(15));
--USER1 테이블에 행 삽입
INSERT INTO USER1 VALUES('ABC','홍길동','010-1234-5678');
INSERT INTO USER1 VALUES('ABC','홍길동','010-1234-5678');
SELECT * FROM USER1;
COMMIT;



--2)
-- 🔒[컬럼수준]의 UNIQUE 제약조건
--USER2 테이블 생성 - 아이디(문자형 - UNIQUE), 이름(문자형), 전화번호(문자형 - UNIQUE)
 CREATE TABLE USER2(ID VARCHAR2(20) CONSTRAINT USER2_ID_UK UNIQUE,
 					NAME VARCHAR2(30),
                    PHONE VARCHAR2(15) CONSTRAINT USER2_PHONE_UK UNIQUE);
--제약조건 확인
SELECT CONSTRAINT_NAME , CONSTRAINT_TYPE FROM USER_CONSTRAINTS WHERE TABLE_NAME='USER2';
--USER2 테이블에 행 삽입
INSERT INTO USER2 VALUES('ABC','홍길동','010-1234-5678'); --OK
INSERT INTO USER2 VALUES('ABC','임꺽정','010-5678-1234'); --ERROR, ID컬럼값 중복
INSERT INTO USER2 VALUES('XYZ','임꺽정','010-1234-5678'); --ERROR, PHONE컬럼값 중복
INSERT INTO USER2 VALUES('ABC','홍길동','010-1234-5678'); --ERROR, ID와 PHONE컬럼값 중복
INSERT INTO USER2 VALUES('XYZ','임꺽정','010-5678-1234'); --OK
--UNIQUE 제약조건에 설정된 컬럼에 NULL을 전달하여 삽입 처리 가능
INSERT INTO USER2 VALUES('ASD','전우치',NULL);  --OK
--NULL은 값이 아니므로 UNIQUE제약조건의 영향을 받지 않아 중복유무와 상관없이 삽입 처리 가능
INSERT INTO USER2 VALUES('QWE','일지매',NULL);  --OK
SELECT * FROM USER2;
COMMIT;



--3)
--🔒[테이블수준]의 UNIQUE 제약조건
--USER3 테이블 생성 - 아이디(문자형 - UNIQUE), 이름(문자형), 전화번호(문자형 - UNIQUE)
CREATE TABLE USER3(ID VARCHAR2(20) ,
					NAME VARCHAR2(30),
                    PHONE VARCHAR2(15),
                    CONSTRAINT USER3_ID_UK UNIQUE(ID),
                    CONSTRAINT USER3_PHONE_UK UNIQUE(PHONE));
--제약조건 확인 - USER3 테이블에는 ID컬럼 또는 PHONE컬럼에 중복된 값 저장 방지
SELECT CONSTRAINT_NAME , CONSTRAINT_TYPE FROM USER_CONSTRAINTS WHERE TABLE_NAME='USER3';
--USER3 테이블에 행 삽입
INSERT INTO USER2 VALUES('ABC','홍길동','010-1234-5678'); --OK
INSERT INTO USER2 VALUES('ABC','임꺽정','010-5678-1234'); --ERROR, ID컬럼값 중복
INSERT INTO USER2 VALUES('XYZ','임꺽정','010-1234-5678'); --ERROR, PHONE컬럼값 중복
INSERT INTO USER2 VALUES('ABC','홍길동','010-1234-5678'); --ERROR, ID와 PHONE컬럼값 중복
INSERT INTO USER2 VALUES('XYZ','임꺽정','010-5678-1234'); --OK
SELECT * FROM USER3;
COMMIT;



--4)
--🔒[테이블수준]의 UNIQUE 제약조건 + (컬럼그룹화)
--UNIQUE제약조건은 테이블 수준의 제약조건을 사용하여 컬럼을 그룹화하여 중복 저장되는 것을 방지
--USER4 테이블 생성 - 아이디(문자형), 이름(문자형), 전화번호(문자형)
-- 테이블수준의 제약조건  : 아이디와 전화번호를 그룹화하여 UNIQUE 제약조건 설정
-- 테이블수준의 제약조건만 가능  : 제약조건이 1개가 됨!
CREATE TABLE USER4(ID VARCHAR2(20) ,
					NAME VARCHAR2(30),
                    PHONE VARCHAR2(15),
                    CONSTRAINT USER4_ID_PHONE_UK UNIQUE(ID,PHONE));
--제약조건 확인
SELECT CONSTRAINT_NAME , CONSTRAINT_TYPE FROM USER_CONSTRAINTS WHERE TABLE_NAME='USER4';
--USER4 테이블에 행 삽입
INSERT INTO USER4 VALUES('ABC','홍길동','010-1234-5678'); --OK
INSERT INTO USER4 VALUES('ABC','임꺽정','010-5678-1234'); --OK, ID컬럼값 중복되어도 저장 가능
INSERT INTO USER4 VALUES('XYZ','전우치','010-1234-5678'); --OK, PHONE컬럼값 중복되어도 저장 가능
--ID컬럼값과 PHONE컬럼값이 둘 다 중복될 경우 에러 발생
INSERT INTO USER4 VALUES('ABC','일지매','010-1234-5678'); --ERROR
SELECT * FROM USER4;
COMMIT;

1)

2)

2)

3)

3)

4)

4)

6) PRIMARY KEY (PK) VS UNIQUE

• 중복된 컬럼값 저장을 방지하기 위한 제약조건 (PK)
• 테이블의 관계를 설정하기 위해 반드시 필요한 제약조건 (PK)
• [컬럼수준] & [테이블수준]의 제약조건 가능
• 테이블의 딱 하나의 컬럼에만 설정 가능
• NULL 허용하지 않음 - NOT NULL
• 제약조건명 설정 생략 가능
• INSERT & UPDATE 시 영향

--1)
-- 🔒어떤 컬럼에 PK 제약조건을 설정하면 좋을까?
-- => 테이블에서 행을 구분할 수 있는 고유값이 저장된 컬럼에 설정
-- => PK 제약조건은*테이블의 관계를 설정하기 위해 반드시 설정해야함
-- => PK가 있어야 FK 존재 가능!



--2)
-- 🔒[컬럼수준]의 PK 제약조건
-- MGR1 테이블 생성 - 사원번호(숫자형 - PRIMARY KEY), 사원이름(문자형), 입사일(날짜형)
CREATE TABLE MGR1(NO NUMBER(4) CONSTRAINT MGR1_NO_PK PRIMARY KEY, NAME VARCHAR2(20), STARTDATE DATE);
DESC MGR1; -- PRIMARY KEY 제약조건에 의해 NO 컬럼은 자동으로 NUT NULL 설정
--제약조건 확인
SELECT CONSTRAINT_NAME , CONSTRAINT_TYPE FROM USER_CONSTRAINTS WHERE TABLE_NAME='MGR1';
--MGR1테이블에 행 삽입
INSERT INTO MGR1 VALUES(1000,'홍길동',SYSDATE); --OK
--NO 컬럼에 중복값을 전달할 경우 PRIMARY KEY 제약조건에 의해 에러 발생
INSERT INTO MGR1 VALUES(1000,'임꺽정',SYSDATE); --ERROR
--PRIMARY KEY 제약조건이 설정된 컬럼에 NULL을 전달할 경우 에러 발생
INSERT INTO MGR1 VALUES(NULL,'임꺽정',SYSDATE); --ERROR
INSERT INTO MGR1 VALUES(2000,'임꺽정',SYSDATE); --OK
SELECT * FROM MGR1;
COMMIT;



--3)
--🔒[테이블수준]의 PK 제약조건 - 컬럼 수준의 제약조건과 동일
--MGR2 테이블 생성 - 사원번호(숫자형 - PRIMARY KEY), 사원이름(문자형), 입사일(날짜형)
CREATE TABLE MGR2(NO NUMBER(4), NAME VARCHAR2(20), STARTDATE DATE, CONSTRAINT MGR2_NO_PK PRIMARY KEY(NO));
--제약조건 확인
SELECT CONSTRAINT_NAME , CONSTRAINT_TYPE FROM USER_CONSTRAINTS WHERE TABLE_NAME='MGR2';

2)

2) 제약조건 확인

2)

3)

7) FOREIGN KEY (FK)

• 부모 테이블에 저장된 행의 컬럼값을 참조하여, 자식 테이블의 컬럼에 비정상적인 값 저장을 방지하는 제약조건 (FK)
• 테이블의 관계를 구현화하기 위한 제약조건 (FK)
• PK와 FK 제약조건이 있어야 테이블과 테이블이 서로 관계를 맺을 수 있음
• 부모 테이블의 PK제약조건이 설정된 컬럼을 참조해 자식 테이블의 컬럼에 FK 제약조건 설정
• [테이블 수준]의 제약조건으로만 설정 가능
• INSERT & UPDATE & DELETE(유일) 시 영향
• 삽입, 삭제, 변경 시 FK 가 있으면 부모테이블을 참조해야하므로 속도가 느림
• 테이블이 10개 미만 시 FK를 안만들어도 되지만, 테이블이 많아지면 관계를 설정하는 것이 중요
• 데이타 무결성을 유지하기 위해서는 관계를 설정하는 것이 중요함

--1)
--💑부모테이블 SUBJECT1 생성 (PK제약조건 필수!!)
--SUBJECT1 테이블 생성 - 과목코드(숫자형 - PRIMARY KEY), 과목명(문자형)
CREATE TABLE SUBJECT1(SNO NUMBER(2) CONSTRAINT SUBJECT1_SNO_PK PRIMARY KEY, SNAME VARCHAR2(20));
--SUBJECT1 테이블에 행 삽입
INSERT INTO SUBJECT1 VALUES(10,'JAVA');
INSERT INTO SUBJECT1 VALUES(20,'JSP');
INSERT INTO SUBJECT1 VALUES(30,'SPRING');
SELECT * FROM SUBJECT1;
COMMIT;


--2)
-- 🐭일반 테이블 TRAINEE1 생성
-- 수강생번호(숫자형 - PRIMARY KEY), 수강생이름(문자형), 수강과목코드(숫자형)
CREATE TABLE TRAINEE1(TNO NUMBER(4) CONSTRAINT  TRAINEE1_TNO_PK PRIMARY KEY, TNAME VARCHAR(20), SCODE NUMBER(2));
--TRAINEE1 테이블 행 삽입
INSERT INTO TRAINEE1 VALUES(1000,'홍길동',10);
INSERT INTO TRAINEE1 VALUES(2000,'임꺽정',20);
INSERT INTO TRAINEE1 VALUES(3000,'전우치',30);
INSERT INTO TRAINEE1 VALUES(4000,'일지매',40);
SELECT * FROM TRAINEE1;
COMMIT;


--3)
-- [JOIN] 
-- TRAINEE1 테이블과 SUBJECT1 테이블에서 모든 수강생의 수강생번호, 수강생이름, 수강과목명 검색
-- 결합조건 : TRAINEE1 테이블의 수강과목 코드(SCODE)와 SUBJECT1 테이블의 과목코드(SNO)이 같은 행을 결합하여 검색
-- INNER JOIN 이용: INNER JOIN은 결합조건이 맞는 행만 결합하여 검색 - 결합조건이 맞지 않는 행 미검색
-- 테이블 결합시 결합조건에 맞지 않는 컬럼값이 저장되어 있는 경우 잘못된 결과 제공 - 데이타 무결성 위반
SELECT TNO, TNAME, SNAME FROM TRAINEE1 JOIN SUBJECT1 ON SCODE=SNO;--INNER JOIN

-- OUTER JOIN을 사용하여 결합조건이 맞지 않는 행은 NULL과 결합하여 검색
SELECT TNO, TNAME, SNAME FROM TRAINEE1 LEFT JOIN SUBJECT1 ON SCODE=SNO;--LEFT OUTER JOIN

1) 부모테이블

2) 일반테이블

3) INNER JOIN

3) LEFT OUTER JOIN

 

--4)
-- 👨‍👧‍자식테이블 TRAINEE2 생성( FK제약조건 부여!! )
-- 데이타 무결성을 위반하지 않기 위해  FK 제약조건이 걸린 테이블을 만들어보자!!
-- 자식 테이블이 참조하는 부모 테이블의 컬럼은 반드시 PK 제약조건이 설정되어 있어야 참조 가능 - 테이블의 관계가 형성됨!
--TRAINEE2 테이블 생성 - 수강생번호(숫자형 - PRIMARY KEY), 수강생이름(문자형), 수강과목코드(숫자형 - FOREIGN KEY)
--TRAINEE2 테이블의 수강과목코드(SCODE)는 SUBJECT1 테이블의 과목코드(SNO)를 참조하도록 FOREIGN KEY 제약조건을 설정함
--TRAINEE2 테이블 - 자식테이블이 됨! , 부모테이블 참조가능!
CREATE TABLE TRAINEE2(TNO NUMBER(4) CONSTRAINT  TRAINEE2_TNO_PK PRIMARY KEY, 
						TNAME VARCHAR(20),
                        SCODE NUMBER(2),
                        CONSTRAINT TRINEE2_SCODE_FK FOREIGN KEY(SCODE) REFERENCES SUBJECT1(SNO));
                        --SUBJECT1테이블의 SNO 컬럼을 반!드!시! 참조하세요!!!
--제약조건 확인
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, R_CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME='TRAINEE2';


--5)
--TRAINEE2 테이블에 행 삽입
INSERT INTO TRAINEE2 VALUES(1000,'홍길동',10);
INSERT INTO TRAINEE2 VALUES(2000,'임꺽정',20);
INSERT INTO TRAINEE2 VALUES(3000,'전우치',30);
--FOREIGN KEY 제약조건이 설정된 컬럼에 부모 테이블의 참조 컬럼에 저장되지 않은 값을 전달할 경우
--참조가 불가능하여 에러 발생
INSERT INTO TRAINEE2 VALUES(4000,'일지매',40); --ERROR, FOREIGN KEY 제약조건 위반
SELECT * FROM TRAINEE2;
COMMIT;



--6)
-- [JOIN] 
-- TRAINEE2테이블(자식테이블)과 SUBJECT1테이블(부모테이블)에서 
-- 모든 수강생의 수강생번호, 수강생이름, 수강과목명 검색 - 데이타 무결성 위반할 일이 없음!!!
-- 결합조건 : TRAINEE2 테이블의 수강과목 코드(SCODE)와 SUBJECT1 테이블의 과목코드(SNO)이 같은 행을 결합하여 검색
-- INNER JOIN이용
SELECT TNO, TNAME, SNAME FROM TRAINEE2 JOIN SUBJECT1 ON SCODE=SNO; -- INNER JOIN


--7)
-- [ERROR]
-- 자식테이블(TRAINEE2)에서 수강생번호가 1000인 수강생의 수강과목코드를 40으로 [변경]
-- FOREIGN KEY 제약조건이 설정된 컬럼에 부모 테이블의 참조 컬럼에 저장되지 않은 값을 전달할 경우 참조가 불가능하여 에러 발생
--자식이 참조 할 "부모가 없다"
UPDATE TRAINEE2 SET SCODE=40 WHERE TNO=1000; --ERROR, FK 제약조건 위반





--8)
-- [ERROR] - 유일한 삭제 에러 fk
-- 부모테이블(SUBJECT1)에서 과목코드가 10인 과목정보 [삭제] - 유일하게 삭제 시 에러!!!!
-- 유일하게 제약조건 중 삭제할 때 ERROR 나오는 FK 제약조건
-- FK 제약조건이 설정된 자식 테이블의 컬럼이 참조하는 부모 테이블의 컬럼값이 포함된 행을 삭제할 경우 에러 발생
-- 자식 테이블(TRAINEE2)의 컬럼이 참조하는 부모 테이블(SUBJECT1)의 컬럼값 검색
-- 검색된 컬럼값이 저장된 부모 테이블의 행은 삭제 불가능
SELECT DISTINCT SCODE FROM TRAINEE2; --검색결과 : 10,20,30
--부모의 행이 삭제되면 "이미 참조하고 있는 자식이 참조할 부모를 잃는다"
DELETE FROM SUBJECT1 WHERE SNO=10; --ERROR, FK 제약조건 위반

4) 제약조건 확인

5)

6)

8)

--9)
--💑부모테이블 SUBJECT2 생성 (PK제약조건 필수!!)
--SUBJECT2 테이블 생성 - 과목코드(숫자형 - PRIMARY KEY), 과목명(문자형)
CREATE TABLE SUBJECT2(SNO NUMBER(2) CONSTRAINT SUBJECT2_SNO_PK PRIMARY KEY, SNAME VARCHAR2(20));
--SUBJECT1 테이블에 행 삽입
INSERT INTO SUBJECT2 VALUES(10,'JAVA');
INSERT INTO SUBJECT2 VALUES(20,'JSP');
INSERT INTO SUBJECT2 VALUES(30,'SPRING');
SELECT * FROM SUBJECT2;
COMMIT;



--10)
-- 👨‍👧‍자식테이블 TRAINEE3 생성 ( FK제약조건 부여!! )
-- FK 제약조건을 설정할 경우 ON DELETE CASCADE 또는 ON DELETE SET NULL 기능 추가 가능
-- ON DELETE CASCADE : 만약 부모 컬럼값이 삭제되면 자식도 같이 지워라
-- 부모테이블의 행을 삭제할 경우 자식테이블에 참조 컬럼값이 저장된 행도 같이 삭제하는 기능 제공
-- ON DELETE SET NULL : 만약 부모 컬럼값이 삭제되면 자식의 컬럼값을 NULL로 바꿔라
-- 부모테이블의 행을 삭제할 경우 자식테이블에 참조 컬럼값을 NULL로 변경하는 기능 제공
-- TRAINEE3 테이블 생성 - 수강생번호(숫자형 - PRIMARY KEY), 수강생이름(문자형), 수강과목코드(숫자형 - FOREIGN KEY)
-- TRAINEE3 테이블의 수강과목코드(SCODE)는 SUBJECT2 테이블의 과목코드(SNO)를 참조하도록 FOREIGN KEY 제약조건을 설정함
CREATE TABLE TRAINEE3(TNO NUMBER(4) CONSTRAINT  TRAINEE3_TNO_PK PRIMARY KEY, TNAME VARCHAR(20), SCODE NUMBER(2),
	CONSTRAINT TRINEE3_SCODE_FK FOREIGN KEY(SCODE) REFERENCES SUBJECT2(SNO) ON DELETE CASCADE);
--TRAINEE3 테이블에 행 삽입
INSERT INTO TRAINEE3 VALUES(1000,'홍길동',10);
INSERT INTO TRAINEE3 VALUES(2000,'임꺽정',20);
INSERT INTO TRAINEE3 VALUES(3000,'전우치',30);
SELECT * FROM TRAINEE3;
COMMIT;




--11)
--[NOT ERROR] 
--부모테이블(SUBJECT2)에서 과목코드가 10인 과목정보 [삭제]
--ON DELETE CASCADE 옵션에 의해
--TRAINEE3 테이블에 저장된 수강생 중 10번 과목을 수강하는 모든 수강생정보가 삭제됨
DELETE FROM SUBJECT2 WHERE SNO=10; --OK
SELECT * FROM SUBJECT2;
SELECT * FROM TRAINEE3;
COMMIT;

9)

10)

11) SUBJECT2

11)TRANIEE3

 

 

 

 

 

 

 

 

 

 

4. 서브쿼리를 이용한 테이블 생성 - 복사

• 복사 기능, 기존 테이블을 이용해 새로운 테이블 생성가능

1) 형식

• 서브쿼리 검색결과로 타켓테이블을 생성하고 검색된 행을 생성된 타켓테이블에 삽입함

CREATE TABLE 타켓테이블명[(컬럼명,컬럼명,...)] AS SELECT 검색대상,검색대상,... FROM 원본테이블명 [WHERE 조건식]

2) 서브쿼리를 사용해 복사한 테이블의 특징

• 서브쿼리 검색대상의 속성을 전달받아 타켓테이블을 생성함
• 타켓테이블의 컬럼명 변경 가능
• 타켓테이블의 컬럼 자료형 및 크기 변경 불가능
• 타켓테이블에는 제약조건 미적용

--1)
--🐹 모든행(모든사원) + 모든컬럼값( * ) 복사
--EMP테이블(원본테이블)에 저장된 모든 사원의 사원정보를 검색하여 EMP2테이블(타겟테이블)을 생성하고 검색행을 삽입 처리
CREATE TABLE EMP2 AS SELECT * FROM EMP;
--EMP테이블과 EMP2 테이블의 구조 비교 - 원본테이블과 타겟테이블의 속성 동일
DESC EMP;
DESC EMP2;
--EMP 테이블과 EMP2 테이블의 제약조건 비교
-- EMP테이블에는 제약조건이 설정되어 있지만, EMP2테이블에는 제약조건 미설정
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE FROM USER_CONSTRAINTS WHERE TABLE_NAME='EMP';
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE FROM USER_CONSTRAINTS WHERE TABLE_NAME='EMP2';
--EMP테이블과 EMP2테이블에 저장된 행 비교 - 원본테이블과 타겟테이블의 저장행 동일
SELECT * FROM EMP;
SELECT * FROM EMP2;



--2)
--🐹 모든행(모든사원) + 특정컬럼값만 복사
--EMP테이블에 저장된 모든 사원의 사원번호, 사원이름, 급여를 검색하여
--EMP3 테이블을 생성하고  검색행을 삽입 처리
CREATE TABLE EMP3 AS SELECT EMPNO,ENAME,SAL FROM EMP;
--테이블의 구조 및 저장행 확인
DESC EMP3;
SELECT * FROM EMP3;


--3)
--🐹 특정행만 + 특정컬럼값만 복사
--EMP테이블에서 급여가 2000 이상인 사원의 사원번호, 사원이름, 급여를 검색하여
--EMP4 테이블을 생성하고 검색행을 삽입 처리
CREATE TABLE EMP4 AS SELECT EMPNO,ENAME,SAL FROM EMP WHERE SAL>=2000;
--테이블의 구조 및 저장행 확인
DESC EMP4;
SELECT * FROM EMP4;


--4)
--🐹 특정행 + 특정컬럼값 + 컬럼명 변경해서 복사
--EMP테이블에서 급여가 2000 이상인 사원의 사원번호, 사원이름, 급여를 검색하여 EMP5 테이블을 생성하고 검색행을 삽입 처리
--EMP5테이블의 컬럼명을 NO(사원번호), NAME(사원이름), PAY(급여)가 되도록 작성
CREATE TABLE EMP5(NO,NAME,PAY) AS SELECT EMPNO, ENAME, SAL FROM EMP WHERE SAL>=2000;
--테이블의 구조 및 저장행 확인
DESC EMP5;
SELECT * FROM EMP5;


--5)
--🐹 행만 복사 - 무조건적인 거짓 사용
--EMP 테이블과 동일한 속성이 설정된 EMP6 테이블을 생성
--원본 테이블의 행을 복사하지 않도록 작성
--서브쿼리의 검색행의 조건식을 무조건 거짓으로 설정하여 검색
CREATE TABLE EMP6 AS SELECT * FROM EMP WHERE 1=0;
--테이블의 구조 및 저장행 확인
DESC EMP6;
SELECT * FROM EMP6;

1)

1)

1)

1)  원본테이블과 타겟테이블의 저장행 동일

2)

2)

3)

3)

4)

4)

5)

5)