PostgreSQL ya aiwatar da ingancin bayanan ta hanyar sharukodin — ka'idoji akan bayanan teburin. Bayan wadanda aka saba da su (, , , , , ), Postgres ya kara ingizamawa masu iko kamar sharukodin da flexible magudanar.
PostgreSQL ya aiwatar da ingancin bayanan ta hanyar sharukodin — ka'idoji akan bayanan teburin. Bayan wadanda aka saba da su (, , , , , ), Postgres ya kara ingizamawa masu iko kamar sharukodin da flexible magudanar.
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 sharukodin zai iya aiwatar da ƙarfillin kasuwanci mai yawa, gaba ɗaya abubuwan da suka shafi bambabbai da alamu na regex.
-- 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 sharukodin (sifa ta Postgres kawai) yana hana jeri da suka dani ta abokin aikin na alaƙa — sanannun, hana albarkatun lokacin da suke tatawa juna (ajiyoyin sau biyu), wanda UNIQUE sharukodin ba zai iya yin haka ba.
-- check constraints at COMMIT instead of immediately (useful for circular references)
FOREIGN KEY (...) REFERENCES ... DEFERRABLE INITIALLY DEFERRED
Sharukodin suna da mahimmanci ga ingancin bayanan — suna aiwatar da ka'idodin bayanan a matakin gida (sarsarin karewa na ƙarshe, koda yaushe an aiwatar dashi ba tare da nuni ta lambar aikacin ba), saboda haka fahimtar goyon sharukodin PostgreSQL yana da mahimmanci don tsara bayanan dabam-dabam masu ƙarfi.
Sanin sharukodin yanda aka saba da su (PRIMARY KEY, FOREIGN KEY tare da karfi na zaiswa, UNIQUE, NOT NULL, CHECK, DEFAULT) da abin da kowannen abu ya aiwata shine tushe ga tsarin gidage, wanda zai ba da garantu a matakin gida (keɓantaccen nuni, zaiswar da ta kirki, ƙimomin da suka zama dole, bayanan da suka yi daidai) waɗanda ke kare mun daidaitattun rashin damre, ƙa'idar masu sari, da bayanan marasa jiya ta hanyar kalmar lambar aikaci kawai ba.
Sharukodin CHECK na PostgreSQL suna da ƙarfi musamman, wanda ke aiwatar da ƙarfillin kasuwanci mai yawa gaba ɗaya abubuwan da suka shafi bambabbai (jituwa na hankali kamar end_date > start_date) kuma hatta alamu na regex — wanda zai ba da izin a gina ka'idodin yanki a tsari kaɗai.
Musamman mai mahimmanci shine sharukodin ta Postgres kawai EXCLUDE, wanda ke hana jeri masu suni ta abokan aikin na alaƙa — sauran daidaici hana albarkatun lokacin da suke tatawa juna (ajiyoyin sau biyu na fili/albarkatun), ƙarfin aiki wanda UNIQUE sharukodin ba zai iya buɗe ba kuma wanda zai maida mafi sauƙi a gida jerin gida tsari (ajiyoyin lokaci, buƙatar fili).
Fahimtar sharukodin — wadanda aka saba da su don ingancin, magudanar flexible na Postgres CHECK, da mai iko na EXCLUDE don hana suni — yana da mahimmanci don tsara bayanan da ke aiwatar da kirki ta atomatik.
Tun da ingancin bayanan yana da mahimmanci kuma sharukodin ke ba da garantu lambar aikaci ba zai iya yi, kuma tun da Postgres ya ba da ingizamawa masu iko (CHECK mai yawa, EXCLUDE don albarkatun) wajeje ga asali, fahimtar goyon sharukodin PostgreSQL yana da daraja, ilimin da ya shafi al'amari don gina bayanan jiya, tare da sharukodin EXCLUDE musamman kasancewar sifa ta Postgres da take amfani, bayanan a sani ko ajiyoyin lokaci/gida.