[SQL-Oracle]DDL(데이타 정의어)-CREATE, 자료형, 딕셔너리, 제약조건-CHECK, NOT NULL, UNIQUE

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

🔘CREATE

테이블(TABLE): 데이터베이스에서 데이터(행)를 저장하기 위한 가장 기본적인 객체

테이블 생성 : 표현대상의 속성을 모델링(데이터베이스 모델링)하여 설계하여 테이블을 설계한 후 생성
형식)CREATE TABLE 테이블명(컬럼명 자료형[(크기)] [DEFAULT 기본값] [컬럼제약조건]
      ,컬럼명 자료형[(크기)] [DEFAULT 기본값] [컬럼제약조건],...[,테이블제약조건]) 

🔶 오라클 자료형(DATATYPE): 컬럼에 저장 가능한 값의 형태를 표현하기 위한 키워드
1.숫자형 :

NUMBER[(전체자릿수, 소수점자릿수)]
2.문자형 :

CHAR(크기) - 크기 : 1~2000(BYTE) >> 고정길이 - 컬럼값의 크기에 상관없이 컬럼의 크기는 고정되도록 처리
VARCHAR2(크기) - 크기 : 1~4000(BYTE) >> 가변길이 - 컬럼값의 크기에 상관없이 컬럼의 크기가 변화되도록 처리
LONG - 최대 2GBYTE까지의 텍스트 파일이 저장 가능한 가변길이 자료형 - 테이블의 컬럼 중 하나에만 사용 가능하며 정렬 불가능
CLOB - 최대 4GBYTE까지의 텍스트 파일을 저장하기 위한 가변길이 자료형
BLOB - 최대 4GBYTE까지의 이진 파일을 저장하기 위한 가변길이 자료형
3.날짜형 :

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

--SALESMAN 테이블 생성 - 사원번호(숫자형),사원이름(문자형),입사일(날짜형)
CREATE TABLE SALESMAN(NO NUMBER(4),NAME VARCHAR2(20), STARTDATE DATE);

