일일 정리

테이블 생성과 데이터 타입(DDL), 테이블 관리, 제약 조건 : PK, FK

mysecurity 2025. 3. 18. 23:41

목차

1. 테이블 생성과 데이터 타입 

1-1 DDL

 

2. 테이블 관리

2-1 테이블 생성

2-2 테이블 내용 완전 삭제

 

3. 제약 조건 : PK, FK

3-1 제약 조건의 이해

3-2 제약 조건 검색

 

 

1. 테이블 생성과 데이터 타입 

1-1 DDL

DDL(Data Definition Language)은 데이터베이스의 구로를 정의하는 언어로, 다음과 같은 명령어를 가진다.

- CREATE (생성)

- ALTER (수정)

- DROP (삭제)

- RENAME

- TRUNCATE

 

CREATE, ALTER, DROP 명령어는 항상 붙어 다니며 보안 등급이 높다.

TRUNCATE 명령어는 sql 레벨에서는 DELETE와 같은 기능을 제공하며 사용 시 데이터의 공간 할당을 해제한다. 

 

● 테이블 생성과 삭제

CREATE TABLE 테이블(              
컬럼 데이터타입[DEFAULT 값] [컬럼 레벨 제약조건],
......
[테이블 레벨 제약조건],
.....
);
DROP TABLE 테이블 [CASCADE CONSTRAINT]; 
// 삭제 내용은 휴지통에 남겨놓는다. 휴지통은 공간을 차지하지 않는다.
PURGE RECYCLEBIN;
// 휴지통 비우기

 

DDL 명령어는 commit 없이 바로 저장되므로 실행 시 주의한다.

 

● 테이블 생성 규칙

- 문자로 시작한다.

 

- 30자 이내로 한다.

 

- 영문, 숫자, _, $, # 을 사용한다.

    한글 사용이 가능은 하지만 되도록 사용하지 않는 것이 좋다.

    $는 매우 중요한 곳에 쓰이므로 웬만해선 쓰지 않는다.

 

- 테이블의 이름은 동일한 유저(스키마) 안에서 유일해야 한다.

     select * from emp; 는 원래 select * from ast09.emp; 이다.

 

- 예약어는사용이 불가능하다. ex) from

 

- 대소문자를 구별하지 않는다.

    생성할 때 사용한 문자와는 관계없이 모든 이름은 대문자로 정의된다.

    테이블 이름은 딕셔너리에 저장되는데, 모두 대문자로 저장된다.

 

● 데이터 타입

데이터 타입은 매우 다양한데, 이는 타입마다 저장 용량을 다르게 하여 메모리를 최대한 적게 먹기 위함이다.

최근에는 메모리가 커져 다음 3가지 대표 타입만을 거의 사용한다.

VARCHAR2(n) 가변 길이 문자 타입 (1 < n < 4000 byte)
n=200 이면 200까지 공간 차지 가능하다.
NUMBER(n,p) 숫자 타입, n은 전체 자리수이고 p는 소수점 이하 자리수이다.
전체 자리수를 초과할 경우 입력 거부되지만 소수점 이하 자리수가 초과되면 반올림되어 입력된다.
DATE 날짜 타입, 출력이나 입력 형식과 무관하게 YYYY/MM/DD:HH24:MI:SS 형태로 저장된다.
(기원전 4712년1월1일 ~ 서기 9999년12월31일)

 

다른 데이터베이스는 VARCHAR,  오라클 데이터베이스만 VARCHAR2를 사용한다. 참고로 VARCHAR와 VARCHAR2의 차이는 없다.

 

● 테이블 생성 관련 명령어

 SQL> SELECT * FROM tab;
 	 =
 SQL> SELECT table_name FROM user_tables;

- 스키마에 테이블 목록을 검색한다.

- 스키마는 유저와 동일하게 쓰인다.

- user_tables는 딕셔너리라고 부르며 번역을 따로 하지 않는다. _indexs, _views, _sequences 등 원하는 오브젝트를 검색한다.

 

 SQL> DESC 테이블;
	 =
 SQL> SELECT table_name, column_name, data_type, data_length
   2  FROM user_tab_columns
   3  [WHERE table_name = '테이블'];

- 테이블의 컬럼 구성을 확인한다.

 

● 테이블 생성과 확인

ex) 다음과 같은 구조의 테이블을 생성하고 확인한다.

테이블명: board(게시판)

구성컬럼: no(게시물번호), name(작성자), sub(제목), content(내용), hdate(입력일시)

SQL> CREATE TABLE board (
  2  no NUMBER,
  3  name VARCHAR2(50),
  4  sub VARCHAR2(100),
  5  content VARCHAR2(4000),
  6  hdate DATE DEFAULT SYSDATE
  7  ); 
  
SQL> SELECT * FROM tab;
SQL> SELECT table_name FROM user_tables
  2  WHERE table_name = 'BOARD';

