01. Primary Key
01-01. 개요
기본키(Primary key)는 하나의 테이블에서 각 행을 구분하는 데에 사용되는 행이다.(행은 하나일 수도 있고 두 개 이상일 수도 있다.)
기본키는 not-null 제약조건과 unique 제약조건을 합친 형태이다. 아래는 SQL에서의 key에 대한 대략적인 설명이다.
[ Key란? ]
Key는 “무엇인가를 식별하는 고유 식별자”의 역할을 한다. 따라서 DB에서 Key는 다른 행과 구분할 수 있는 속성의 집합으로 생각하면 된다.
Key는 아래와 같이 두 속성을 갖는다.
최소성 - Key를 구성하고 있는 컬럼이 꼭 필요한 것인지 판단(레코드를 식별하는데 반드시 필요하지 않은 컬럼이 포함 되어있는 경우 최소성 만족 x)
유일성 - unique하여 행을 식별할 수 있는 역할을 하는지
[ Key의 종류와 특징 ]
key 종류 특징 기본키 - 모든 값은 유일한 값(Unique) - Null값은 올 수 없음 - 값이 변경될 가능성이 높은 속성은 기본키 설정 x - 최소성과 유일성 가짐 후보키 - 기본키로 사용할 수 있는 속성들 - 최소성과 유일성을 가짐 대체키 - 후보키 중 기본키를 제외한 후보키 슈퍼키 - 유일성을 만족하여 key의 역할은 할 수 있지만, 최소성을 만족하지 못한 key 예) (팀, 셀, 사번) 외래키 - 참조되는 테이블의 기본키와 대응되어 테이블 간의 참조 관계를 나타냄 - 외래키는 참조되는 테이블의 기본키에 없는 값은 들어갈 수 없음(= 참조 무결성 조건)
01-02. 예제
-- 예제 1) 테이블 생성 시 행 제약조건 지정
DROP TABLE IF EXISTS profile ;
CREATE TABLE profile(
id SERIAL PRIMARY KEY,
name VARCHAR,
address TEXT
);⇒ 테이블을 생성하는 구문 CREATE TABLE에서 column constraint로 기본키를 지정
-- 예제 2) 테이블 생성 시 테이블 제약조건 지정
DROP TABLE IF EXISTS profile ;
CREATE TABLE profile(
id SERIAL,
name VARCHAR,
address TEXT,
PRIMARY KEY(id)
);⇒ table contraint로 기본키를 슈퍼키로 지정할 경우 여러 개의 컬럼을 한 번에 지정해야함 / PRIMARY KEY(id, name) 형식으로 사용
-- 예제 3) 이미 생성된 테이블에 제약조건 추가
DROP TABLE IF EXISTS profile ;
-- 기본키를 지정하지 않고 테이블 생성
CREATE TABLE profile(
id SERIAL,
name VARCHAR,
address TEXT
);
-- ALTER TABLE을 사용하여 기본키 추가
ALTER TABLE profile
ADD PRIMARY KEY(name);-- 예제 4) 이미 생성된 테이블에 기본키가 되는 행 추가
DROP TABLE IF EXISTS profile ;
-- 기본키를 지정하지 않고 테이블 생성
CREATE TABLE profile(
id SERIAL,
name VARCHAR,
address TEXT
);
-- 테이블에 정보 저장
INSERT INTO profile(name, address)
VALUES
('KIMjw', 'Hanam-city'),
('KIMsw', 'Seoul-city'),
('Choise', 'Bupyeong-city')
;-- 예제 5) 제약조건 삭제
ALTER TABLE profile
DROP CONSTRAINT
profile_pkey;02. FOREIGN KEY
02-01. 개요
Key의 종류들에서도 간단히 설명했지만, 외래키는 두 테이블을 join할 때 참조되는 테이블의 기본키와 대응되는 키값이다. 테이블 간의 관계에 따라서 한 테이블에 여러 개의 외래키가 있을 수 있다.
참조하는 테이블의 기본키를 참조하는 외래키는 참조무결성 제약조건에 따라 기본키를 삭제하면 어떻게 처리할지 지정할 수 있다. 아래 예시를 살펴보자
예시로 알아보는 action의 종류
⇒ customers를 참조 되는 테이블(부모 테이블), reservations를 참조하는 테이블(자식 테이블)이라고 할 때, customers 에서 (3, 최승언)을 삭제하는 경우 action 별 동작
종류 부모 테이블 기본키 삭제 시 동작 SET NULL (104, 2022-05-05, Null)로 변경 SET DEFAULT (104, 2022-05-05, default value)로 변경 RESTRICT 제약조건 위반으로 error 발생 NO ACTION 제약조건 위반으로 error 발생 CASCADE customer_id가 3인 행 삭제
02-02 예제
-- 예제 1) 테이블 제약조건으로 지정
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS contacts;
CREATE TABLE customers(
customer_id INT,
customer_name VARCHAR(255) NOT NULL,
PRIMARY KEY(customer_id)
);
CREATE TABLE contacts(
contact_id INT GENERATED ALWAYS AS IDENTITY,
customer_id INT,
contact_name VARCHAR(255) NOT NULL,
PRIMARY KEY(contact_id),
CONSTRAINT
FOREIGN KEY(customer_id)
REFERENCES customers(customer_id)
);⇒ 고객정보를 담고 있는 customers 테이블(기본키 : customer_id), 고객의 예약 정보를 담고있는 contacts 테이블(기본키 : contact_id / 외래키 : customer_id)
03. CHECK CONSTRAINT
03-01 개요
CHECK 제약조건은 열의 값이 특정 조건을 만족하는지를 확인하는 제약조건이다. CHECK를 통해 데이터가 추가되거나 변경되기 전에 값을 평가한다.
03-02. 예제
-- 예제 1) 테이블 생성 시 제약조건 추가
DROP TABLE IF EXISTS employees;
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
first_name VARCHAR (50),
last_name VARCHAR (50),
birth_date DATE CHECK (birth_date > '1900-01-01'),
salary NUMERIC CHECK(salary > 0)
);⇒ birth_date의 값은 1900년 01월 01일 이후 값이 들어와야 하며, 그렇지 않은 경우에는 eorror 발생
-- 예제 2) CHECK 제약조건 추가하기
DROP TABLE IF EXISTS employees;
CREATE TABLE employees(
id SERIAL PRIMARY KEY,
first_name VARCHAR (50),
last_name VARCHAR (50),
birth_date DATE,
salary NUMERIC
);
ALTER TABLE employees
ADD CONSTRAINT date_salary_check(
birth_date > '1900-01-01'
AND salary > 0
);⇒ 위와 동일한 CHECK 제약조건 추가
04. UNIQUE CONSTRAINT
04-01. 개요
어떤 컬럼의 값이 각각 고유한 값을 가지는지 확인해야 할 때 UNIQUE 제약조건을 사용한다.(이메일, 전화번호 등의 컬럼에서)
04-02. 예제
-- 예제 1) 테이블 생성 시 제약조건 추가
CREATE TABLE person (
id SERIAL PRIMARY KEY,
first_name VARCHAR (50),
last_name VARCHAR (50),
email VARCHAR (50) UNIQUE
);⇒ email 컬럼에 unique 제약조건 추가
05. NOT NULL CONSTRAINT
05-01. 개요
DB에서 NULL은 ‘값이 없음 혹은 알 수 없음’을 의미한다. 데이터를 추가 및 변경할 때 NULL 값이 있으면 안 되는 컬럼에 대하여 NOT NULL 제약조건을 걸어 NULL 값이 발생하는 경우를 피할 수 있다.
NOT NULL 제약규제 역시 앞서 살펴본 다른 제약규제들과 마찬가지로 테이블을 생성할 때 컬럼에 지정할 수도 있고, 이미 존재하는 테이블에 제약조건을 추가할 수도 있다.
05-02. 예제
-- 예제 1) 테이블 생성 시 제약조건 추가
CREATE TABLE invoices(
id SERIAL PRIMARY KEY,
product_id INT NOT NULL,
qty numeric NOT NULL CHECK(qty > 0),
net_price numeric CHECK(net_price > 0)
);⇒ PostgreSQL에서는 제약조건을 콤마(,)없이 나열하는 것으로 컬럼에 여러가지 제약조건을 걸 수 있다. NOT NULL을 명시적으로 사용하지 않았을 때에는 NULL이 default로 사용되며 이 경우 NULL이던 어떠한 값이 오던 관계없다.
-- 예제 2) 제약조건 추가
DROP TABLE IF EXISTS invoices;
-- NOT NULL이 없는 테이블 생성
CREATE TABLE invoices(
id SERIAL PRIMARY KEY,
product_id INT
qty numeric
net_price numeric CHECK(net_price > 0)
);
-- NOT NULL 제약조건 추가
ALTER TABLE invoices
ALTER COLUMNS product_id
SET NOT NULL,
ALTER COLUMNS qty
SET NOT NULL,
-- 예제 3) 두 컬럼이 모두 NULL인 경우만 제외하기
CREATE TABLE users (
id serial PRIMARY KEY,
username VARCHAR (50),
password VARCHAR (50),
email VARCHAR (50),
CONSTRAINT username_email_notnull CHECK (
NOT (
(username IS NULL OR username = '')
AND
(email IS NULL OR email = '')
)
)
);⇒ CHECK 제약조건 안을 보면, (username이 null이거나 빈칸인 경우) & (email이 null이거나 빈칸인 경우) 가 아닌지(not) 판단하는 제약조건 username_email_notnull을 제약조건으로 추가했다.
해당 테이블에 다음 두 INSERT 문을 실행하고 결과를 확인해보자.
-- 정삭 작동하는 코드
INSERT INTO users (username, email)
VALUES
('user1', NULL),
(NULL, 'email1@example.com'),
('user2', 'email2@example.com'),
('user3', '');
⇒ username과 email 중 값이 하나 이상 있으면 에러가 발생하지 않는다.
-- 에러 발생하는 코드
INSERT INTO users (username, email)
VALUES
(NULL, NULL),
(NULL, ''),
('', NULL),
('', '');
⇒ username과 email이 모두 null(혹은 빈칸)인 경우 에러가 발생한다.
