2023 - 06 - 23 SQL 제약조건 - 쿼리문 기본 키 (Primary Key), 외래 키 (Foreign Key), Null, Unique 고유제약조건

 

SQL의 제약조건은 각 컬럼들의 제한사항을 관리하고, 조건을 위반하는 데이터를 방지하여 데이터베이스의 완전성을 보장하는 규칙. 

 

1. Unique : (고유제약조건) 테이블에 소속된 특정 컬럼이 중복된 키를 가질 수 없는 조건

 

2. Null : 아무런 값을 입력받지 않을 수 있도록 설정하는 조건

 

3. Primary Key : (기본키 제약조건) 특정 컬럼을 테이블 내에서 고유성을 보장하는 컬럼을 설정하는 조건

 

4. Foreign Key : (외래키 제약조건) 테이블 간의 관계를 설정하는 조건 (데이터베이스 설계시 가장 많은 고민하게 됨)

 

 

 

 

 

연관관계 고민.

고객이 음식 주문시, 고객테이블에 주문정보를 넣을 경우, 회원이 중복된다.

그렇다면 음식테이블에 주문정보를 넣을경우, 음식이 중복된다.

따라서, 주문을 위한 테이블이 따로 필요하다.

 

유저의 고유 ID값과, 음식의 고유 ID값으로 새로운 주문 테이블을 만들 수 있다.

회원 1명은 주문을 N개 할수 있으므로, 회원과 주문은 = 1 : N 관계

음식 1개는 주문 N개에 포함될 수 있으므로 음식과 주문은 1 : N 관계

회원과 음식은 N : N 관계

 

이를 SQL로 구현하면 다음과 같다.

 

 

CREATE TABLE User
(
    userId int(11)      NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name   varchar(255) NOT NULL UNIQUE
);

CREATE TABLE Food
(
    foodId int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name   varchar(255),
    price  int(11)
);

CREATE TABLE Order
(
    orderId   int(11)  NOT NULL AUTO_INCREMENT PRIMARY KEY,
    userId    int(11)  NOT NULL,
    foodId    int(11)  NOT NULL,
    createdAt datetime NOT NULL DEFAULT NOW(),
    FOREIGN KEY (foodId) REFERENCES Food (foodId)
        ON DELETE NO ACTION
        ON UPDATE CASCADE,
    FOREIGN KEY (userId) REFERENCES User (userId)
        ON DELETE NO ACTION
        ON UPDATE CASCADE
);

 

1. 기본 키 제약조건 (Primary Key)

 

테이블에 있는 데이터를 고유하게 구분할 수 있는 정보를 나타내기 위해서 사용함.

CREATE TABLE User
(
    userId int(11)      NOT NULL AUTO_INCREMENT PRIMARY KEY, 
    name   varchar(255)
);

유저아이디가 int(정수)타입으로 있고, 
Null값은 허용하지 않는다.
데이터 삽입할때 아무런 데이터를 컬럼에 삽입하지 않더라도, 이 컬럼은 자동적으로 고유한 값을 유지하게 한다.
이 유저아이디는 기본키 값이다.
이름은 문자열이고 255자까지 있다.

AUTO_INCREMENT는 데이터를 삽입할 때 아무런 데이터를 입력하지 않아도 고유값을 유지할 수 있도록 도와주는 속성.

 

 

2. Null 제약조건

 

Null 속성은 특정 컬럼에서 NULL값을 허용하거나, 허용하지 않도록 설정하는 조건.

테이블에서 컬럼을 생성할 때 추가조건을 작성하지 않으면 NULL값을 허용하므로 NOT NULL 제약조건을 추가한다.

CREATE TABLE User
(
    userId int(11)      NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name   varchar(255) NOT NULL
);

User 테이블의 name 컬럼은 Null값을 허용하지 않는다

 

3. 고유 제약조건 (Unique 제약조건)

특정 컬럼에서 중복된 값이 허용되지 않도록 설정하는 조건

예를들어 어플리케이션 사용자가 로그인 시도 시 이메일의 중복을 허용할 경우, 이름, 나이, 닉네임과 같은 추가 정보를 입력받아 사용자를 특정해낼수 있게 함.

 

CREATE TABLE User
(
    userId int(11)      NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name   varchar(255) NOT NULL UNIQUE
);

User 테이블의 name 컬럼은 중복된 값을 허용하지 않는다

 

4. 외래키 제약조건 (Foreign Key)

