SQL 기초 - 7. 제약(제약, 기본키, 시퀀스)

이 글은 '김상형의 SQL 정복'의 예제 코드를 설명하는 방식으로 SQL 쿼리 예제를 소개하고 있습니다.

 

SQL 기초 - 7. 제약(제약, 기본키, 시퀀스)

컬럼 무결성 : 컬럼 하나에 저장되는 원자적인 값을 점검(도메인 무결성). 타입 지정, 널 허용 여부, 체크, 기본값 등의 제약이 있다.

엔터티 무결성 : 레코드끼리 중복값을 가지지 않도록 하여 유일한 식별자를 관리한다. 기본키와 유니크 제약이 있다.

참조 무결성 : 테이블간의 관계를 구성하는 키가 항상 유효하도록 관리하며 외래키 제약으로 관리한다. 이쪽 테이블이 참조하는 정보가 저쪽 테이블에 반드시 존재해야 한다.

-- 평범한 데이터 삽입...
INSERT INTO tCity VALUES ('평양','엄청 넓음','꽤 많을걸','n','조선인민민주주의공화국');

-- 특정 열만 지정해서 삽입할 수 있다.
INSERT INTO tCity (name, popu, metro, region) VALUES ('울산', 114, 'y', '경상');
INSERT INTO tCity (name, metro, region) VALUES ('삼척', 'n', '강원');

INSERT INTO tCity (area, popu, metro, region) VALUES (456, 123, 'n', '충청');
INSERT INTO tCity (name, area, popu) VALUES ('의정부', 456,123);

-- NOT NULL은 NULL 값을 허용하지 않겠다는 뜻이다.
CREATE TABLE tNullable
(
    name CHAR(10) NOT NULL,
    age INT
);

-- DEFAULT를 줌으로써, 기본 값을 설정할 수 있다.
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
);

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

-- DEFAULT 설정을 했다고 생략해도 되는게 아니라, DEFAULT를 넣어주어야 한다.
INSERT INTO tCityDefault VALUES ('강릉', 1111, 22, '강원');			-- 에러
INSERT INTO tCityDefault VALUES ('강릉', 1111, 22, DEFAULT, '강원');	-- 정상 실행

UPDATE tCity_Default SET metro = DEFAULT WHERE name = '인천'

-- CHECK를 이용해, 해당 컬럼이 어떤 값을 가져야하는지 제약 조건을 줄 수 있다.
CREATE TABLE tCheckTest
(
	gender CHAR(3) NULL CHECK(gender = '남' OR gender = '여'),
	grade INT NULL CHECK (grade >= 1 AND grade <= 3),
	origin CHAR(3) NULL CHECK(origin IN ('동','서','남','북')),
	name CHAR(10) NULL CHECK(name LIKE '김%')
);

INSERT INTO tCheckTest (gender) VALUES ('여');
INSERT INTO tCheckTest (grade) VALUES (1);
INSERT INTO tCheckTest (origin) VALUES ('동');
INSERT INTO tCheckTest (name) VALUES ('김좌진');

-- 아래의 쿼리들은 제약 조건 위반으로 추가가 되지 않는다. (체크 제약조건 위배 오류)
INSERT INTO tCheckTest (gender) VALUES ('노');
INSERT INTO tCheckTest (grade) VALUES (0);
INSERT INTO tCheckTest (origin) VALUES ('중');
INSERT INTO tCheckTest (name) VALUES ('청산리');

UPDATE tCheckTest SET grade = 4 WHERE grade IS NOT NULL;

-- region 컬럼의 제약 조건을 확인 (아래 줄, 쿼리가 아님)
region CHAR(6) NOT NULL CHECK (region IN ('경기', '충청', '강원', '경상', '전라', '제주'))
INSERT INTO tCity3 VALUES ('울릉',72,1,'n','우산');

INSERT INTO tCity VALUES ('춘천',1116,27,'n','강원');

-- PRIMARY KEY 조건을 줄 때, 마지막 줄에서 지정 가능하다 (테이블 제약)
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)
); 

-- 컬럼 옆에 쓰면 컬럼 제약이 된다. 다만, PRIMARY KEY는 하나만 사용 가능하다.
CREATE TABLE tCity
(
	name CHAR(10) PRIMARY KEY,
	region CHAR(6) PRIMARY KEY,
	area INT NULL ,
	popu INT NULL ,
	metro CHAR(1) NOT NULL
);

-- 여러 요소를 묶어 PRIMARY KEY를 지정하고 싶을 때는 복합문을 사용해야 한다(테이블 제약).
CREATE TABLE tCityCompoKey
(
	name CHAR(10) NOT NULL,
	region CHAR(6) NOT NULL,
	area INT NULL ,
	popu INT NULL ,
	metro CHAR(1) NOT NULL,
	CONSTRAINT PK_tCity_name_region PRIMARY KEY (name, region)
);

