SKN/02. DB

08. CONSTRAINTS

claovy☘️ 2025. 2. 19. 12:38
💡CONSTRAINT는 제약조건으로 테이블에 데이터가 입력되거나 수정될 때의 규칙을 정의한다.
     데이터 무결성을 보장하는데 도움이 된다.

 

1. NOT NULL

  • NULL값을 허용하지 않는 제약조건 
# 제약조건 확인용 테이블 생성 및 테스트 데이터 INSERT 후 조회하기
DROP TABLE IF EXISTS user_notnull;
CREATE TABLE IF NOT EXISTS user_notnull (
    user_no INT NOT NULL,
    user_id VARCHAR(255) NOT NULL,
    user_pwd VARCHAR(255) NOT NULL,
    user_name VARCHAR(255) NOT NULL,
    gender VARCHAR(3),
    phone VARCHAR(255) NOT NULL,
    email VARCHAR(255)
) ENGINE=INNODB;

INSERT INTO user_notnull
(user_no, user_id, user_pwd, user_name, gender, phone, email)
VALUES
(1, 'user01', 'pass01', '홍길동', '남', '010-1234-5678', 'hong123@gmail.com'),
(2, 'user02', 'pass02', '유관순', '여', '010-777-7777', 'yu77@gmail.com');

SELECT * FROM user_notnull;
# not null 제약조건 에러 발생(null 값 적용)
INSERT INTO user_notnull
(user_no, user_id, user_pwd, user_name, gender, phone, email)
VALUES
(3, 'user03', null, '이순신', '남', '010-222-2222', 'lee222@gmail.com');

 

2. UNIQUE

  • 중복값 허용하지 않는 제약조건 
# 제약조건 확인용 테이블 생성 및 테스트 데이터 INSERT 후 조회하기
DROP TABLE IF EXISTS user_unique;
CREATE TABLE IF NOT EXISTS user_unique (
    user_no INT NOT NULL,
    user_id VARCHAR(255) NOT NULL,
    user_pwd VARCHAR(255) NOT NULL,
    user_name VARCHAR(255) NOT NULL,
    gender VARCHAR(3),
    phone VARCHAR(255) NOT NULL UNIQUE,
    email VARCHAR(255),
    UNIQUE (phone)
) ENGINE=INNODB;

INSERT INTO user_unique
(user_no, user_id, user_pwd, user_name, gender, phone, email)
VALUES
(1, 'user01', 'pass01', '홍길동', '남', '010-1234-5678', 'hong123@gmail.com'),
(2, 'user02', 'pass02', '유관순', '여', '010-777-7777', 'yu77@gmail.com');

SELECT * FROM user_unique;
# unique 제약조건 에러 발생(전화번호 중복값 적용)
INSERT INTO user_unique
(user_no, user_id, user_pwd, user_name, gender, phone, email)
VALUES
(3, 'user03', 'pass03', '이순신', '남', '010-777-7777', 'lee222@gmail.com');

 

3. PRIMARY KEY

  • 테이블에서 한 행의 정보를 찾기 위해 사용 할 컬럼을 의미한다.
  • 테이블에 대한 식별자 역할을 한다. (한 행씩 구분하는 역할을 한다.)
  • NOT NULL + UNIQUE 제약조건의 의미
  • 한 테이블당 한 개만 설정할 수 있음
  • 컬럼 레벨, 테이블 레벨 둘 다 설정 가능함
  • 한 개 컬럼에 설정할 수도 있고, 여러 개의 컬럼을 묶어서 설정할 수도 있음(복합키)
# 제약조건 확인용 테이블 생성 및 테스트 데이터 INSERT 후 조회하기
DROP TABLE IF EXISTS user_primarykey;
CREATE TABLE IF NOT EXISTS user_primarykey (
--     user_no INT PRIMARY KEY,
    user_no INT,
    user_id VARCHAR(255) NOT NULL,
    user_pwd VARCHAR(255) NOT NULL,
    user_name VARCHAR(255) NOT NULL,
    gender VARCHAR(3),
    phone VARCHAR(255) NOT NULL,
    email VARCHAR(255),
    PRIMARY KEY (user_no)
) ENGINE=INNODB;

