[ SQLD ] SQL - DDL 240815

- 테이블의 인스턴스를 변형하는 DML ( 데이터 조작어 )

- 테이블의 스키마를 변형하는 DDL ( 데이터 정의어 )

 

테이블 생성

- 테이블명 ( 테이블 이름 ) 작성 규칙

1) 객체를 의미할 수 있는 이름으로 단수형을 권고하며 ( 권장사항 ),

2) 다른 테이블의 이름과 중복되지 않아야 한다 ( 필수사항 ).

예를 들어 여러 학생의 정보를 담을 테이블을 생성할 경우 테이블 이름을 Student 라고 작성할 수 있다. 

- 칼럼명 ( 칼럼 이름 ) 작성 규칙

1) 하나의 테이블 안에서 칼럼명은 중복되지 않아야 한다.

→ 예를 들어 A테이블 내에 ID 라는 컬럼이 2개 존재할 수 없다.

그러나 A테이블이 아닌 다른테이블에 ID라는 컬럼이 존재할 수 있다.

하지만 여러 테이블을 통틀어 동일한 컬럼이 존재하는 것은 권장사항이 아니므로

A테이블과 B테이블에 각각 ID라는 컬럼을 만들고 싶다면 A테이블에는 A_ID,

B테이블에는 B_ID라고 작명하는 것이 좋다.

2) 테이블을 생성할 때 칼럼들을 나열하는데 각 칼럼들은 괄호 안에서 콤마로 구분되고,

각 칼럼마다 뒤에 ( 데이터의 ) 유형이 반드시 지정되어야 한다.

- 테이블 / 칼럼 작명시 공통적으로 해당되는 작성 규칙

1) 사전에 정의된 예약어 ( Reserved word ) 는 사용이 불가하다

→ 예약어는 사전에 이미 정의된 키워드를 의미하며 예를 들어 SELECT, UPDATE, FROM 등이 해당한다.

2) 테이블명과 칼럼명에 문자, 숫자, 일부기호 (_, $, #) 만 허용되는데

테이블명과 칼럼명은 반드시 문자로 시작해야 한다 ( 숫자, 기호로 시작할 수 없다 )

3)  제약조건 ( PK, FK 등 ) 도 작명이 가능한데 다른 제약조건의 이름과 중복되지 않아야 한다.

제약조건명은 여러 테이블에 걸쳐서 유일해야 한다.

 

Oracle의 주요 데이터 타입

문자 타입을 나타내는 CHAR와 VARCHAR2, 숫자타입을 나타내는 NUMBER,

날짜 타입을 나타내는 DATE가 있다. CHAR와 CHAR 타입의 경우

DATE는 날짜와 시각 정보를 포함하고, NUMBER는 정수, 실수등의 숫자 정보를 나타낸다.

NUMBER를 작성할 때 전체 자리수와 소수 부분 자리수를 명시하는데 NUMBER(8,2) 이렇게 작성한다.

전체자리수 8에서 소수자리수 2를 제외한 6이 정수 부분을 의미한다. 즉 정수 부분은 6자리, 소수 부분은 2자리다.

CHAR와 VARCHAR2의 차이는 문자열의 고정 유무다. CHAR 타입은 고정문자열이고, VARCHAR2는 가변 길이 문자열이다.

CHAR 타입의 경우 변수에 할당된 값이 고정 길이보다 작은 경우, 나머지 공간에 공백이 채워진다.

VARCHAR2의 경우 변수에 할당된 값이 고정 길이보다 작은 경우, 나머지 공간은 사용하지 않는다.

예를 들어 4자리가 주어졌을 때, CHAR타입의 경우 AA데이터 값을 입력하면

AA의 2자리에 공백 2자리를 더해 4자리를 만들어 저장한다. 그래서 'AA'를 저장하든 'AA '을 저장하든 값은 같다.

그런데 VARCHAR2 타입의 경우 가변 길이 문자열이기 때문에 AA를 입력하면 'AA'가 저장되고,

'AA '를 입력하면 'AA '가 저장되기 때문에 'AA'와 'AA '는 동일한 값이 아니라는 결과가 나온다.

 

