PostgreSQL ngenforcer integritas data liwat constraints — aturan ing data tabel. Saliyane sing standar (, , , , , ), Postgres nambah opsi sing kuat kayata constraints lan expressions sing fleksibel.
PostgreSQL ngenforcer integritas data liwat constraints — aturan ing data tabel. Saliyane sing standar (, , , , , ), Postgres nambah opsi sing kuat kayata constraints lan expressions sing fleksibel.
PRIMARY KEYFOREIGN KEYUNIQUENOT NULLCHECKDEFAULTCHECKCREATE TABLE bookings (
id SERIAL PRIMARY KEY, -- unique identifier
room_id INT NOT NULL REFERENCES rooms(id), -- foreign key (referential integrity)
email VARCHAR(255) UNIQUE NOT NULL, -- unique + required
guests INT CHECK (guests > 0 AND guests <= 10), -- value constraint
status TEXT DEFAULT 'pending' -- default value
);
PRIMARY KEY → unique row identifier
FOREIGN KEY → references another table (referential integrity, with ON DELETE options)
UNIQUE → no duplicate values
NOT NULL → must have a value
CHECK → value must satisfy a condition
DEFAULT → default value when not provided
CHECK (price > 0)
CHECK (end_date > start_date) -- logical consistency between columns
CHECK (status IN ('active', 'inactive', 'pending'))
CHECK (email ~ '^[^@]+@[^@]+\.[^@]+$') -- regex validation
Postgres CHECK constraints bisa ngenforce bisnis rules sing kaya, kalebu kondisi lintas kolom lan regex patterns.
-- prevent OVERLAPPING bookings for the same room (impossible with UNIQUE alone!)
CREATE TABLE bookings (
room_id INT,
during TSRANGE, -- a time range
EXCLUDE USING GIST (room_id WITH =, during WITH &&) -- no two rows where room_id is
); -- equal AND time ranges OVERLAP
EXCLUDE constraints (fitur khusus Postgres) nyegah baris sing konflik miturut operator custom — terkenal, nyegah overlapping time ranges (double-bookings), sing UNIQUE constraint ora bisa gawe.
-- check constraints at COMMIT instead of immediately (useful for circular references)
FOREIGN KEY (...) REFERENCES ... DEFERRABLE INITIALLY DEFERRED
Constraints penting banget kanggo data integrity — ngenforce aturan data ing tingkat database (jalur pertahanan paling akhir, selalu dieksekusi apapun kode aplikasi), dadi ngerti dukungan constraint PostgreSQL penting kanggo ndesain database sing kuat.
Ngerti constraints standar (PRIMARY KEY, FOREIGN KEY karo referential integrity, UNIQUE, NOT NULL, CHECK, DEFAULT) lan apa sing dipaksa saben-saben iku fundamental kanggo desain skema, ngeduweni jaminan tingkat database (keunikan, referensi valid, nilai kudu ana, data valid) sing lindungi menyang bugs, race conditions, lan data sing ora konsisten karo cara kode aplikasi dhewe ora bisa.
Postgres CHECK constraints iku kapabel banget, ngenforce bisnis rules sing kaya kalebu kondisi lintas kolom (konsistensi logikal kayata end_date > start_date) lan malah regex patterns — ngliloni kowe enkod domain rules langsung ing skema.
Sangat berharga iku Postgres-specific EXCLUDE constraint, sing nyegah baris konflik miturut operator custom — paling utama nyegah overlapping time ranges (double-bookings kanggo kamar/resource), kapabilitas sing kuat sing UNIQUE constraint ora bisa diwedharake lan sing elegan mecahake masalah real-world umum (penjadwalan, reservasi) ing tingkat database.
Ngerti constraints — sing standar kanggo integrity, fleksibilitas Postgres CHECK expressions, lan EXCLUDE sing kuat kanggo nyegah konflik — penting kanggo ndesain database sing ngenforce kebenaran otomatis.
Sebab integritas data kritis lan constraints ngeduweni jaminan kode aplikasi ora bisa, lan sebab Postgres ngeduweni opsi sing kuat (CHECK kaya, EXCLUDE kanggo overlaps) buwana standar, ngerti dukungan constraint PostgreSQL iku ilmu sing berharga, praktis-relevan kanggo nbangun database sing kuat, karo EXCLUDE constraint khususan dadi kapabilitas Postgres sing beda, berguna sing patut dikenal kanggo sistem penjadwalan/reservasi.