INSERT INTO user_primarykey
(user_no, user_id, user_pwd, user_name, gender, phone, email)
VALUES
(1, 'user01', 'pass01', '홍길동', '남', '010-1234-5678', 'hong123@gmail.com'),
(2, 'user02', 'pass02', '유관순', '여', '010-777-7777', 'yu77@gmail.com');

SELECT * FROM user_primarykey;
# primary key 제약조건 에러 발생(null값 적용)
INSERT INTO user_primarykey
(user_no, user_id, user_pwd, user_name, gender, phone, email)
VALUES
(null, 'user03', 'pass03', '이순신', '남', '010-777-7777', 'lee222@gmail.com');
# primary key 제약조건 에러 발생(중복값 적용)
INSERT INTO user_primarykey
(user_no, user_id, user_pwd, user_name, gender, phone, email)
VALUES
(2, 'user03', 'pass03', '이순신', '남', '010-777-7777', 'lee222@gmail.com');

 

4. FOREIGN KEY

  • 참조(REFERENCES)된 다른 테이블에서 제공하는 값만 사용할 수 있음
  • 참조 무결성을 위배하지 않기 위해 사용
  • FOREIGN KEY 제약조건에 의해서 테이블 간의 관계(RELATIONSHIP)가 형성 됨
  • 제공되는 값 외에는 NULL을 사용할 수 있음
# 제약조건 확인용 테이블 생성 및 테스트 데이터 INSERT 후 조회하기 1 (부모 테이블)
DROP TABLE IF EXISTS user_grade;
CREATE TABLE IF NOT EXISTS user_grade (
    grade_code INT NOT NULL UNIQUE,
    grade_name VARCHAR(255) NOT NULL
) ENGINE=INNODB;

INSERT INTO user_grade
VALUES 
(10, '일반회원'),
(20, '우수회원'),
(30, '특별회원');

SELECT * FROM user_grade;

 

# 제약조건 확인용 테이블 생성 및 테스트 데이터 INSERT 후 조회하기2 (자식 테이블 - DELETE 삭제룰 없을 시 )
DROP TABLE IF EXISTS user_foreignkey1;
CREATE TABLE IF NOT EXISTS user_foreignkey1 (
    user_no INT PRIMARY KEY,
    user_id VARCHAR(255) NOT NULL,
    user_pwd VARCHAR(255) NOT NULL,
    user_name VARCHAR(255) NOT NULL,
    gender VARCHAR(3),
    phone VARCHAR(255) NOT NULL,
    email VARCHAR(255),
    grade_code INT ,
    FOREIGN KEY (grade_code) 
		REFERENCES user_grade (grade_code)
) ENGINE=INNODB;

INSERT INTO user_foreignkey1
(user_no, user_id, user_pwd, user_name, gender, phone, email, grade_code)
VALUES
(1, 'user01', 'pass01', '홍길동', '남', '010-1234-5678', 'hong123@gmail.com', 10),
(2, 'user02', 'pass02', '유관순', '여', '010-777-7777', 'yu77@gmail.com', 20);

SELECT * FROM user_foreignkey1;
# foreign key 제약조건 에러 발생(참조 컬럼에 없는 값 적용)
INSERT INTO user_foreignkey1
(user_no, user_id, user_pwd, user_name, gender, phone, email, grade_code)
VALUES
(3, 'user03', 'pass03', '이순신', '남', '010-777-7777', 'lee222@gmail.com', 50);
# 제약조건 확인용 테이블 생성 및 테스트 데이터 INSERT 후 조회하기3 (자식 테이블 - DELETE 삭제룰 있을 시 )
DROP TABLE IF EXISTS user_foreignkey2;
CREATE TABLE IF NOT EXISTS user_foreignkey2 (
    user_no INT PRIMARY KEY,
    user_id VARCHAR(255) NOT NULL,
    user_pwd VARCHAR(255) NOT NULL,
    user_name VARCHAR(255) NOT NULL,
    gender VARCHAR(3),
    phone VARCHAR(255) NOT NULL,
    email VARCHAR(255),
    grade_code INT ,
    FOREIGN KEY (grade_code) 
		REFERENCES user_grade (grade_code)
        ON UPDATE SET NULL
        ON DELETE SET NULL
) ENGINE=INNODB;

