Narziyev Orzu

Narziyev Orzu

Narziyev Orzu

PostgreSQL quyidagi amallarni qo'llab -quvvatlaydi:

• SET NULL
• SET DEFAULT
• RESTRICT
• NO ACTION
• CASCADE

Narziyev Orzu

Narziyev Orzu

Customer

Customer_id

Customer_name

contacts

Contact_id

Customer_id

Contact_name

Phone

email

Narziyev Orzu

Narziyev Orzu

PostgreSQL tashqi kalitlarni cheklashga misollar
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS contacts;
CREATE TABLE customers(
customer_id serial,
customer_name VARCHAR(255) NOT NULL,
PRIMARY KEY(customer_id)
);
CREATE TABLE contacts(
contact_id serial,
customer_id INT,
contact_name VARCHAR(255) NOT NULL,
phone VARCHAR(15),
email VARCHAR(100),
PRIMARY KEY(contact_id),
CONSTRAINT fk_customer
FOREIGN KEY(customer_id)
REFERENCES customers(customer_id)
);

Narziyev Orzu

Narziyev Orzu

SET NULL
SET NULL, ota -jadvaldagi havola qilingan satrlar o'chirilganda, avtomatik ravishda NULL -ni bola jadvalining mos yozuvlar qatorlaridagi chet el kalit ustunlariga o'rnatadi.
Quyidagi iboralar namunaviy jadvallarni o'chirib tashlaydi va ularni ON DELETE bandidagi SET NULL amalidan foydalanadigan tashqi kalit yordamida qayta yaratadi:
Narziyev Orzu
SET NULL qanday ishlashini bilish uchun mijozlar jadvalidan id 1 bilan xaridorni o'chirib tashlaylik
DELETE FROM customers
WHERE customer_id = 1;
ON DELETE SET NULL harakati tufayli kontaktlar jadvalidagi mos yozuvlar qatorlari NULLga o'rnatiladi. Quyidagi bayonot kontaktlar jadvalidagi ma'lumotlarni ko'rsatadi:
SELECT * FROM contacts;
Chiqishdan aniq ko'rinib turibdiki, customer_id 1 bo'lgan qatorlar endi customer_id to'plamlarini NULLga ega.
Narziyev Orzu
CASCADE
ON DELETE CASCADE, ota -jadvaldagi havola qilingan qatorlar o'chirilganda, bola jadvalidagi barcha havola qatorlarini avtomatik ravishda o'chiradi. Amalda, ON DELETE CASCADE - eng ko'p ishlatiladigan variant.
CASCADE O'CHIRISH harakati tufayli kontaktlar jadvalidagi barcha havolalar avtomatik ravishda o'chiriladi:
Narziyev Orzu
Mavjud jadvalga chet el kalitlari cheklovini qo'shish uchun ALTER TABLE bayonotining quyidagi shakli ishlatiladi:
ALTER TABLE child_table
ADD CONSTRAINT constraint_name
FOREIGN KEY (fk_columns)
REFERENCES parent_table (parent_key_columns);
ALTER TABLE child_table
ADD CONSTRAINT constraint_fk
FOREIGN KEY (fk_columns)
REFERENCES parent_table(parent_key_columns)
ON DELETE CASCADE;
ALTER TABLE child_table
DROP CONSTRAINT constraint_fkey;
Narziyev Orzu
Narziyev Orzu
Narziyev Orzu
Narziyev Orzu