66 / 100

ALTER TABLE

Jadval tuzilmasini o'zgartirish

Kirish: Ushbu qo'llanmada siz PostgreSQL ALTER TABLE buyrug'i yordamida mavjud jadval tuzilmasini o'zgartirish (ustun qo'shish, o'chirish, nomini o'zgartirish va cheklovlar o'rnatish)ni o'rganasiz.

PostgreSQL ALTER TABLE buyrug'iga kirish

PostgreSQLda ALTER TABLE buyrug'i mavjud jadvalning tuzilmasini samarali o'zgartirish imkonini beradi.

Asosiy sintaksis:

query.sql

ALTER TABLE table_name
action;

Ushbu sintaksisda action bajarilishi kerak bo'lgan amalni bildiradi:

Jadval nomini o'zgartirish (RENAME TO);
Ustun nomini o'zgartirish (RENAME COLUMN);
Yangi ustun qo'shish (ADD COLUMN);
Ustunni o'chirish (DROP COLUMN);
Ustunning ma'lumot turini o'zgartirish (SET DATA TYPE);
Ustunga cheklovlar qo'shish (NOT NULL, CHECK, DEFAULT, UNIQUE).

Tajriba uchun dastlab materials jadvalini yaratib olamiz:

query.sql

CREATE TABLE materials (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(40) NOT NULL,
  status BOOL NOT NULL
);

Jadval nomini o'zgartirish: RENAME TO

Jadval nomini o'zgartirish sintaksisi:

query.sql

ALTER TABLE table_name
RENAME TO new_table_name;

Masalan, materials jadvali nomini products ga o'zgartiramiz:

query.sql

ALTER TABLE materials
RENAME TO products;

Ustun nomini o'zgartirish: RENAME COLUMN

Ustun nomini o'zgartirish sintaksisi:

query.sql

ALTER TABLE table_name
RENAME COLUMN column_name TO new_column_name;

Masalan, status ustunini active ga o'zgartiramiz:

query.sql

ALTER TABLE products
RENAME COLUMN status TO active;

Yangi ustun qo'shish: ADD COLUMN

Jadvalga yangi ustun qo'shish:

query.sql

ALTER TABLE table_name
ADD COLUMN column_name data_type constraint;

Masalan, products jadvaliga price ustunini qo'shamiz:

query.sql

ALTER TABLE products
ADD COLUMN price DEC(5, 2) NOT NULL;

Bir vaqtning o'zida bir nechta ustun qo'shish ham mumkin:

query.sql

ALTER TABLE products
ADD COLUMN weight DEC(5, 2),
ADD COLUMN volume DEC(5, 2),
ADD COLUMN model_no VARCHAR(25);

Ustunning ma'lumot turini o'zgartirish

Ustun turini kengaytirish yoki almashtirish uchun ALTER COLUMN ... SET DATA TYPE:

query.sql

ALTER TABLE table_name
ALTER COLUMN column_name SET DATA TYPE new_data_type;

Masalan, price ustunini DEC(11, 2) ga kengaytiramiz:

query.sql

ALTER TABLE products
ALTER COLUMN price SET DATA TYPE DEC(11, 2);

Cheklovlarni o'rnatish va o'zgartirish

NOT NULL cheklovi

query.sql

ALTER TABLE products
ALTER COLUMN weight SET NOT NULL,
ALTER COLUMN volume SET NOT NULL;

CHECK cheklovi

query.sql

ALTER TABLE products
ADD CHECK (weight > 0),
ADD CHECK (volume > 0);

DEFAULT cheklovi

query.sql

ALTER TABLE products
ALTER COLUMN active SET DEFAULT true;

UNIQUE cheklovi

query.sql

ALTER TABLE products
ADD UNIQUE (model_no);

Ustunni o'chirish: DROP COLUMN

Jadvaldan keraksiz ustunni olib tashlash:

query.sql

ALTER TABLE table_name
DROP COLUMN column_name;

Masalan, active ustunini o'chirib tashlaymiz:

query.sql

ALTER TABLE products
DROP COLUMN active;

Xulosa

Mavjud jadvallar strukturasini o'zgartirish uchun ALTER TABLE buyrug'idan foydalaning.
Bir nechta amallarni bitta ALTER TABLE buyrug'ida vergul bilan ajratib bir vaqtda bajarish mumkin.

Oldingi

Keyingi