🔶 딕셔너리(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 딕셔너리 대신 사용 가능한 동의어(SYNONYM)로 TABS 제공
SELECT TABLE_NAME FROM TABS;

 

--SALESMAN 테이블의 속성 확인
DESC SALESMAN;

--SALESMAN 테이블에 행 삽입
INSERT INTO SALESMAN VALUES(1000,'홍길동','00/04/18');
SELECT * FROM SALESMAN;
COMMIT;

--컬럼을 생략하여 행을 삽입한 경우 생략된 컬럼에는 컬럼 기본값이 전달되어 삽입 처리
--테이블 생성시 컬럼 기본값을 설정하지 않으면 자동으로 NULL을 기본값으로 사용
INSERT INTO SALESMAN(NO,NAME) VALUES(2000,'임꺽정');
SELECT * FROM SALESMAN;\


COMMIT;

--테이블 생성시 컬럼에 제약조건을 부여하지 않으면 어떤 값을 전달해도 삽입 처리 가능 - 데이터 무결성 위반 가능(제약조건을 주는 것이 좋음)

INSERT INTO SALESMAN VALUES(1000,'전우치','10/10/10');
SELECT * FROM SALESMAN;
COMMIT;

 

예제

--MANAGER 테이블(사원정보) 생성 - 사원번호(숫자형),사원이름(문자형),입사일(날짜형-기본값:현재), 급여(숫자형-기본값:1000)
CREATE TABLE MANAGER(NO NUMBER(4),NAME VARCHAR2(20),STARTDATE DATE DEFAULT SYSDATE,PAY NUMBER DEFAULT 1000);

--테이블 목록 및 속성 확인
SELECT TABLE_NAME FROM TABS;
DESC MANAGER;

🔸  USER_TAB_COLUMNS : 테이블의 컬럼 정보를 제공하는 딕셔너리

SELECT COLUMN_NAME,DATA_DEFAULT FROM USER_TAB_COLUMNS WHERE TABLE_NAME='MANAGER';

--MANAGER 테이블에 행 삽입
INSERT INTO MANAGER VALUES(1000,'홍길동','00/05/29',3000);--컬럼 기본값 미사용
SELECT COLUMN_NAME,DATA_DEFAULT FROM USER_TAB_COLUMNS WHERE TABLE_NAME='MANAGER';

 

--행 삽입시 컬럼을 생략할 경우 기본값이 전달되어 삽입 처리

INSERT INTO MANAGER(NO,NAME) VALUES(2000,'임꺽정');
SELECT * FROM MANAGER;

--행 삽입시 컬럼값 대신 DEFAULT 키워드로 컬럼 기본값을 전달하여 삽입 처리

INSERT INTO MANAGER VALUES(3000,'전우치',DEFAULT,DEFAULT);
SELECT * FROM MANAGER;
COMMIT;

🔶제약조건(CONSTRAINT) : 컬럼에 잘못된 값이 저장되는 것을 방지하기 위한 기능 - 데이타 무결성을 위해 사용
--컬럼에 부여된 제약조건을 위반하는 컬럼값을 사용할 경우 에러 발생
--컬럼 수준의 제약조건과 테이블 수준의 제약조건으로 구분

🔘 CHECK : 조건식을 제공받 조건식의 결과가 참(TRUE)인 경우에만 컬럼값으로 저장하는 기능을 부여하는 제약조건
--테이블 생성시 컬럼 수준의 제약조건 또는 테이블 수준의 제약조건을 사용하여 컬럼에 제약조건 부여

 

제약조건 없이 테이블 생성하면⬇️

--SAWON1 테이블 생성 - 사원번호(숫자형),사원이름(문자형),급여(숫자형)
CREATE TABLE SAWON1(NO NUMBER(4),NAME VARCHAR2(20),PAY NUMBER);
DESC SAWON1;

--SAWON1 테이블에 행 삽입 - PAY 컬럼에는 모든 숫자값을 전달받아 저장 가능
INSERT INTO SAWON1 VALUES(1000,'홍길동',8000000);
INSERT INTO SAWON1 VALUES(2000,'임꺽정',800000);
SELECT * FROM SAWON1;
COMMIT;

 

컬럼 수준의 CHECK 제약조건을 부여한 테이블 생성하면⬇️

--SAWON2 테이블 생성 - 사원번호(숫자형),사원이름(문자형),급여(숫자형-최소급여:5000000)
--CHECK 제약조건을 컬럼수준의 제약조건으로 부여
--CHECK 제약조건을 컬럼수준의 제약조건으로 부여할 경우 CHECK 제약조건의 조건식은 현재 컬럼만 사용하여 작성 가능
CREATE TABLE SAWON2(NO NUMBER(4),NAME VARCHAR2(20),PAY NUMBER CHECK(PAY>=5000000));
DESC SAWON2;

--SAWON2 테이블에 행 삽입 - PAY 컬럼에는 CHECK 제약조건으로 제공된 조건식이 참인 컬럼값만 전달받아 삽입 가능
INSERT INTO SAWON2 VALUES(1000,'홍길동',8000000);
INSERT INTO SAWON2 VALUES(2000,'임꺽정',800000);--CHECK 제약조건을 위반한 컬럼값이 존재하여 에러 발생
SELECT * FROM SAWON2;
COMMIT;

🔸 USER_CONSTRAINTS : 테이블에 부여된 제약조건 정보를 제공하는 딕셔너리
--CONSTRAINT_NAME : 제약조건을 구분하기 위한 이름(고유값) - 제약조건의 이름을 설정하지 않으면 자동으로 SYS_XXXXXX 형식으로 제공
--CONSTRAINT_TYPE : 제약조건의 종류 - C(CHECK), U(UNIQUE), P(PRINARY KEY), R(REFEFNCE - FOREIGN KEY)
--SEARCH_CONDITION : CHECK 제약조건에 의해 생성된 조건식

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

--SAWON3 테이블 생성 - 사원번호(숫자형),사원이름(문자형),급여(숫자형-최소급여:5000000)
--테이블 생성시 컬럼에 제약조건을 부여할 경우 제약조건을 효율적으로 관리하기 위해 제약조건의 이름을 설정하는 것을 권장
형식)컬럼명 자료형 CONSTRAINT 제약조건명 제약조건

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';

 

테이블 수준의 CHECK 제약조건을 부여한 테이블 생성하면⬇️

--SAWON4 테이블 생성 - 사원번호(숫자형),사원이름(문자형),급여(숫자형-최소급여:5000000)
--CHECK 제약조건을 테이블 수준의 제약조건으로 부여 
--CHECK 제약조건을 컬럼 수준의 제약조건으로 부여할 경우 제약조건을 부여한 컬럼만 사용하여 조건식 작성 가능
CREATE TABLE SAWON4(NO NUMBER(4),NAME VARCHAR2(20),PAY NUMBER CONSTRAINT SAWON4_PAY_CHECK CHECK(PAY>=5000000));
SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,SEARCH_CONDITION FROM USER_CONSTRAINTS WHERE TABLE_NAME='SAWON4';

🔘  NOT NULL: 컬럼에 값을 반드시 저장되도록 만들어 주는 기능을 제공하는 제약조건 - NULL 미허용
--컬럼 수준의 제약조건으로만 부여 가능 - CHECK 제약조건으로 표현

 

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;

 

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);
DESC DEPT2;

