## Performance Impact of Constraints

Constraints add overhead, but the cost is usually worth it for data integrity.

**PRIMARY KEY:**

- Creates an index automatically (fast lookups)

- Minimal overhead on INSERT (index update)

- No overhead on SELECT

- Cost: ~5-10% slower INSERTs

**FOREIGN KEY:**

- Requires lookup in parent table on INSERT/UPDATE

- Requires checking child tables on DELETE (unless CASCADE)

- Should always have an index on the foreign key column

- Cost: ~10-20% slower INSERTs, ~50-100% slower DELETEs (if checking children)

**UNIQUE:**

- Creates an index automatically (fast lookups)

- Requires index check on INSERT/UPDATE

- Cost: ~5-10% slower INSERTs

**CHECK:**

- Evaluated on every INSERT/UPDATE

- Simple checks (price > 0) are very fast

- Complex checks (subqueries, functions) can be slow

- Cost: ~1-5% slower INSERTs for simple checks, ~10-50% for complex checks

**NOT NULL:**

- Minimal overhead (just a NULL check)

- Cost: <1% slower INSERTs

## Optimization Strategies

**1. Index foreign keys:**

```sql

CREATE TABLE orders (

order_id SERIAL PRIMARY KEY,

customer_id INT NOT NULL,

FOREIGN KEY (customer_id) REFERENCES customers(customer_id)

);

-- Always add this index:

CREATE INDEX idx_orders_customer_id ON orders(customer_id);

```

Without this index, deleting a customer requires a full table scan of orders to check for references.

**2. Use simple CHECK constraints:**

```sql

-- Fast:

CHECK (price > 0)