우재남님의 "혼자 공부하는 SQL"을 기반으로 학습한 게시물입니다.
📕 목차

1. 제약 조건(Constraint)
2. 가상 테이블: 뷰(View)

1. 제약 조건(Constraint)

 

📌 무결성 제약 조건(Data Integrity Constraint Rule)
  • 데이터의 무결성을 지키기 위해 제한하는 조건들
  • 대표적인 제한 조건
    • Primary key
    • Foreign key
    • Unique
    • Check
    • Default
    • Null

 

무결성 제약 조건 역할
NOT NULL NULL을 허용하지 않는다.
UNIQUE 중복값을 허용하지 않는다.
PRIMARY KEY NULL과 중복값을 허용하지 않는다.
FOREIGN KEY 참조되는 테이블의 column값이 존재하면 허용한다.
CHECK 저장 가능한 데이터 값의 범위나 조건을 지정하여 설정한 값만을 허용한다.

 

📌 기본 키 제약조건
  • 데이터를 구분할 수 있는 식별자(identifier)
  • 기본 키로 생성한 것은 자동으로 클러스터형 인덱스가 생성된다.
  • 테이블은 기본 키를 1개만 가질 수 있다.
  • 해당 기본 키를 참조하는 테이블이 있다면, 외래 키 테이블을 먼저 삭제해야 기본 키 테이블을 삭제할 수 있다.

 

🟡 Primary key 지정

CREATE TABLE foo (
    id BIGINT NOT NULL PRIMARY KEY
);

DESCRIBE member;

PRIMARY KEY로 지정된 Column에는 "PRI"라는 정보가 뜬다.

CREATE TABLE foo (
    id BIGINT NOT NULL,
    ...,
    PRIMARY KEY(id)
);

가장 마지막에 지정해주어도 결과는 같다.

 

✒️ PRI 이름 변경하기

기본 키는 별도의 이름이 없기에, DESCRIBE 명령으로 확인하면 그냥 PRI로 나온다.
만약 이를 바꿔주고 싶다면, TABLE을 생성할 때 CONSTRAINT PRIMARY KEY (PK 이름) (PK 필드)로 Query를 작성하면 된다.

 

🟡 ALTER TABLE

CREATE TABLE foo (
    id BIGINT NOT NULL,
    ...
);

ALTER TABLE foo
    ADD CONSTRAINT
    PRIMARY KEY (id);

이미 만들어진 Table을 수정하는 방법으로 PK를 지정해줄 수도 있다.

 

📌 외래 키 제약조건
  • 두 테이블 사이의 관계를 연결하여, 데이터의 무결성을 보장해준다.
    • 참조 테이블의 FK는 반드시 기준 테이블의 PK로 존재하기 때문이다.
  • 외래 키가 설정된 Column은 반드시 다른 테이블의 기본 키와 연결된다.
  • 기본 키가 있는 테이블을 기준 테이블, 외래 키가 있는 테이블을 참조 테이블이라 한다.

 

🟡 FOREIGN KEY 지정

CREATE TABLE member (
    id BIGINT NOT NULL PRIMARY KEY,
    ...
);

CREATE TABLE buy (
    ...,
    mem_id BIGINT NOT NULL,
    ...
    FOREIGN KEY(mem_id) REFERENCES member(id)
);

 

🟡 ALTER TABLE

CREATE TABLE buy (
    ...,
    mem_id BIGINT NOT NULL,
    ...
);

ALTER TABLE buy
    ADD CONSTRAINT
    FOREIGN KEY(mem_id)
    REFERENCES member(id);

 

📌 CASCADE
UPDATE member SET mem_id = "PINK" WHERE mem_id = "BLK";
DELETE FROM member WHERE mem_id="BLK";

둘 다 실패한다.

  • 해당 Record의 PK를 참조하는 FK가 하나라도 있다면, 참조 데이터 무결성을 위해 FK를 함부로 수정할 수 없다.
  • CASCADE 속성은 기준 Table column이 변경될 때, 자동으로 참조 Table의 column 이름을 변경한다.

 

ALTER TABLE buy
    ADD CONSTRAINT
    FOREIGN KEY(mem_id) REFERENCES member(mem_id)
    ON UPDATE CASCADE
    ON DELETE CASCADE;

위의 명령어를 그대로 실행해보면 이제는 정상적으로 수행됨을 알 수 있다.

 

 

📌 기타 제약조건

1️⃣ Unique

  • 중복되지 않는 유일한 값을 입력해야 한다.
  • NULL 값을 허용한다. (Primary key 제약 조건과의 차이)
  • Unique는 여러 column에 설정될 수 있다.

 

2️⃣ Check

-- 반드시 100 이상의 키만 입력
CREATE TABLE member (
    ...,
    height TINYINT UNSIGNED NULL CHECK (height >= 100)
);

-- 정해진 휴대폰 local 정보만 입력 가능
ALTER TABLE member 
    ADD CONSTRAINT
    CHECK (phone1 IN ('02', '031', '032', '053', '071'));
  • 입력되는 데이터를 점검한다.
  • CHECK(조건)으로 추가할 수 있다.

 

3️⃣ Default