어떤 테이블과 다른 테이블 간의 관계를 맺을 때 사용하는 제약 조건. 외개 키는 테이블 간의 연관 관계를 표현할 때 사용한다.

1 : 1 = 1명의 사용자(user)는 1개의 사용자 정보 (user Info)를 가질 수 있다.

1 : N = 1명의 사용자(user)는 여러개의 주문 (order)를 할 수 있다.

N : N = 여러명의 학생 (students)는 여러 개의 학원 (schools)를 등록할 수 있다.

CREATE 테이블명
FOREIGN KEY (컬럼명) REFERENCES 참조 테이블명 (참조 컬럼명)
ON DELETE (연계 참조 제약조건)
ON UPDATE (연계 참조 제약조건)

CREATE TABLE Garden
(
    gId int(11)      NOT NULL AUTO_INCREMENT PRIMARY KEY,
    address  varchar(255) NOT NULL
);

CREATE TABLE GardenPlants
(
    gardenPlantsId int(11)      NOT NULL AUTO_INCREMENT PRIMARY KEY,
    GardenId       int(11)      NOT NULL,
    name           varchar(255) NOT NULL,
    FOREIGN KEY (GardenId) REFERENCES Garden (gardenId)
);

정원(Garden) 테이블 (컬럼 : 이름, 주소),
정원식물(GardenPlants) 테이블 (컬럼 : 식물 이름)

정원 식물은 특정 정원에 소속되어 있다.
하나의 정원은 여러개의 정원 식물을 가질 수 있다.

정원식물의 GardenId는 외래 키를 설정하는 부분, 
이 외래키를 통해 정원식물이 특정한 정원에 소속되어 있음을 알 수있다.

    FOREIGN KEY (GardenId) REFERENCES Garden (gId)
    GardenPlants 테이블의 GardenId의 컬럼을 참조할건데, 이는 Garden 테이블에 있는 gId 컬럼을 참조한다.

 

외래키 제약조건 심화

외래키의 경우 다른 테이블과 관계를 맺고있는 참조 데이터가 삭제 (DELETE), 수정 (UPDATE)될때 어떤 행위를 해야하는지 설정할 수 있다.

 

CASCADE 조건

참조하고 있는 개체가 변경 또는 삭제될 경우 함께 변경 및 삭제된다.

FOREIGN KEY (UserId) REFERENCES Users(userId)
    ON DELETE CASCADE
    ON UPDATE CASCADE;
    
    
   내가 현재 참조하고있는 Users 테이블의 userId 값이 변경 또는 삭제될 때
   내가 갖고있는 UserId의 컬럼값도 같이 변경 또는 삭제된다.

 

NO ACTION 조건

참조하고 있는 개체가 변경/삭제 될 경우 아무런 행위를 하지 않고 에러가 발생하게된다.

FOREIGN KEY (UserId) REFERENCES Users(userId)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION;
    
   내가 현재 참조하고있는 Users 테이블의 userId 값이 변경 또는 삭제될 때
   내가 갖고있는 UserId의 컬럼값에는 아무런 변경이 없다.

 

SET NULL

참조하고 있는 개체가 변경/삭제 될 경우 현재 데이터를 NULL로 변경한다.

FOREIGN KEY (UserId) REFERENCES Users(userId)
    ON DELETE SET NULL
    ON UPDATE SET NULL;
    
   내가 현재 참조하고있는 Users 테이블의 userId 값이 변경 또는 삭제될 때
   내가 갖고있는 UserId의 컬럼값은 Null 값으로 변한다

 

 

SET DEFAULT

참조하고 있는 개체가 변경/삭제 될 경우 현재 데이터를 기본 값으로 변경한다.

FOREIGN KEY (UserId) REFERENCES Users(userId)
    ON DELETE SET DEFAULT
    ON UPDATE SET DEFAULT;
    
   내가 현재 참조하고있는 Users 테이블의 userId 값이 변경 또는 삭제될 때
   내가 갖고있는 UserId의 컬럼값을 기본값으로 변경한다

 

 

SELEC JOIN 연산자

두 테이블간의 공통된 데이터를 기준으로 테이블을 연결하여 하나의 테이블 처럼 조회 할 수 있는 연산자

 

 

위와 같은 ERD는 사용자(Users) 테이블과 게시글(Posts) 테이블에서 1명의 사용자가 여러개의 게시글을 작성 할 수 있으므로 1:N 관계를 갖게 된다.