INSERT INTO user_foreignkey2
(user_no, user_id, user_pwd, user_name, gender, phone, email, grade_code)
VALUES
(1, 'user01', 'pass01', '홍길동', '남', '010-1234-5678', 'hong123@gmail.com', 10),
(2, 'user02', 'pass02', '유관순', '여', '010-777-7777', 'yu77@gmail.com', 20);

SELECT * FROM user_foreignkey2;
# 1) 부모 테이블의 grade_code 수정
# (user_foreignkey1 테이블 DROP하고 진행할 것(user_foreignkey에는 foreign key 제약조건에 수정 및 삭제룰 적용이 되지 않았기 때문)
DROP TABLE IF EXISTS user_foreignkey1;

UPDATE user_grade
SET grade_code = null
WHERE grade_code = 10;

-- 자식 테이블의 grade_code가 10이 었던 회원의 grade_code값이 NULL이 된 것을 확인
SELECT * FROM user_foreignkey2;

# 2) 부모 테이블의 행 삭제
# (user_foreignkey1 테이블 DROP하고 진행할 것(user_foreignkey에는 foreign key 제약조건에 수정 및 삭제룰 적용이 되지 않았기 때문)
DELETE FROM user_grade
WHERE grade_code = 20;

-- 자식 테이블의 grade_code가 20이 었던 회원의 grade_code값이 NULL이 된 것을 확인
SELECT * FROM user_foreignkey2;

 

5. CHECK

  • check 제약 조건 위반시 허용하지 않는 제약조건
# 제약조건 확인용 테이블 생성 및 테스트 데이터 INSERT 후 조회하기
DROP TABLE IF EXISTS user_check;
CREATE TABLE IF NOT EXISTS user_check (
    user_no INT AUTO_INCREMENT PRIMARY KEY,
    user_name VARCHAR(255) NOT NULL,
    gender VARCHAR(3) CHECK (gender IN ('남','여')),
    age INT CHECK (age >= 19)
) ENGINE=INNODB;

INSERT INTO user_check
VALUES 
    (null, '홍길동', '남', 25),
    (null, '이순신', '남', 33);

SELECT * FROM user_check;

 

# gender 컬럼의 CHECK 제약 조건 에러 발생(성별이 두 글자)
INSERT INTO user_check
VALUES (null, '안중근', '남성', 27);

# age 컬럼의 CHECK 제약 조건 에러 발생(나이가 19세 미만)
INSERT INTO user_check
VALUES (null, '유관순', '여', 17);

 

6. DEFAULT

  • 컬럼에 null 대신 기본 값 적용
  • 컬럼 타입이 DATE일 시 current_date만 가능
  • 컬럼 타입이 DATETIME일 시 current_time과 current_timestamp, now() 모두 사용 가능
# 제약조건 확인용 테이블 생성 및 테스트 데이터 INSERT 후 조회하기
DROP TABLE IF EXISTS tbl_country;
CREATE TABLE IF NOT EXISTS tbl_country (
    country_code INT AUTO_INCREMENT PRIMARY KEY,
    country_name VARCHAR(255) DEFAULT '한국',
    population VARCHAR(255) DEFAULT '0명',
    add_day DATE DEFAULT (current_date),
    add_time DATETIME DEFAULT (current_time)
) ENGINE=INNODB;

SELECT * FROM tbl_country;
# default 설정이 되어 있는 컬럼들에 default 값이 들어가도록 INSERT 진행 후 조회
INSERT INTO tbl_country
VALUES (null, default, default, default, default);

SELECT * FROM tbl_country;

'SKN > 02. DB' 카테고리의 다른 글

10. VIEW  (0) 2025.02.19
09. INDEX  (0) 2025.02.19
07. DDL(Data Definition Language)  (0) 2025.02.19
06. 정규화  (0) 2025.02.19
05. 이상(Anomaly)  (0) 2025.02.19

'SKN/02. DB'의 다른글

  • 현재글08. CONSTRAINTS

관련글

티스토리툴바