--DEPT2 테이블의 제약조건 확인
SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,SEARCH_CONDITION FROM USER_CONSTRAINTS WHERE TABLE_NAME='DEPT2';

--DEPT2 테이블에 행 삽입
INSERT INTO DEPT2 VALUES(10,'총무부','서울시');
INSERT INTO DEPT2 VALUES(20,NULL,NULL);--묵시적 NULL 사용 : NOT NULL 제약조건을 위반하여 에러 발생
INSERT INTO DEPT2(DEPTNO) VALUES(30);--묵시적 NULL 사용 : NOT NULL 제약조건을 위반하여 에러 발생
SELECT * FROM DEPT2;
COMMIT;

 

🔘UNIQUE : 중복된 컬럼값이 저장되는 것을 방지하기 위한 기능을 제공하는 제약조건
--컬럼 수준의 제약조건 또는 테이블 수준의 제약조건으로 제약조건 부여 가능
 --PK 제약조건과의 차이점: UNIQUE 제약조건은 테이블에 여러번 부여할 수 있으며 NULL 허용

 

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;

 

컬럼 수준의 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);

--USER2 테이블의 제약조건 확인  
SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE FROM USER_CONSTRAINTS WHERE TABLE_NAME='USER2';

--USER2 테이블에 행 삽입
INSERT INTO USER2 VALUES('ABC','홍길동','010-1234-5678');
INSERT INTO USER2 VALUES('ABC','홍길동','010-1234-5678');--UNIQUE 제약조건을 위반해 에러 발생 : 아이디와 전화번호 중복 
INSERT INTO USER2 VALUES('ABC','임꺽정','010-7890-1234');--UNIQUE 제약조건을 위반해 에러 발생 : 아이디 중복 
INSERT INTO USER2 VALUES('XYZ','임꺽정','010-1234-5678');--UNIQUE 제약조건을 위반해 에러 발생 : 전화번호 중복 
INSERT INTO USER2 VALUES('XYZ','임꺽정','010-7890-1234');
SELECT * FROM USER2;
COMMIT;

--UNIQUE 제약조건이 부여된 컬럼에 NULL을 전달하여 저장 가능
INSERT INTO USER2 VALUES('OPQ','전우치',NULL);
INSERT INTO USER2 VALUES('IJK','일지매',NULL);--NULL은 값이 아니므로 UNIQUE 제약조건을 위반하지 않는 것으로 처리
SELECT * FROM USER2;
COMMIT;

 

테이블 수준의 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 테이블의 제약조건 확인  
SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE FROM USER_CONSTRAINTS WHERE TABLE_NAME='USER3';

--USER3 테이블에 행 삽입
INSERT INTO USER3 VALUES('ABC','홍길동','010-1234-5678');
INSERT INTO USER3 VALUES('ABC','홍길동','010-1234-5678');--UNIQUE 제약조건을 위반해 에러 발생 : 아이디와 전화번호 중복 
INSERT INTO USER3 VALUES('ABC','임꺽정','010-7890-1234');--UNIQUE 제약조건을 위반해 에러 발생 : 아이디 중복 
INSERT INTO USER3 VALUES('XYZ','임꺽정','010-1234-5678');--UNIQUE 제약조건을 위반해 에러 발생 : 전화번호 중복 
INSERT INTO USER3 VALUES('XYZ','임꺽정','010-7890-1234');
SELECT * FROM USER3;
COMMIT;

아이디와 전화번호를 묶어서 테이블 수준의 UNIQUE 제약조건을 부여한 테이블을 생성하면⬇️

--USER4 테이블 생성 - 아이디(문자형),이름(문자형),전화번호(문자형)
--아이디와 전화번호를 묶어서 제약조건을 부여하기 위해 테이블 수준의 제약조건 사용
CREATE TABLE USER4(ID VARCHAR2(20),NAME VARCHAR2(30),PHONE VARCHAR2(15)
                    ,CONSTRAINT USER4_ID__PHONE_UK UNIQUE(ID,PHONE));

--USER4 테이블의 제약조건 확인  
SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE FROM USER_CONSTRAINTS WHERE TABLE_NAME='USER4';

--USER4 테이블에 행 삽입
INSERT INTO USER4 VALUES('ABC','홍길동','010-1234-5678');
INSERT INTO USER4 VALUES('ABC','홍길동','010-1234-5678');--UNIQUE 제약조건을 위반해 에러 발생 : 아이디와 전화번호 중복 
INSERT INTO USER4 VALUES('ABC','임꺽정','010-7890-1234');--아이디가 중복되어도 삽입 가능
INSERT INTO USER4 VALUES('XYZ','임꺽정','010-1234-5678');--전화번호가 중복되어도 삽입 가능
SELECT * FROM USER4;
COMMIT;