여기서 게시글 목록을 조회할 때 작성한 사용자의 이메일을 표시하고싶더라도 게시글 테이블에는 email 컬럼이 존재하지 않기 때문에, 이메일을 표시해줄 수 없게된다.

하지만 게시글 테이블에서는 UserId 컬럼을 이용해 어떤 사용자가 게시글을 작성했는지 확인할 수 있으므로 JOIN을 이용해 외래키가 설정된 UserId를 기준으로 해당 사용자의 이메일을 함께 출력하도록 설정할 수 있다.

 

CREATE TABLE Users
(
    userId   int(11)      NOT NULL AUTO_INCREMENT PRIMARY KEY,
    email    varchar(255) NOT NULL,
    password varchar(255) NOT NULL
);

CREATE TABLE Posts
(
    postId  int(11)      NOT NULL AUTO_INCREMENT PRIMARY KEY,
    UserId  int(11)      NOT NULL,
    title   varchar(255) NOT NULL,
    content varchar(255) NOT NULL,
    FOREIGN KEY (UserId) REFERENCES Users (userId)
);
Posts테이블의 UserId컬럼이 Users테이블의 userId컬럼을 참조한다.



INSERT INTO Users (userId, email, password)
VALUES (1, 'AAAA', '1234'),
       (2, 'BBBB', '1234');

INSERT INTO Posts (UserId, title, content)
VALUES (1, 'AAAA Title1', 'content'),
       (1, 'AAAA Title2', 'content'),
       (2, 'BBBB Title1', 'content'),
       (2, 'BBBB Title2', 'content');
       
Users테이블에 (userId, email, password) 데이터를 삽입하고
Posts테이블에도 (UserId, title, content) 데이터를 삽입한다

조회하기

SELECT p.postId, p.title, p.content, u.email
FROM Posts as p
JOIN Users as u // 기준
	ON p.UserId = u.userId;
    
    
    Posts테이블은 p로 설정하고,
    Users테이블은 u로 설정한다.
    
    p의 UserId컬럼과 u의 userId컬럼이 공통된 부분이다.
    
    
    여기서 FROM ... as 와 Join ... as는 알리아스 (별칭, alias)으로써 여러개의 테이블을 참조할 때나
    특정 테이블을 간단하게 나타내기 위해 사용. 쉽게알기위해 변수선언하는거랑 똑같다고보면 됨.
    테이블의 이름이 길게 정의되지 않도록 해줌.
    
    특히 Join ... as는  어떤 컬럼을 기준으로 테이블을 병합하려하는지 명시할 때 사용한다.
    Users 테이블에 있는 userId가 Posts 테이블에 있는 UserId로 외래키가 설정되었으므로
    해당 컬럼을 기준으로 Posts의 작성자를 확인할 수 있다.

 

alias를 사용한 예시

SELECT
    pm.productionId,
    pm.debutDate,
    p.productionName,
    p.address,
    pm.createdAt,
    pm.updatedAt,
    pm.deletedAt
FROM
    ProductionMember as pm
    JOIN Production as p ON pm.productionId = p.productionId;
    
    
alias를 사용하지 않은 예시
    
    SELECT
    ProductionMember.productionId,
    ProductionMember.debutDate,
    Production.productionName,
    Production.address,
    ProductionMember.createdAt,
    ProductionMember.updatedAt,
    ProductionMember.deletedAt
FROM
    ProductionMember
    JOIN Production ON ProductionMember.productionId = Production.productionId;

 

제약조건 연습문제

 

1 : N 테이블 요구사항

 

출판사(`Publisher`) 테이블
1. `publisherId` 컬럼을 가집니다. 기본 키 조건을 설정합니다.
2. 출판사 명(`publisherName`) 컬럼을 가집니다. 문자열 타입을 가집니다.
3. 출판사 주소(`publisherAddress`) 컬럼을 가집니다. 문자열 타입을 가집니다.

책(`Books`) 테이블
1. `bookId` 컬럼을 가집니다. 기본 키 조건을 설정합니다.
2. 책 명(`bookName`) 컬럼을 가집니다. 문자열 타입을 가집니다.

이외 요구사항
- 출판사는 여러개의 책을 가질 수 있습니다.
- 출판사 명, 출판사 주소, 책 명은 NULL 값을 가질 수 없습니다.

CREATE TABLE Publisher
(
    publisherId int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    publisherName varchar(255) NOT NULL,
    publisherAddress varchar(255) NOT NULL
);