- SYSDATE는 INSERT를 했을 때의 시간이 들어간다.

- 첫 문장에서 board로 작성했으나 저장은 대문자로 되므로 검색 시에는 BOARD로 검색한다.

 

ex) 생성된 테이블에 행을 입력하고 default 입력을 확인한다.

SQL> DESC board;
SQL> SELECT table_name, column_name, data_type, data_length
  2  FROM user_tab_columns
  3  WHERE table_name = 'BOARD';
SQL> INSERT INTO board (no) VALUES (1);
SQL> COMMIT;
SQL> SELECT * FROM board;

 

● 문자 타입 데이터

- 문자와 숫자

: 개별 자리가 의미가 있는 경우, 연산 가능성이 없는 경우 문자 타입이다.

 ex) 주민번호, 군번, 학번 등

 

: 연산 가능성이 있는 경우 숫자 타입이다. NULL이 없도록 주의한다.

ex) 금액 등

 

- VARCHAR, VARCHAR2

: 둘은 같은 데이터 타입이다.

 

- CHAR, VARCHAR2

: CHAR의 장점은 없다. 과거 호환성때문에 가지고 있을 수 있다.

 

● CHAR, VARCHAR2의 비교
동일한 값이 입력된 두 컬럼이 데이터 타입에 따라 다르게 인식됨을 확인한다.

SQL> CREATE TABLE comp( 
  2  co1 CHAR(4),
  3  co2 VARCHAR2(4)
  4  );
SQL> INSERT INTO comp VALUES ('AA','AA');
SQL> SELECT LENGTHB(co1), LENGTHB(co2) FROM comp;	
SQL> SELECT * FROM comp WHERE co1 ='AA';
SQL> SELECT * FROM comp WHERE co2 ='AA';
SQL> SELECT * FROM comp WHERE co1 =co2;

 

위의 경우 co1과 co2의 크기는 다음과 같다.

- co1

A A    

 

- co2

A A

 

char(n) 타입은 고정 길이 문자 타입으로, 입력된 값이 더 짧더라도 반드시 n byte만큼 메모리를 차지함을 확인할 수 있다.

때문에 SELECT * FROM comp WHERE co1=co2; 실행 시 아무것도 검색되지 않는다.

 

● 날짜 타입 컬럼 검색

날짜 데이터 입력 시에 sysdate 등의 자동으로 입력되는 기능을 이용할 때 주의사항

SQL> CREATE TABLE SQL> CREATE TABLE hd (
  2  no NUMBER,
  3  hdateDATE
  4  );
  
SQL> INSERT INTO hd VALUES (1, sysdate);
SQL> SELECT * FROM hd;
SQL> SELECT * FROM hd WHERE hdate = '2021/10/02';
SQL> SELECT no, TO_CHAR(hdate,'YYYY/MM/DD:HH24:MI:SS') FROM hd;
SQL> SELECT * FROM hd
  2  WHERE hdateBETWEEN '2021/10/02' AND '2021/10/03';

 

insert 문에서 sysdate를 그냥 입력했으므로 '연/월/일:시:분:초'의 형태로 시간이 저장되므로 SELECT * FROM hd WHERE hdate = '2021/10/02'; 실행 시 아무것도 검색되지 않는다.

때문에 마지막 sql문과 같이 검색하거나, 애초에 insert 문에서 sysdate에 trunc를 씌워 입력한다.

 

● 삭제 테이블 복구

SELECT object_name, original_name FROM recyclebin;
FLASHBACK TABLE ORIGINAL_NAME TO BEFORE DROP
[RENAME TO NEW_NAME];

 

ex) TEST 테이블을 삭제하고 복구한다.

SQL> DROP TABLE test;
SQL> SELECT * FROM tab;
SQL> SELECT object_name, original_name FROM recyclebin;
SQL> FLASHBACK TABLE test TO BEFORE DROP;
SQL> SELECT * FROM tab;

 

기존 테이블의 형태가 기억된 공간은 언제든지 덮어씌워질 수 있으므로 이 경우 복구가 되지 않을 수 있다.

때문에 복구 작업은 최대한 빨리 실행해야 한다.

 

● 테이블 생성 실습 방법

테이블 생성과 같이 긴 SQL문은 반드시 스크립트 파일을 만들어 두고 실행한다.

스크립트 파일 생성 시 주의 사항

- 파일명은 ###.sq l파일로 생성한다.

- 반드시 스크립트 파일을 저장하는 별도의 폴더를 지정하거나 sqlplus를 실행하는 sql 폴더에 저장한다.

 

sql 스크립트 저장
@저장경로 입력하여 스크립트 실행

 

2. 테이블 관리

2-1 테이블 생성

● 서브 쿼리를 이용한 테이블 생성

