[PostgreSQL] 4장 - 인덱스와 제약조건

✅ 1. 인덱스 

인덱스는 테이블에서 특정 컬럼의 검색 속도를 빠르게 하기 위한 자료구조로, 테이블에 직접적인 영향을 주지는 않지만, SELECT, JOIN, WHERE, ORDER BY 등에서 성능을 향상시킵니다. 

-- users 테이블 생성
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    order_date DATE NOT NULL
);

-- 외래 키 제약 조건 추가
ALTER TABLE orders
ADD CONSTRAINT fk_user
FOREIGN KEY(user_id) REFERENCES users(id)
ON DELETE CASCADE
ON UPDATE CASCADE;

-- 단일 컬럼 인덱스 생성
CREATE INDEX idex_users_name 
ON users(name);

-- 다중 컬럼 인덱스 생성
CREATE INDEX idx_orders_userid_date
ON orders(user_id, order_date);


-- 고유 인덱스 (Unique 제약 조건과 비슷)
CREATE UNIQUE INDEX idx_users_email
ON users(email);

• 인덱스가 많으면 오히려 쓰기 작업 성능이 저하됨
• 자주 검색되는 컬럼, WHERE 절 조건, JOIN에 자주 쓰이는 컬럼 위주로 생성

---

✅ 2. 제약조건(Constraints)

테이블 데이터의 무결성(Integrity)을 유지하기 위한 규칙으로 PRIMARY KEY는 각 행(row)을 고유하게 식별합니다.

CREATE TABLE searchList (
    id SERIAL PRIMARY KEY, -- 기본 키, 자동으로 인덱스 생성 
    name VARCHAR(50) NOT NULL
)

---

🚀 UNIQUE

 

UNIQUE는 컬럼 값이 중복되지 않도록 제한합니다. 여러 컬럼에 걸쳐 복합 UNIQUE를 사용할 수 있습니다.

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY, 
    email VARCHAR(100) UNIQUE 
)

-- 다중 컬럼 UNIQUE 제약 조건
CREATE TABLE user_profiles (
    user_id INT,
    role_id INT,
    UNIQUE(user_id, role_id)
)

INSERT INTO employees (email) VALUES ('sample@example.com')

SELECT * FROM employees;

---

🚀 NOT NULL

 

NOT NULL은 컬럼 값이 NULL이 될 수 없도록 제한합니다.

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price NUMERIC NOT NULL
);

INSERT INTO products (name, price) VALUES ('Laptop', 999.99);   
INSERT INTO products (name, price) VALUES ('Smartphone', 499.49);


SELECT * FROM products;

---

🚀 FOREIGN KEY 

 

FOREIGN KEY는 다른 테이블의 PRIMARY KEY 또는 UNIQUE 컬럼과 관계를 맺으며 데이터의 참조 무결성을 유지합니다.

CREATE TABLE foreigns (
    order_id SERIAL PRIMARY KEY,
    user_id INT,
    order_date DATE NOT NULL,
    CONSTRAINT fk_user
        FOREIGN KEY(user_id) REFERENCES users(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

INSERT INTO foreigns (user_id, order_date) VALUES (1, '2023-10-01');

SELECT * FROM foreigns;

• ON DELETE CASCADE -> 참조 대상 삭제 시 관련 행도 삭제
• ON UPDATE CASCADE -> 참조 대상 변경 시 자동 업데이트

---

✅ 3. 인덱스와 제약조건 관계

• PRIMARY KEY나 UNIQUE를 생성하면 PostgreSQL이 자동으로 인덱스 생성
• 직접 CREATE INDEX로 인덱스를 만들 수 있지만, 제약조건으로 인해 중복되는 인덱스가 생기지 않도록 주의

---

 

GitHub - Koras02/postgresql-bloging: https://thinky.tistory.com/category/Database/PostGresQL