89 / 100

DROP TYPE

Turni o'chirish

Ushbu qo'llanmada PostgreSQLda yaratilgan maxsus ma'lumot turini (kompozit tur, enum va h.k.) bazadan butunlay o'chirish uchun DROP TYPE buyrug'idan qanday foydalanishni o'rganasiz.

PostgreSQL DROP TYPE buyrug'iga kirish

DROP TYPE buyrug'i ma'lumotlar bazasidan foydalanuvchi tomonidan kiritilgan bitta yoki bir nechta ma'lumot turlarini o'chirib tashlaydi.

DROP TYPE buyrug'ining sintaksisi:

query.sql

DROP TYPE [IF EXISTS] type_name [CASCADE | RESTRICT];

Sintaksis tushuntirishi:

type_name — o'chirilishi kerak bo'lgan ma'lumot turi nomi.
IF EXISTS — tur bazada mavjud bo'lmasa, xatolik chiqarmaydi.
RESTRICT (sukut bo'yicha) — agar ushbu turdan jadval ustunlarida yoki boshqa ob'ektlarda foydalanilayotgan bo'lsa, o'chirishni taqiqlaydi.
CASCADE — ushbu turga bog'liq barcha ob'ektlarni (masalan, jadval ustunlari, funksiyalar) ham birgalikda o'chirib yuboradi.

Bir vaqtning o'zida bir nechta turni o'chirish:

query.sql

DROP TYPE type1, type2, ...;

DROP TYPE amaliy misollari

1) Oddiy turni o'chirish

Avval contact nomli kompozit turni yaratamiz:

query.sql

CREATE TYPE contact AS ( 
    phone TEXT, 
    email TEXT 
);

Endi uni xavfsiz o'chiramiz:

query.sql

DROP TYPE IF EXISTS contact;

Tur muvaffaqiyatli o'chirildi.

2) Bog'liq ob'ektlarga ega bo'lgan turni o'chirish

Avval address turini va undan foydalanuvchi customers jadvalini yaratamiz:

query.sql

CREATE TYPE address AS (
  city VARCHAR,
  street VARCHAR,
  zip_code VARCHAR,
  state VARCHAR,
  country VARCHAR
);

CREATE TABLE customers (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR NOT NULL,
  email VARCHAR NOT NULL,
  billing_address address,
  shipping_address address
);

Endi address turini o'chirishga urinamiz:

query.sql

DROP TYPE address;

PostgreSQL rad javobini beradi:

matn

ERROR:  cannot drop type address because other objects depend on it
DETAIL:  column shipping_address of table customers depends on type address
column billing_address of table customers depends on type address
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

Chunki customers jadvalining ikkita ustuni aynan shu turga bog'langan.

Agar CASCADE opsiyasi qo'llanilsa:

query.sql

DROP TYPE address CASCADE;

Natija:

matn

NOTICE:  drop cascades to 2 other objects
DETAIL:  drop cascades to column shipping_address of table customers
drop cascades to column billing_address of table customers
DROP TYPE

PostgreSQL address turini o'chirish bilan birga, customers jadvalidan shipping_address va billing_address ustunlarini ham butunlay olib tashladi!

[!CAUTION] CASCADE opsiyasini qo'llashda juda ehtiyot bo'ling, aks holda u kutilmaganda jadvallaringizdagi ustunlar va ulardagi barcha ma'lumotlarni o'chirib yuborishi mumkin.

Xulosa

DROP TYPE orqali keraksiz ma'lumot turlari o'chiriladi.
Sukut bo'yicha RESTRICT rejimi ma'lumotlarni tasodifiy yo'qotishdan himoya qiladi.
CASCADE bog'liq ob'ektlar bilan birga tozalaydi, undan ehtiyotkorlik bilan foydalanish lozim.

Oldingi

Keyingi