CREATE TABLE Books
(
    bookId int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    bookName varchar(255) NOT NULL
);

원래는 위와 같은 식인데, 출판사는 여러개의 책을 가질 수 있다고 했으므로 1:N의 관계
아래와 같이 바꾸면 된다. 
Books테이블에 publisherId를 넣었고, 외래키로 어떤 테이블에 어떤 컬럼을 가져와서 넣었는지 쓴다.
↓↓↓


CREATE TABLE Publisher
(
    publisherId int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    publisherName varchar(255) NOT NULL,
    publisherAddress varchar(255) NOT NULL
);


CREATE TABLE Books
(
    bookId int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    publisherId int(11) NOT NULL   
    bookName varchar(255) NOT NULL

    FOREIGN KEY (publisherId) REFERENCES Publisher (publisherId)
);

 

1:1 테이블 요구사항

 

별(`Star`) 테이블

1. `starId` 컬럼을 가집니다. 기본 키 조건을 설정합니다.

별 세부정보(`StarInfo`) 테이블

1. `starInfoId` 컬럼을 가집니다. 기본 키 조건을 설정합니다.
2. 이름(`name`) 컬럼을 가집니다. 문자열 타입을 가집니다.
3. 밝기(`magnitude`) 컬럼을 가집니다. 문자열 타입을 가집니다.
4. 거리(`distance`) 컬럼을 가집니다. 문자열 타입을 가집니다.

이외 요구사항

- 별은 1개의 별 세부정보를 가질 수 있습니다.
- 이름, 밝기, 거리 컬럼은 NULL 값을 가질 수 없습니다.

CREATE TABLE Star
(
    starId int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY
)

CREATE TABLE starInfo
(
    starInfoId int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name varchar(255) NOT NULL,
    magnitude varchar(255) NOT NULL,
    distance varchar(255) NOT NULL
)

원래는 위와 같은 식인데, 1대 1 요구사항에 맞게 변한다.
Starinfo테이블에서 Star테이블에 있는 starId컬럼을 갖고와 고유값설정 (UNIQUE)하고,
외래키 설정하여 starId는 Star테이블의 starId컬럼을 참조한다.


CREATE TABLE Star
(
    starId int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY
)

CREATE TABLE starInfo
(
    starInfoId int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    starId int(11) NOT NULL UNIQUE
    name varchar(255) NOT NULL,
    magnitude varchar(255) NOT NULL,
    distance varchar(255) NOT NULL
    FOREIGN KEY (starId) REFERENCES Star (starId)
)

 

N:M 테이블 요구사항

아이돌(`Idol`) 테이블

1. 이름(`name`) 컬럼을 가집니다.
2. 나이(`age`) 컬럼을 가집니다.
3. 성별(`gender`) 컬럼을 가집니다.

소속사(`production`) 테이블

1. 회사명(`productionName`) 컬럼을 가집니다.
2. 주소(`address`) 컬럼을 가집니다.

소속사 멤버(`ProductionMember`) 테이블

1. 아이돌과 소속사를 연결해줍니다.
2. 아이돌의 소속사 데뷔 날짜(`debutDate`) 컬럼을 가집니다.

**이외 요구사항**

- 아이돌은 소속사를 가지지 않거나, 여러개를 가질 수 있습니다.
- 아이돌의 이름은 중복될 수 없습니다.
- 소속사는 아이돌을 가지지 않거나, 여러명을 소속시킬 수 있습니다.
- 소속사의 회사명은 중복될 수 없습니다.
- 소속사의 주소는 중복될 수 없습니다.

CREATE TABLE Idol
(
    idolId int(11)        NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name   varchar(255)   NOT NULL UNIQUE,
    age    int(11)        NULL,
    gender enum ('M','F') NOT NULL
);

CREATE TABLE Production
(
    productionId   int(11)      NOT NULL AUTO_INCREMENT PRIMARY KEY,
    productionName varchar(255) NOT NULL UNIQUE,
    address        varchar(255) NOT NULL UNIQUE
);

CREATE TABLE ProductionMember
(
    productionMemberId int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    productionId       int(11) NOT NULL,
    idolId             int(11) NOT NULL,
    debutDate          date    NOT NULL,
    FOREIGN KEY (productionId) REFERENCES Production (productionId),
    FOREIGN KEY (idolId) REFERENCES Idol (idolId)
);

 

 

SQL의 종류

https://velog.io/@wngud4950/SQL%EC%9D%98-%EC%A2%85%EB%A5%98