SQL 뽀개기 - 6 | 제약조건

1. 제약

무결성(Integrity) : 모든 데이터가 결함없이 완벽한 상태

 

클라이언트에서 담당하는 무결성 처리 규칙

입력할 정보에 맞는 입력타입(라디오 버튼, 체크박스, 리스트 박스 등등..)으로 오입력 차단 등..

 

서버측에서 담당하는 무결성 처리 규칙

1. 컬럼 무결성 : 컬럼 하나에 저장되는 원자적인 값을 점검. 도메인 무결성이라고도 한다. 

-> 타입 지정, 널 허용여부, 체크, 기본값 등

2. 엔티티 무결성 : 레코드끼리 중복값을 가지지 않도록 하여 유일한 식별자를 관리.

-> 기본키, 유니크 제약

3. 참조 무결성 : 테이블간의 관계를 구성하는 키가 항상 유효하도록 관리하며 외래키 제약으로 관리.

-> 이쪽 테이블이 참조하는 정보가 저쪽 테이블에 반드시 존재해야 한다

 

제약(Constraint) - 조건을 위반하는 데이터를 방지하여 완전성을 보장하는 규칙

- 필드의 입력값 형태를 미리 지정해두어 규칙에 맞는 데이터만 입력받을 수 있도록 한다

- NULL, NOT NULL, PRIMARY KEY 등...

 

NULL -> NULL 상태를 허용한다는 뜻(NULLABLE)

NOT NULL -> NULL 상태를 허용하지 않는다는 뜻

 

CREATE TABLE tNullable
(
    name CHAR(10) NOT NULL,
    age INT NOT NULL,
    sex CHAR(1) NOT NULL,
    
    grade INT NULL,
    birth DATE NULL
)

name, age, sex는 NULL값을 받을 수 없다

grade, birth는 NULL값을 받을 수 있다

- (NOT) NULL을 안적으면 default값은 NULL을 허용하는걸로 한다

 

기본값(Default)

- NULL 허용 대신 기본값을 사용하는게 NULL을 고려할 필요가 없어 성능 상 유리하다

CREATE TABLE tCityDefault
(
    name CHAR(10) PRIMARY KEY,
    area INT NULL,
    popu INT NULL,
    metro CHAR(1) DEFAULT 'n' NOT NULL,
    region CHAR(6) NOT NULL
);

- DEFAULT 키워드는 NULL 허용 속성보다 앞에 와야한다

- DEFAULT 키워드를 지정하면 필드에 값을 입력하지 않으면 기본값이 채워진다

INSERT INTO tCityDefault (name, area, popu, region)
VALUES ('진주', 712, 34, '경상');

INSERT INTO tCityDefault (name, area, popu, metro, region)
VALUES ('인천', 1063, 295, 'y', '경기');

INSERT INTO tCityDefault VALUES ('강릉', 1111, 22, '강원'); -- ERROR
INSERT INTO tCityDefault VALUES ('강릉', 1111, 22, DEFAULT, '강원'); -- CORRECT

- 필드 목록을 생략하고  INSERT를 하는 경우 값 목록이 완전해야 한다. 

-> DEFAULT라고 적어줘야됨

- 기본값 유무와 NULL 허용 여부는 완전히 별개이다

-> 기본값이 있더라도 NULL을 직접 입력할 수 있다. 기본값은 단지 생략시 적용할 값에 불과.

 

★★★★ 개 중요한 부분 ★★★★

한글 1글자는 2바이트

CREATE TABLE tStaffDefault
(
  name VARCHAR(5) PRIMARY KEY,
  depart VARCHAR(5) DEFAULT '영업부' NOT NULL,
  gender CHAR(1) NOT NULL,
  joindate DATE NULL,
  grade VARCHAR(5) DEFAULT '수습' NOT NULL,
  salary INT DEFAULT '280' NULL,
  score DECIMAL(5, 2) DEFAULT '1.0' NULL
);

이거를 실행해 보면 뜬금없이 에러가 난다

ORA-01401 : 열에 입력한 값이 너무 큽니다

 

배운대로 썼는데 안된다고 에러가 나니까 짜증이 나서 왜이런가 찾아봤는데

한글은 한글자에 2바이트이기 때문에 "영업부" 를 담기 위해서는 최소 6바이트가 있어야 하기 때문에 에러가 난 것.

 

CREATE TABLE tStaffDefault
(
  name CHAR(5) PRIMARY KEY,
  depart CHAR(15) DEFAULT '영업부' NOT NULL,
  gender CHAR(1) NOT NULL,
  joindate DATE NULL,
  grade VARCHAR(15) DEFAULT '수습' NOT NULL,
  salary INT DEFAULT '280' NULL,
  score DECIMAL(5, 2) DEFAULT '1.0' NULL
);

그래서 CHAR의 크기를 좀 넉넉하게 잡아주니까 테이블이 정상적으로 생성이 된다

 

체크 제약 : 필드의 값 종류를 제한. 논리적인 값의 형식을 점검한다 

CREATE TABLE tCheckTest
(
    gender CHAR(3) NULL CHECK(gender = '남' OR gender = '여'),
    grade INT NULL CHECK(grade BETWEEN 1 AND 3),
    origin CHAR(3) NULL CHECK(origin IN('동', '서', '남', '북')),
    name CHAR(10) NULL CHECK(name LIKE '김%')
);

CREATE TABLE tCheckTest
(
    name CHAR(10) PRIMARY KEY,
    depart CHAR(10) CHECK(depart IN('영업부', '총무부', '인사과')) NOT NULL,
    gender CHAR(4) CHECK(gender = '남' OR gender = '여') NOT NULL,
    salary INT CHECK(salary > 0) NOT NULL    
    
);

