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 -> 가변형문자 15BYTECREATE 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 성적표;