IDENTITY

Kirish: Ushbu qo'llanmada siz PostgreSQL IDENTITY ustunidan foydalanib, avtomatik ravishda unikal ketma-ket sonlarni generatsiya qilishni o'rganasiz.

PostgreSQL IDENTITY ustuniga kirish

PostgreSQLda IDENTITY ustuni SQL standarti asosida ishlovchi qulay mexanizm bo'lib, unikal butun sonlarni ichki ketma-ketlik (sequence) orqali avtomatik ravishda yaratib beradi.

IDENTITY ustunini yaratish uchun ikkita cheklovdan biri qo'llaniladi:

1. GENERATED ALWAYS AS IDENTITY: Ustun faqat tizim tomonidan generatsiya qilingan sonlarni qabul qiladi. INSERT yoki UPDATE amallarida foydalanuvchi qo'lda son kirita olmaydi (faqat OVERRIDING SYSTEM VALUE ko'rsatilgandagina ruxsat beriladi).
2. GENERATED BY DEFAULT AS IDENTITY: Standart holatda tizim son generatsiya qiladi, biroq xohlasangiz qo'lda o'zingiz ham ixtiyoriy son kiritishingiz mumkin.

Ko'pincha IDENTITY ustuni jadvalning birlamchi kaliti (PRIMARY KEY) sifatida belgilanadi:

CREATE TABLE table_name (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  column1 data_type,
  ...
);

INT dan tashqari SMALLINT va BIGINT turlaridan ham foydalanish mumkin.

IDENTITY ustunini birlamchi kalit sifatida ishlatish

Buyurtmalar sarlavhasini saqlovchi sales_orders jadvalini yaratamiz:

CREATE TABLE sales_orders (
    order_no INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_name VARCHAR(255) NOT NULL,
    status VARCHAR(25) NOT NULL,
    requested_date DATE NOT NULL,
    created_date TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Jadvalga qatorlar kiritamiz (order_no ko'rsatilmaydi):

INSERT INTO
  sales_orders (customer_name, status, requested_date)
VALUES
  ('John Doe', 'Pending', '2024-12-15'),
  ('Jane Doe', 'Completed', '2024-12-20') 
RETURNING order_no;

Natija:

 order_no
----------
        1
        2

Agar GENERATED ALWAYS ustuniga qo'lda qiymat kiritishga urinsak:

INSERT INTO sales_orders (order_no, customer_name, status, requested_date)
VALUES (3, 'Adam Smith', 'Pending', '2024-12-17');

PostgreSQL xatolik chiqaradi:

ERROR: column "order_no" can only be updated to DEFAULT
DETAIL: Column "order_no" is an identity column defined as GENERATED ALWAYS.

Bunday ustunga majburiy qo'lda qiymat berish uchun OVERRIDING SYSTEM VALUE bandi ishlatiladi:

INSERT INTO sales_orders (order_no, customer_name, status, requested_date)
OVERRIDING SYSTEM VALUE
VALUES (3, 'Adam Smith', 'Pending', '2024-12-17')
RETURNING order_no;

Natija:

 order_no
----------
        3

GENERATED BY DEFAULT AS IDENTITY misoli

Buyurtma tarkibidagi tovarlarni saqlovchi sales_order_items jadvalini yaratamiz:

CREATE TABLE sales_order_items (
  order_no INT,
  item_no INT GENERATED BY DEFAULT AS IDENTITY UNIQUE,
  product_name VARCHAR(255) NOT NULL,
  quantity INT NOT NULL CHECK (quantity > 0),
  price DEC(15, 2) NOT NULL CHECK (price > 0),
  PRIMARY KEY (order_no, item_no),
  FOREIGN KEY (order_no) REFERENCES sales_orders (order_no) ON DELETE CASCADE
);

Ushbu jadvalda item_no kiritilmasa avtomatik generatsiya bo'ladi, xohlasangiz qo'lda ham qiymat kiritishingiz mumkin:

INSERT INTO
  sales_order_items (order_no, item_no, product_name, quantity, price)
VALUES
  (1, 3, 'Charger', 5, 29.99)
RETURNING order_no, item_no;

Ketma-ketlik parametrlarini sozlash (START, INCREMENT)

IDENTITY ustuniga boshlang'ich son (START) va qadam qiymatini (INCREMENT) belgilash mumkin:

CREATE TABLE sales_order_item_schedules (
  item_no INT,
  schedule_no INT GENERATED BY DEFAULT AS IDENTITY (START 10 INCREMENT 10),
  delivery_qty INT NOT NULL,
  delivered_on DATE NOT NULL,
  PRIMARY KEY (item_no, schedule_no),
  FOREIGN KEY (item_no) REFERENCES sales_order_items (item_no) ON DELETE CASCADE
);

Endi qiymatlar 10, 20, 30 ko'rinishida generatsiya qilinadi.

Mavjud ustunni IDENTITY ga aylantirish

Mavjud jadvaldagi ustunga IDENTITY qo'shish uchun:

ALTER TABLE taxes
ALTER COLUMN id 
ADD GENERATED ALWAYS AS IDENTITY;

Ustun avvaldan NOT NULL chekloviga ega bo'lishi shart.

IDENTITY cheklovini olib tashlash

Ustundan IDENTITY xususiyatini o'chirish:

ALTER TABLE taxes
ALTER COLUMN id
DROP IDENTITY IF EXISTS;

Xulosa

• SQL standartidagi avtomatik unikal butun sonlar uchun IDENTITY ustunlaridan foydalaning.
• Qat'iy nazorat uchun GENERATED ALWAYS AS IDENTITY, moslashuvchanlik uchun esa GENERATED BY DEFAULT AS IDENTITY tanlanadi.
• Ichki ketma-ketlikni START va INCREMENT orqali sozlash mumkin.