체크 제약조건에 해당하지 않는 값이 들어오면 삽입을 거부한다

 

2. 기본키

 

테이블의 특정 레코드를 읽거나 변경하기 위해서는 레코드끼리 구분할 수 있는 고유의 키(식별자)가 필요하다

-> 이는 중복되서는 안되고 NULL이어서도 안된다

 

조건을 만족하는 필드를 후보키(Candidate Key) 라고 하며 한 테이블에는 여러개의 후보키가 있다.

이 중 레코드를 가장 잘 대표하는 후보키 중 하나를 골라 기본키(Primary Key, PK)로 선정한다.

 

기본키는 다음과 같은 요건을 만족해야 함

1. 대표성 - 레코드를 상징하는 값이어야 한다

2. 자주 참조하는 속성 - 기본키에는 기본적으로 인덱스가 생성되어 검색 효율이 좋다

3. 가급적 짧은 속성 - 테이블간의 연결고리가 됨으로 비교속도가 빨라야 효율이 좋다

+ 기본키는 중복, NULL을 허락하지 않는다

 

컬럼 제약 : 컬럼 선언 뒤에 위치

- NULL 여부, 기본값, 체크 등 컬럼에 대한 속성

- [CONSTRAINT 이름] PRIMARY KEY

 

테이블 제약 : 테이블 선언 뒤에 위치

- [CONSTRAINT 이름] PRIMARY KEY(대상필드)

 

- 제약 이름을 생략하면 오라클이 자동으로 임의의 ID를 부여한다

- PRIMARY KEY 제약은 NOT NULL 제약을 겸한다

CREATE TABLE tCity 
(
    name CHAR(10),
    area INT NULL,
    popu INT NULL,
    metro CHAR(1) NOT NULL,
    region CHAR(6) NOT NULL,
    CONSTRAINT PK_tCity_name PRIMARY KEY(name) -- 테이블 제약
)

 

3. 복합키

- 두개 이상의 필드를 묶어 기본키로 지정하는 것을 복합키(Composite Key)라고 한다.

- 복합키를 지정할때는 컬럼 제약으로는 불가능, 테이블 제약을 사용해야 한다

CREATE TABLE tCity 
(
    name CHAR(10),
    region CHAR(6) NOT NULL,
    area INT NULL,
    popu INT NULL,
    metro CHAR(1) NOT NULL,
    CONSTRAINT PK_tCity_name PRIMARY KEY(name, region) -- 테이블 제약, 복합키
)

- 복합키도 중복을 허용하지 않으나 복합키를 구성하는 개별 키는 중복되어도 무방하다

- 모든 필드가 동시에 같지만 않으면 됨

 

+) 다른 테이블의 기본 키와 연결고리가 되는 키를 "외래키[참조키], (Foreign key)" 라고 한다.

 

4. 유니크

- 유니크 제약은 필드의 중복값을 방지하여 모든 필드가 고유한 값을 가지도록 강제한다

- 기본키는 NULL을 허용하지 않지만 유니크는 NULL을 허용한다

- NULL끼리도 중복될 수 없어 단 하나의 NULL만 존재할 수 있다

- UNIQUE와 NOT NULL을 동시에 지정하면 기본키와 유사하다 

- 기본키는 테이블당 하나만 지정할 수 있지만 유니크는 개수에 상관없이 얼마든지 지정할 수 있다

- 기본키는 자동으로 인덱스를 생성하여 레코드의 정렬순서를 결정하지만 유니크는 그렇지 않다

- 기본키를 보조하는 중복방지 제약. 

CREATE TABLE tCityUnique
(
    name CHAR(10) PRIMARY KEY,
    area INT NULL,
    popu UNIQUE NULL, -- 유닠 -> 필드의 모든 값이 중복불가
    metro CHAR(1) NOT NULL,
    region CHAR(6) NOT NULL
)

 

5. 시퀸스

5.1 - 일련번호 필드

> 기본키로 쓸만한 마땅한 필드가 없을때 기본키로 쓸만한 일련번호를 만들어 레코드의 고유성을 부여하는 필드

> 주로 정수형,, 1, 2, 3, 4,... 순

 

5.2 - 시퀸스 객체

시퀸스는 테이블과 상관없는 독립적인 객체

CREATE SEQUENCE seqSale START WITH 1 INCREMENT BY 1;

INSERT INTO tSale VALUES(seqSale.NEXTVAL, '123', 456);
INSERT INTO tSale VALUES(seqSale.NEXTVAL, '234', 123);

- 시퀸스.NEXTVAL으로 일련번호를 구한다

- 중간에 번호가 하나 빠지더라도 일련번호는 계속 증가한다

- 중간에 빠진 번호를 다시 사용하고 싶다면 시퀸스 대신 원하는 번호를 직접 삽입하면 된다

 

5.3 - 자동생성 시퀸스

CREATE TABLE tSaleSeq
(
    saleno INT GENERATED AS IDENTITY PRIMARY KEY,
    customer VARCHAR(20),
    product VARCHAR(20)
);

INSERT INTO tSaleSeq(customer, product) VALUES('홍길동', '사과');
INSERT INTO tSaleSeq(customer, product) VALUES('김길동', '바나나');

SELECT * FROM tSaleSeq;

- GENERATED AS IDENTITY를 붙여주자

- 여러 부가 옵션이 많은데 귀찮아서 생략..