CREATE TABLE 테이블[(
컬럼[DEFAULT 값],
...
)]
AS(SELECT 문장: Sub query문);

- 서브 쿼리 실행 결과를 테이블로 생성한다.

- 컬럼 목록이 생략되면 서브 쿼리의 열 이름이 생성될 테이블의 컬럼 명이 된다.

- 컬럼에 DEFAULT 항목을 지정할 수 있다.

- 테이블을 복사하거나 일부를 별도로 저장하는 용도로 사용한다.

 

ex) 사번, 이름, 업무, 부서 정보만을 갖는 테이블을 생성한다.

SQL> CREATE TABLE emp2
  2  AS
  3  SELECT eno 사번, ename 이름, job 업무, d.dno 부서번호, dname 부서
  4  FROM emp e, dept d
  5  WHERE d.dno = e.dno;
SQL> DESC emp2;
	=    
SQL> CREATE TABLE emp2 (사번, 이름, 업무, 부서번호, 부서)
  2  AS
  3  SELECT eno, ename, job, d.dno, dname
  4  FROM emp e, dept d
  5  WHERE d.dno = e.dno;

 

2-2 테이블 내용 완전 삭제

 TRUNCATE TABLE 테이블;

- 테이블의 내용(행, 공간)을 완전히 삭제한다.

- DDL 작업으로 ROLLBACK 할 수 없다.

- 'DELETE FROM 테이블' 과 비슷하지만 공간까지 해제하는 차이가 있다.

    DELETE에 비해 속도가 빠르다.

 

3. 제약 조건 : PK, FK

3-1 제약 조건의 이해

- 제약 조건

: 테이블 단위에서 정의되고 적용된다.

: 종속성이 존재하는 경우 테이블의 삭제를 막아준다.

: 자료가 삽입, 갱신, 삭제될 때마다 규칙이 적용된다.

: 일시적으로 활성화하거나 비활성화하는 것이 가능하다.

: 제약 조건은 개체처럼 관리되므로 반드시 이름이 필요하다.

 

제약 조건은 잘못된 데이터가 입력되는 것을 방지해준다.

 

- 오라클에서 제공되는 제약 조건

: PRIMARY KEY

: FOREIGN KEY

: UNIQUE KEY

: NOT NULL

: CHECK

 

● PK, FK

- PK

: Primary Key, 주키, 주식별자, 식별자

: 테이블마다 한 개만 정의할 수 있다.

: 테이블 내에 모든 행을 유일하도록 식별해주는 컬럼이다.

: 모든 컬럼은 PK 컬럼에 함수적 종속 관계(functional dependency)를 갖는다.

: 중복될 수 없고 NULL을 허용하지 않는다. (이 조건을 만족한다고 꼭 PK인 것은 아니다.)

: 고유 인덱스(= 중복된 값이 없는 인덱스)가 자동으로 생성된다. 

 

PK는 임의로 설정되는 컬럼으로 다른 행과 값이 겹치지 않는다(= 유니크하다).

 

- FK

: Foreign Key, 외부키, 외부식별자

: 테이블 간 관계(Relationship)를 의미한다.

: 항상 부모자식 관계이다.

: 자식 테이블의 참조 컬럼에 지정한다.

: 두 컬럼에 데이터 타입이 일치해야 한다.

: PK나 UK만 참조 가능하다. (UK를 참조하는 경우는 적다.)

 

● PK, FK 설정

// 테이블 레벨에서 정의 //

SQL> CREATE TABLE 테이블(
 2   .....
 3   CONSTRAINT 제약조건이름 PRIMARY KEY (컬럼));
SQL> CREATE TABLE 테이블(
 2   .....
 3   CONSTRAINT 제약조건이름 FOREIGN KEY (컬럼)
 4   REFERENCES 참조할_테이블 (참조할_컬럼) [ON DELETE CASCADE]);
 
 
// 컬럼 레벨에서 정의  //