제약 조건

- PRIMARY KEY는 NOT NULL 이기도 하고 UNIQUE 하기도 하고 그리고 해당 테이블을 대표하는 컬럼이다.

→ NOT NULL & UNIQUE 한 컬럼이 모두 PRIMARY KEY는 아니다.

NOT NULL & UNIQUE 조건은 PRIMARY KEY가 되기 위한 필요조건이지 충분조건은 아니다.

- FOREIGN KEY는 다른 테이블의 기본키 ( PRIMARY KEY, PK ) 를 참조하는 컬럼에 외래키를 지정하는 것이다.

외래키 지정시 참조 무결성 제약 옵션을 선택한다.

 

SQL문으로 실습 및 해석하기

- 테이블을 생성하는 SQL문은 CREATE TABLE 테이블명 ( 컬럼명1 데이터타입1, 컬럼명2 데이터타입2, 컬럼3 데이터타입3,...);

 

CREATE TABLE STADIUM ( → 테이블을 생성할 것이고, 테이블 이름은 STADIUM 이다.

STADIUM_ID                 CHAR(3) NOT NULL,

→ STADIUM_ID 컬럼에는 문자열 데이터만 작성할 수 있고,

길이는 3으로 정해져 있다 ( 고정 문자열 ) 그리고 이 컬럼에 빈 값은 담길 수 없다 ( NOT NULL )

STADIUM_NAME         VARCHAR2(40) NOT NULL,

→ STADIUM_NAME 컬럼에는 문자열 데이터만 작성할 수 있고,

가변 길이 문자열이다. 그리고 이 컬럼에 빈 값은 담길 수 없다 ( NOT NULL )

HOMETEAM_ID            CHAR(3),

SEAT_COUNT               NUMBER, → 숫자타입만 담길 수 있는 SEAT_COUNT 컬럼

ADDRESS                        VARCHAR2(60),

DDD                                  VARCHAR2(3),

TEL                                    VARCHAR2(10),

CONSTRAINT                STADIUM_PK PRIMARY KEY (STADIUM_ID)

→ 제약조건 선언, STADIUM_ID 컬럼에 PRIMARY KEY 제약조건을 설정하고, STADIUM_PK로 제약조건 이름을 짓는다.

);

 

FOREIGN KEY 선언시

CONSTRAINT TEAM_FK FOREIGN KEY ( STADIUM_ID ) REFERENCES STADIUM ( STADIUM_ID )

→ 제약조건 ( CONSTRAINT ) 선언, 제약조건명은 TEAM_FK고, TEAM_FK는 외래키인데, STADIUM_ID에 외래키를 설정한다.

이 외래키는 테이블 STADIUM의 기본키인 STADIUM_ID를 참조하는 것이다.

STADIUM 테이블이 먼저 생성되어 있어야 한다.

 

제약조건의 지정

 

DROP TABLE PLAYER1 CASCADE CONSTRAINT;

→ DROP 명령어는 DDL ( 데이터 정의어 ) 에 속하며, DROP TABLE1은 TABLE1 테이블을 삭제한다는 것,

CASCADE CONSTRAINT는 TABLE1 을 삭제할 때 설정되어 있던 제약조건들도 함께 삭제한다는 것을 의미한다.

 

1) 묵시적 방식으로 테이블 컬럼에 제약조건을 설정할 때

 

2) 명시적 방식으로 테이블 컬럼에 제약조건을 설정할 때 ( 제약조건명을 명시한다

→ 컬럼명 데이터타입 CONSTRAINT 제약조건명 제약조건 )

 

- 제약조건명을 몰아서 작성할 때

컬럼을 먼저 선언하고,

아래에 CONSTRAINT ( 제약조건 ) 를 몰아서 작성한다.

PLAYER_ID CHAR(7), → 컬럼 선언, PLAYER_ID 라는 컬럼에는 고정 문자열 ( CHAR ) 타입으로 크기는 7CONSTRAINT p3_pk_id PRIMARY KEY ( PLAYER_ID ),→ 제약조건을 설정할건데, 제약조건명은 p3_pk_id 고,