CREATE TABLE member (
    ...,
    height TINYINT UNSIGNED NULL DEFAULT 160
);
ALTER TABLE member
    ALTER COLUMN phone1 SET DEFAULT '02';
  • 값을 입력하지 않았을 때 자동으로 입력될 값을 미리 지정해 놓는 방법이다.

 

위처럼 DEFAULT가 설정된 필드는 INSERT SQL에서 아래처럼 사용할 수 있다.

INSERT INTO member VALUES('WWW', '떠떠떠블유', default, default);

 

4️⃣ Null

  • NULL을 허용하지 않는다면 NOT NULL을 사용하면 된다.
  • 공백과 0과는 다른 의미다.
  • NULL 값을 적게 허용할 수록 데이터의 오류 가능성이 적어진다.

 

2. 가상 테이블: 뷰(View)

 

📌 Concept
  • 데이터베이스 개체 중 하나
  • SELECT 문으로 만들어져 있으며, 사용자 입장에서는 Table과 구분할 수 없다.
  • Table처럼 데이터를 가지고 있지 않은 가상의 Table이다.
  • 여러 종류의 View가 존재한다.
    • 단순 뷰(Simple View) : 하나의 Table과 연관된 View
    • 복합 뷰(Complex View) : 2개 이상의 Table이 Join하여 만든 View
    • 인라인 뷰(Inline View) : SELECT 절의 결과를 FROM 절에서 하나의 Table처럼 사용하는 View
    • 구체화된 뷰(Materialized View) - 구조적인 이해가 필요해서 패스. 데이터가 수십 억인 경우 필요하다고 한다.
  • 기본적으로 읽기 전용이지만, 몇 가지 조건 하에 원본 Table의 데이터를 수정할 수도 있다.

 

📌 사용 방법
CREATE VIEW [뷰 이름] AS [SELECT문];
  • 관례적으로 뷰 이름의 접두사로 'v_'를 붙인다.
  • 생성된 View는 Table로 취급하고 접근해도 무방하다.
    • 필요한 Column만 보거나 조건식을 넣을 수도 있다.

 

CREATE VIEW v_member AS SELECT mem_id, mem_name, addr FROM member;

 

🟡 사용 이유

(좌) https://aristeksystems.com/blog/database-views-what-you-need-to-know/ / (우) dotnettutorials.net

  1. 보안(security)
    • 테이블의 모든 정보를 공개하지 않고, 필요한 부분만 확인 가능하도록 권한을 제한할 수 있다.
    • 즉, Table 자체를 참조할 권한을 주지 않고 View를 제공하여 접근 권한에 차별을 둘 수 있다.
  2. Query 단순화
    • 자주 사용하는 Query를 View로 만들어둔다면, Query를 보다 단순하게 작성할 수 있다.

 

📌 실제 작동

🟡 View의 실제 생성, 수정, 삭제

  • view에 사용될 column 이름을 Table과 다르게 별칭(alias)으로 정할 수도 있다.
  • 별칭은 column 이름 뒤에 작은 따옴표(혹은 큰 따옴표)로 묶어주고, 형식상 AS를 붙여준다.
  • View를 조회할 때 열 이름에 공백이 있으면 백틱(`)으로 묶어주어야 한다.

 

CREATE VIEW v_viewtest1
AS
    SELECT b.mem_id 'Member ID', m.mem_name AS 'Member Name', b.prod_name 'Product Name', CONCAT(m.phone1, m.phone2) AS 'Office Phone'
    FROM buy b
    INNER JOIN member m
    ON b.mem_id = m.mem_id;
    
SELECT DISTINCT `Member ID`, `Member Name` FROM v_viewtest1;

이 예시는 복합 뷰로 보면 된다.

뷰를 수정할 때는 마찬가지로 ALTER VIEW를 사용한다.

삭제할 때는 DROM VIEW를 사용한다.

 

✒️ 기존의 View를 덮어쓰고 싶다면 CREATE OR REPLACE VIEW를 사용하면 된다.

 

🟡 View의 정보 확인

  • DESCRIBE [뷰_이름]
    • 줄여서 DESC라고 써도 가능하다.
  • SHOW CREATE VIEW [뷰_이름]
    • View의 소스 코드를 확인한다.

 

🟡 View를 통한 데이터 입력

  • View를 통해서 원본 Table의 값을 입력하고 싶은데, NOT NULL 필드가 보이지 않는 영역에 존재한다면
    • View를 재정의하거나,
    • 해당 column 속성을 NULL로 바꾸거나,
    • Default 값을 설정해야 한다.
CREATE VIEW v_height167 AS SELECT * 
    FROM member WHERE height >= 167 WITH CHECK OPTION;
  • WITH CHECK OPTION
    • View의 범위를 제한하여, 설정된 값의 범위의 값을 입력되지 않도록 한다.
    • ex. 키가 167 이상인 데이터만 모아놓은 view에서 157의 데이터가 들어가면 안 된다.

 

🟡 View가 참조하는 테이블 삭제

  • Table을 삭제하면, 해당 Table에서 파생된 View 또한 모두 삭제 된다.
  • CHECK TABLE [뷰 이름]으로 Table과 View의 상태를 확인할 수 있다.

 

저작자표시 비영리

티스토리툴바