SQL CREATE와 ALTER 그리고 제약 조건

1. DDL

 

- DDL : data definition language 데이터 정의어
- CREATE : 새로운 객체를 생성할 때 사용하는 명령어
- CREATE TABLE, USER, VIEW, SEQUENCE,TABLE

 

- 기존의 EMPLOYEES 테이블을 한글 버전으로 생성
- 테이블 정보 조회 : 테이블명 작성 -> 드래그 쉬프트 F4

- 실행화면

 

 

CREATE TABLE 테이블 생성

CREATE USER 계정 생성

CREATE SEQUENCE 시퀀스 생성

CREATE VIEW 뷰 생성

 

1.1 테이블 생성

CREATE TABLE 테이블명(
컬럼명 자료형(크기) [기본값] [NULL여부],
-- []는 선택 사항 기본값은 둘 다 NULL
...
컬럼명 자료형(크기) [기본값] [NULL여부]
);

CREATE TABLE 직원(
 -- 컬럼명 자료형(크기) 기본값 NULL 여부
 직원ID NUMBER(6) NOT NULL,
 이름 VARCHAR2(20),
 성 VARCHAR2(25) NOT NULL,
 이메일 VARCHAR2(25) NOT NULL,
 핸드폰 VARCHAR2(20),
 입사일 DATE NOT NULL,
 직업ID VARCHAR2(10) NOT NULL,
 급여 NUMBER(8,2),
 커미션비율 NUMBER(2,2),
 매니저ID NUMBER(6),
 부서ID NUMBER(4)
);

출력 : Table 직원이(가) 생성되었습니다.

 

1.1.1 테이블 생성 규칙

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

- 테이블명은 중복될 수  없다.

- 같은 테이블 내에서 컬럼명은 중복될 수 없다.

- 문자로 시작해야하며, 예약어는 사용 불가능하다.

 

1.1.2 실습

- 부서테이블을 한글VER 만들기

CREATE TABLE 부서(
부서ID NUMBER(4) not null,
부서명 VARCHAR2(30) not null,
매니저ID NUMBER(6),
지역ID NUMBER(4)
);

 

2. 제약 조건 

 

- ★★★ PRIMARY KEY(PK)

- UNIQUE  KEY(UK)

- NOT NULL

- CHECK

- ★★★ FOREIGN KEY(FK)

 

2.1 PRIMARY KEY(PK)

-식별자를 물리적 모델링 한것

-NOT NULL + UNIQUE = NULL 불가 + 중복 불가

- ★ 식별자 : 테이블에서 행을 식별할 수 있는 칼럼

- ex) 사원 ID

 

2.2 UNIQUE  KEY(UK)

- PRIMARY KEY와 다르게 NULL 값 입력 가능

- ★ 중복은 불가능

- ex) 이메일 등등

 

2.3 NOT NULL

- NULL값 불가능

 

2.4 CHECK

- 지정한 데이터만 입력 가능

- ex) 성별 등등

 

2.5 FOREIGN KEY(FK)

왜래키, 테이블끼리 연결되어 있는 관계를 물리적 모델링한 것

다른 테이블의 기본키를 참조하는 속성의 값

- ★ FOREIGN KEY 사용하면 데이터 무결성

ex) 직원은 부서에 소속될 수 있으며, 부서는 직원을 소속할 수 있다.

 

2.6. 무결성 제약조건

- 무결성 데이터에 결함이 없는 상태

- 즉, 데이터가 정확하고 유효하게 유지 된 상태

 

2.6.1 무결성 제약 조건

- 데이터베이스에 저장된 데이터의 무결성을 보장, 일관되게 유지하기 위함

 

- 개체 무결성 제약조건 : 기본키를 구성하는 속성은 NULL 값을 가질 수 없음

- 참조 무결성 제약조건 : 외래키는 참조할 수 없는 값을 지닐 수 없음

 

2.7 PK, UK, CHECK 문법

ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 제약조건(컬럼)

(테이블 변경)                  (제약조건 추가)                          (제약조건)

--PK 지정
ALTER TABLE 직원 ADD CONSTRAINT 직원_직원ID_PK PRIMARY KEY(직원ID);

--UK 지정
ALTER TABLE 직원 ADD CONSTRAINT 직원_이메일_UK UNIQUE(이메일);

--CHECK 지정(급여)
ALTER TABLE 직원 ADD CONSTRAINT 직원_급여_CK CHECK(급여 > 0);

 

2.8 FK 추가

- ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 제약조건(칼럼) REFERENCES 참조할 테이블(참조할 컬럼)
- FK는 UK이거나 PK일 때 참조가능! 따라서 부서테이블의 부서ID를 PK로 먼저 제약조건을 걸고 난 후에 직원테이블에서 가능

