قيود UNIQUE والمفاتيح المركبة

UNIQUE: لا يُسمح بالتكرارات

CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  username VARCHAR(50) UNIQUE
);

ينشئ UNIQUE فهرسًا

-- This shows the implicit index:
SELECT indexname FROM pg_indexes WHERE tablename = 'users';

UNIQUE مركب

CREATE TABLE memberships (
  user_id BIGINT NOT NULL,
  group_id BIGINT NOT NULL,
  role TEXT NOT NULL,
  UNIQUE (user_id, group_id)        -- a user can be in a group at most once
);

UNIQUE مقابل PRIMARY KEY

NULL في UNIQUE

CREATE TABLE invites (email VARCHAR(255) UNIQUE);
INSERT INTO invites VALUES (NULL), (NULL);   -- both succeed

-- PostgreSQL 15+ offers UNIQUE NULLS NOT DISTINCT to treat NULL as equal:
CREATE TABLE invites2 (email VARCHAR(255) UNIQUE NULLS NOT DISTINCT);

مفتاح أساسي مركب

CREATE TABLE order_items (
  order_id   BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  product_id BIGINT NOT NULL REFERENCES products(id),
  quantity   INT NOT NULL CHECK (quantity > 0),
  PRIMARY KEY (order_id, product_id)
);

مفتاح بديل + مفتاح طبيعي UNIQUE

CREATE TABLE products (
  id BIGSERIAL PRIMARY KEY,
  sku VARCHAR(50) NOT NULL UNIQUE,
  name TEXT NOT NULL
);

فهارس UNIQUE جزئية

-- One active email per user (deleted users can re-register):
CREATE UNIQUE INDEX users_active_email_idx
  ON users (email)
  WHERE deleted_at IS NULL;

فهارس UNIQUE للتعبيرات

CREATE UNIQUE INDEX users_email_ci_idx ON users (LOWER(email));

أخطاء انتهاك UNIQUE

ERROR:  duplicate key value violates unique constraint "users_email_key"
DETAIL:  Key (email)=(alice@example.com) already exists.

يعتمد UPSERT على UNIQUE

مراجعة

اختبار سريع