p3_pk_id 를 기본키로 설정, 기본키는 PLAYER_ID 컬럼이다.

 

모든 제약조건 관련 정보를 볼 수 있는 SQL문은

SELECT * FROM ALL_CONSTRAINTS;제약조건 관련 정보를 담는 테이블의 스키마 구조는 아래와 같다.

 

FK 제약조건의 옵션

- ex. CONSTRAINT fk1 FOREIGN KEY ( TEAM_ID ) REFERENCES TEAM ( TEAM_ID )

→ 제약조건을 선언, 제약조건명: fk1, fk1은 외래키, 외래키는 TEAM_ID 컬럼에 설정되어 있고,

TEAM_ID 컬럼은 TEAM 테이블의 ( 기본키인 ) TEAM_ID 컬럼을 참조한다.

ON DELETE CASCADE ON UPDATE RESTRICT; → FK 제약조건의 옵션

TEAM 테이블에서 하나의 레코드 삭제가 발생하면 TEAM 테이블의 TEAM_ID를 외래키로 참조하는 다른 테이블의 컬럼에서 문제가 발생한다. 어떤 테이블의 TEAM_ID 컬럼이 TEAM 테이블의 기본키인 TEAM_ID를 참조하는 외래키라면 (  어떤 테이블의 ) TEAM_ID 컬럼에 존재하는 값이 반드시 TEAM 테이블의 TEAM_ID 컬럼에 존재해야 하는데 TEAM 테이블에서 한 행의 레코드가 삭제된다면

( TEAM 테이블에서 ) 존재하지 않는 TEAM_ID 가 이를 참조하는 다른 테이블의 외래키에 존재할 수 있기 때문에 참조 무결성의 원칙에 위배되면서 아예 삭제가 되지 않는다 ( 단, FK 제약조건의 옵션을 설정하지 않았거나 RESTRICT 또는 NO ACTION 을 명시했을 경우 )

FK 제약조건에 옵션을 줄 때는 2가지를 생각해야 한다.

1) 어떤 명령어에 옵션을 줄 것인가?  ( 참조하는 컬럼에서 DELETE ( 삭제 ) 가 발생했을 때 또는 UPDATE ( 갱신 ) 가 발생했을 때 )

외래키이고, 외래키로 참조하는 어떤 테이블의 컬럼 ( 기본키 ) 에서

- DELETE가 발생했을 때 ON DELETE [ RESTRICT / NO ACTION / CASCADE / SET NULL ]

- UPDATE가 발생했을 때 ON UPDATE [ RESTRICT / NO ACTION / CASCADE / SET NULL ] 

FK 제약조건의 옵션중 RESTRICT는 기본 값 ( DEFAULT ) 으로 기본 값의 삭제 또는 갱신을 불허

 

※ PRIMARY KEY는 UPDATE라는 것이 존재하지 않는다.

예를 들어 PRIMARY KEY의 값이 바뀌었다는 것 ( 갱신 ) 은

한 건의 레코드가 삭제되면서 해당 레코드가 갖는 PRIMARY KEY도 함께 삭제되고,

해당 PRIMARY KEY를 제외한 다른 컬럼의 값들은 복사되고,

복사된 값들에 새로운 PRIMARY KEY가 생기는 경우 → 추후에 좀 더 자세히 알아봐야할 부분

 

기존 테이블을 활용해서 테이블을 생성할 때 ( SELECT 문을 활용 )

생성할 테이블( 명 )이 존재할 수 있으니 먼저 삭제 → DROP TABLE 생성할테이블명 CASCADE CONSTRAINT;

 

기존에 있는 어떤 테이블의 값들을 모두 복사하여 새로운 테이블 생성

→ CREATE TABLE 생성할테이블명

AS SELECT * FROM ( 데이터를 )복사할테이블명;

이렇게 기존에 존재하던 테이블의 스키마의 구조와 모든 인스턴스의 값을 복사하여 새로운 테이블을 생성했을 때

제약 조건은 NOT NULL만 복제된다. PK, FK, UNIQUE, CHECH 등은 수동으로 추가해주어야 한다.