ALTER TABLE 부서 ADD CONSTRAINT 부서_부서ID_PK PRIMARY KEY(부서ID);
ALTER TABLE 직원 ADD CONSTRAINT 직원_부서ID_FK FOREIGN KEY(부서ID) REFERENCES 부서(부서ID);

 

2.9 기타(NOT NULL, 제약 조건 삭제, 제약 조건 조회)

--NOT NULL
ALTER TABLE 직원 MODIFY 이름 NOT NULL;

--제약 조건 삭제
ALTER TABLE 직원 DROP CONSTRAINT 직원_급여_CK;

-- 제약 조건 조회
SELECT * FROM USER_CONSTRAINTS
WHERE TABLE_NAME ='직원';

 

3. 실습

CREATE TABLE 제약조건테스트(
PK테스트 NUMBER PRIMARY KEY,
UK테스트 NUMBER UNIQUE,
NN테스트 NUMBER NOT NULL,
CK테스트 NUMBER CHECK(CK테스트 > 0)
);

CREATE TABLE 제약조건테스트2(
PK테스트 NUMBER,
UK테스트 NUMBER,
CK테스트 NUMBER,
부서ID NUMBER(4),
-- 제약 조건으 추가
CONSTRAINT 테스트2_PK테스트_PK PRIMARY KEY(PK테스트),
CONSTRAINT 테스트2_UK테스트_PK UNIQUE(UK테스트),
CONSTRAINT 테스트2_CK테스트_CK CHECK(CK테스트>0),
CONSTRAINT 테스트2_부서ID_FK FOREIGN KEY(부서ID) REFERENCES 부서(부서ID)
);

 

3.1 문제 

테이블명 : 네이버회원
컬럼/자료형 
ID –> 가변형문자 15BYTE
이름 -> 가변형문자 12 BYTE, NULL 불가
비밀번호 -> 가변형문자 16BYTE
생년월일 -> 날짜형
성별 -> 가변형문자 3BYTE

테이블명 : 네이버블로그
컬럼/자료형 
블로그번호 –> NUMBER
블로그제목 -> 가변형문자 100BYTE, NULL불가
블로그내용 -> 가변형문자 4000BYTE
ID -> 가변형문자 15BYTE

CREATE TABLE 네이버회원(
 회원ID VARCHAR2(15),
 이름 VARCHAR2(12) not null,
 비밀번호 VARCHAR2(16),
 생년월일 DATE,
 성별 VARCHAR2(3),
 CONSTRAINT 회원_ID_PK PRIMARY KEY(회원ID),
 CONSTRAINT 회원_성별_CK CHECK(성별 = '남' or 성별 = '녀')
 );

CREATE TABLE 네이버블로그(
 블로그번호 NUMBER,
 블로그제목 VARCHAR2(100) NOT NULL,
 블로그내용 VARCHAR2(4000),
 회원ID VARCHAR2(15),
 CONSTRAINT 블로그_번호_PK PRIMARY KEY(블로그번호),
 CONSTRAINT 블로그_회원ID_FK FOREIGN KEY(회원ID) REFERENCES 네이버회원(회원ID) 
 );

 

4. ALTER

-- 컬럼 추가
ALTER TABLE 네이버회원 ADD 나이 NUMBER(3);

-- 컬럼 삭제
ALTER TABLE 네이버회원 DROP COLUMN 나이;

-- 컬럼 속성 변경
ALTER TABLE 네이버회원 MODIFY 회원ID VARCHAR2(30);

--컬럼 속성 변경
ALTER TABLE 네이버회원 RENAME COLUMN A to B;

 

5. DROP

- DROP TABLE 테이블 삭제
- 테이블에 UK,PK가 다른 테이블에 참조가 되고 있는 경우 DROP TABLE로 삭제가 불가능
- 참조 되어 있을 때 삭제하려면 CASCADE CONSTRAINT 옵션 추가

DROP TABLE 네이버블로그;
DROP TABLE 네이버회원;

DROP TABLE 네이버회원 CASCADE CONSTRAINT;

 

5.1  DELETE, TUNCATE DROP 차이점

SELECT * FROM 성적표;
DELETE FROM 성적표; -- 데이터를 삭제, 다만 COMMIT전 까지 가능 하다.
ROLLBACK; -- 복구

-- 데이터를 영구적으로 삭제, 복구 불가능!
TURNCATE TABLE 성적표;
-- table 성적표가 잘렸습니다.

-- 테이블을 영구적으로 삭제, 복구 불가능하다.
DROP TABLE 성적표;