SQL> CREATE TABLE 테이블(
 2   컬럼 데이터_타입 CONSTRAINT 제약조건이름 PRIMARY KEY,
 3   ......
SQL> CREATE TABLE 테이블(
 2   컬럼명 데이터_타입 CONSTRAINT 제약조건이름 FOREIGN KEY
 3                           REFERENCES 참조할_테이블 (참조할_컬럼) 
 4                           [ON DELETE CASCADE],
 5   .....

- 제약 조건 이름은 임의로 지정한다.

- 컬럼 레벨, 테이블 레벨에서 모두 정의 가능하나 테이블 레벨 정의를 권장한다.

 

3-2 제약 조건 검색

● PK, FK 설정 테이블 생성

dept, emp 테이블을 생성한다.

- 테이블 생성 전에 두 테이블을 삭제하고 PK, FK를 추가해서 테이블을 생성한다.

- 당연히 부모 테이블을 먼저 생성한다.

 

※ dept 테이블

SQL> DROP TABLE emp;
SQL> DROP TABLE dept;
SQL> PURGE RECYCLEBIN;

SQL> CREATE TABLE dept (
  2  dno VARCHAR2(2),
  3  dnameVARCHAR2(15),
  4  loc VARCHAR2(9),
  5  CONSTRAINT dept_dno_pk PRIMARY KEY(dno) 
  6  );

 

기존 테이블 생성문에서 5번째 줄의 문장을 추가해 PK를 지정한다.

 

 

※ emp 테이블

SQL> CREATE TABLE emp (
  2  eno VARCHAR2(4),
  3  enameVARCHAR2(15),
  4  sex VARCHAR2(4),
  5  job VARCHAR2(12),
  6  mgr VARCHAR2(4),
  7  hdateDATE,
  8  sal NUMBER,
  9  comm NUMBER,
  10  dno VARCHAR2(2),
  11  CONSTRAINT emp_eno_pk PRIMARY KEY (eno),
  12  CONSTRAINT emp_mgr_fk FOREIGN KEY (mgr)  REFERENCES emp (eno),
  13  CONSTRAINT emp_dno_fk FOREIGN KEY (dno)  REFERENCES dept (dno)
  14  );

 

기존 테이블 생성문에서 11, 12, 13번째 줄의 문장을 추가해 PK와 FK를 지정한다.

 

 

● 제약 조건에 의한 무결성 통제

SQL> INSERT INTO dept (dno, dname, loc) VALUES ('10','개발','서울');
SQL> INSERT INTO emp (eno,ename, dno) VALUES ('2000','문시현','10');
SQL> COMMIT;

SQL> INSERT INTO dept (dno, dname, loc) VALUES ('10','총무','부산');
				// ORA‐00001: 무결성제약조건.... 에러발생
SQL> INSERT INTO emp (eno,ename, dno) VALUES ('2001','손하늘','20');
				// ORA‐02291: 무결성제약조건.... 에러발생
SQL> DELETE FROM dept WHERE dno = '10';
				// ORA‐02291: 무결성제약조건.... 에러발생
SQL> UPDATE emp SET dno= '20' WHERE ename= '문시현';
				// ORA‐02291: 무결성제약조건.... 에러발생

- 제약 조건은 입력(수정, 삭제) 작업 시에 발생한다.

- 제약 조건은 잘못된 정보의 입력(수정, 삭제) 작업을 방지한다.

 

● 테이블 상세 도표

※ professor 테이블 (교수)

컬럼명 pno pname section orders hiredate
PK/UK/NOTNULL PK        
참조테이블          
참조컬럼          
CHECK          
데이터타입 VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 DATE
길이 8 15 15 15  

 

※ course 테이블 (과목)

컬럼명 cno cname st_num pno
PK/UK/NOTNULL PK      
참조테이블       professor
참조컬럼       pno
CHECK        
데이터타입 VARCHAR2 VARCHAR2 NUMBER VARCHAR2
길이 8 24   8

 

이전에 실습했던 ERD를 이용한 테이블 표현은 위와 같이 테이블 상세 도표 작성이 선행된 후 진행되는 것이 본래 순서이다.

ERD를 이용한 테이블 표현

 

+ (2025-03-19 추가)

● 제약 조건 검색

SQL> SELECT c.table_name, c.constraint_name, c.constraint_type,
  2         c.status, s.column_name
  3  FROM user_constraints c, user_cons_columns s
  4  WHERE c.constraint_name = s.constraint_name
  5  AND c.table_name in (검색_대상_테이블_목록)
  6  ORDER BY c.table_name;
 
 
SQL> SELECT  p.table_name 상위테이블, p.constraint_name 상위제약조건,       
  2          c.table_name 하위테이블, c.constraint_name 참조제약조건
  3  FROM user_constraints p, user_constraints c
  4  WHERE c.r_constraint_name = p.constraint_name
  5  AND p.table_name in (검색_대상_테이블_목록)
  6  ORDER BY p.table_name;

 

- USER_CONSTRAINTS, USER_CONS_COLUMNS 로부터 제약 조건을 검색한다.

'일일 정리' 카테고리의 다른 글

semi-project 기업 네트워크 환경 구축  (0) 2025.03.19
제약 조건: (UK, NOT NULL, CHECK), 제약 조건 관리 - 추가 삭제와 비활성화, 인덱스 구조와 이해  (0) 2025.03.19
Samba  (1) 2025.03.10
PHP - DB 접속  (0) 2025.03.09
DML - INSERT, UPDATE, DELETE, Transaction과 Lock  (0) 2025.03.06

'일일 정리'의 다른글

  • 현재글테이블 생성과 데이터 타입(DDL), 테이블 관리, 제약 조건 : PK, FK

관련글

티스토리툴바