INSERT INTO tCityCompoKey VALUES ('광주', '전라', 123, 456, 'y');
INSERT INTO tCityCompoKey VALUES ('광주', '경기', 123, 456, 'n');

-- UNIQUE는 유일한 값을 가지도록 하는 제약으로, NULL도 단 하나밖에 허용하지 않는다.
-- 여러 컬럼이 동시에 UNIQUE할 수 있다.
CREATE TABLE tCityUnique
(
	name CHAR(10) PRIMARY KEY,
	area INT NULL ,
	popu INT UNIQUE NULL,
	metro CHAR(1) NOT NULL,
	region CHAR(6) NOT NULL
);

-- UNIQUE도 복합문으로 엮을 수 있다.
CREATE TABLE tCityUnique
(
	name CHAR(10) PRIMARY KEY,
	area INT NULL ,
	popu INT NULL,
	metro CHAR(1) NOT NULL,
	region CHAR(6) NOT NULL,
	CONSTRAINT Unique_tCity_area_popu UNIQUE(area, popu)
);

-- SEQUENCE를 생성하면 객체가 생성된 후 값이 계속 갱신된다. (static value라고 생각하면 편하다)
CREATE SEQUENCE seqSale START WITH 1 INCREMENT BY 1;

CREATE TABLE tSale
(
	saleno INT PRIMARY KEY,
	customer VARCHAR(20),
	product VARCHAR(30)
);

-- NEXTVAL을 이용해 접근하면 되고, 1씩 증가한다.
INSERT INTO tSale VALUES (seqSale.NEXTVAL, '단군', '지팡이');
INSERT INTO tSale VALUES (seqSale.NEXTVAL, '고주몽', '고등어');

INSERT INTO tSale VALUES (NEXT VALUE FOR seqSale, '단군', '지팡이');
INSERT INTO tSale VALUES (NEXT VALUE FOR seqSale, '고주몽', '고등어');

DELETE FROM tSale WHERE saleno = 2;

오라클 : INSERT INTO tSale VALUES (seqSale.NEXTVAL, '박혁거세', '계란');
MSSQL : INSERT INTO tSale VALUES (NEXT VALUE FOR seqSale, '박혁거세', '계란');

INSERT INTO tSale VALUES (2, '고주몽', '고등어');

-- 오라클에서는 GENERATED AS IDENTITY로 내재적인 SEQUENCE를 구현할 수 있다.
CREATE TABLE tSaleSeq
(
	saleno INT GENERATED AS IDENTITY PRIMARY KEY,
	customer VARCHAR(20),
	product VARCHAR(30)
);

INSERT INTO tSaleSeq (customer, product) VALUES ('단군', '지팡이');
INSERT INTO tSaleSeq (customer, product) VALUES ('고주몽', '고등어');

saleno INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
saleno INT GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,

INSERT INTO tSale (saleno, customer, product) VALUES (5, '단군', '지팡이');	-- 가능
INSERT INTO tSale (saleno, customer, product) VALUES (NULL, '고주몽', '고등어');	-- 에러

-- IDENTITY는 SQL SERVER에서 지원하는 문법이다.
CREATE TABLE tSaleId
(
	saleno INT IDENTITY PRIMARY KEY,
	customer VARCHAR(20),
	product VARCHAR(30)
);

INSERT INTO tSaleId (customer, product) VALUES ('단군', '지팡이');
INSERT INTO tSaleId (customer, product) VALUES ('고주몽', '고등어');

DELETE FROM tSaleId WHERE saleno = 2;
INSERT INTO tSaleId (customer, product) VALUES ('박혁거세', '계란');

INSERT INTO tSaleId (saleno, customer, product) VALUES (2, '고주몽', '고등어');

SET IDENTITY_INSERT tSaleId ON;
INSERT INTO tSaleId (saleno, customer, product) VALUES (2, '고주몽', '고등어');
SET IDENTITY_INSERT tSaleId OFF;

INSERT INTO tSaleId VALUES ('왕건', '너구리');

UPDATE tSaleId SET product = '짜파게티' WHERE saleno = @@IDENTITY;

-- AUTO_INCREMENT는 MariaDB에서 지원하는 문법이다.
CREATE TABLE tSale
(
	saleno INT AUTO_INCREMENT PRIMARY KEY,
	customer NCHAR(10),
	product NCHAR(30)
);

INSERT INTO tSale (customer, product) VALUES ('단군', '지팡이');
INSERT INTO tSale (customer, product) VALUES ('고주몽', '고등어');

DELETE FROM tSale WHERE saleno = 2;
INSERT INTO tSale (customer, product) VALUES ('박혁거세', '계란');

INSERT INTO tSale (saleno, customer, product) VALUES (2, '고주몽', '고등어');

ALTER TABLE tSale AUTO_INCREMENT = 100;

INSERT INTO tSale (customer, product) VALUES ('왕건', '너구리');
UPDATE tSale SET product = '짜파게티' WHERE saleno = LAST_INSERT_ID();