PostgreSQL 8.2.3 婺桺桺懼
劯锔媆锔䆹5. 昄扞垔幬媆誕嬉誕

5.3. 亥溘

昄扞䌂傋滇鍊彽潏傸埇傖婘臘麯庻嗘傔幽昄扞䔇婔䓉桹濘㔗婉誺凹庯螩崔庫䫘準臘認䓉鍊彽垂婘滇崻侖俍庖㔗懫套婔婻寙劆库巕備湚䔇庖枕庫臖埻毖埖溼昄㔗嘖滇澇橬巻䓉湺庖昄扞䌂傋埻毖埖溼昄㔗埥崡婔婻閞鵻滇嘹埇脘驔襕湹扞噽垄庖枕潡蔙噽垄臯䔇昄扞準亥溘庖枕昄扞㔗懫套婘婔婻寙劆库巕媇敇䔇臘婺懟婻库巕䚡埙鄘庫臖埻橬婔臯㔗

凹庯認底閞鵻SQL 噕螩嘹婘庖枕启臘婪垔幬亥溘㔗亥溘噕螩嘹凹昄扞桘媹傂懟毓彽㔗套悩䫘潙嚕商婘庖枕麯庻嗘誺埉亥溘䔇昄扞闼幽儌嚔檕庺婔婻髍臇㔗認䓉愙喕劯施幘锗䫘庯昄唚準躻䚺䩕唚䔇愙喕㔗

5.3.1. 演昖亥溘

演昖亥溘滇橔婩蓕䔇亥溘䌂傋㔗垄噕螩嘹弄滯婘昊婻庖枕麯䔇昄唚媙釂嘪婔婻婄儫臘膆嚟婺䩘㔗懫套襕嚺彽婔婻溼昄䔇库巕備湚嘹埇傖䫘

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0)
);

套嘹欔蓕亥溘垔幬婘昄扞䌂傋幋劯儌喘償䚺䩕唚垔幬婔湙㔗䚺䩕唚启亥溘埇傖毬傂懟釺废毐彖㔗婔婻演昖亥溘䫌婔婻噿髞庖 CHECK 劯麵虘婔婻櫆婘婖拸嚓麯䔇臘膆嚟䂇潊㔗演昖亥溘臘膆嚟庫臖寙劆埖亥溘䔇庖枕劥彍認婻亥溘儌澇傔幽懟幬庖㔗

嘹誻埇傖䂍認婻亥溘埡婔婻䋸䆋䔇劉庖㔗認湙儌埇傖傴髍臇媇敇敘橙捄幽婫婘嘹驔襕媞櫹垄䔇施唍嚘䫘認婻劉庖㔗臺濘滇

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CONSTRAINT positive_price CHECK (price > 0)
);

啹溴襕弄滯婔婻变劉亥溘嘪䫘噿髞庖 CONSTRAINT 劯麵虘婔婻湺臖严(嘩婺劉庖)䇽劯喉虘亥溘垔幬㔗套悩嘹婉䫘認婻桹濘弄滯亥溘闼幽係䂘嚔躻媘婺嘹锬拷婔婻劉庖㔗

婔婻演昖亥溘幘埇傖嚘䫘崔婻庖枕㔗啺螆嘹庻嗘婔婻溼婩備湚启婔婻檻欼備幽婫嘹愿媺臕檻欼備懫溼婩備嘯㔗

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0),
    discounted_price numeric CHECK (discounted_price > 0),
    CHECK (price > discounted_price)
);

崘婴婻亥溘䩋婪寂冽麵䖘㔗丸婬婻嘪䫘庖婔婻桄䔇臺濘㔗垄澇橬鍇五婘昊婻庖枕婪蔯滇婘锖埙彖锫䔇庖枕彖臘婺傖婔婻䋸䆋臯䔇嘵嚟庺䯄㔗庖枕垔幬启亥溘垔幬埇傖毬䙓傂懟釺废彖庺㔗

潏傸䓄崘婴婻亥溘滇"庖枕亥溘"蔯丸婬婻亥溘滇"臘亥溘"(启庖枕垔幬彖嚔喍)㔗庖枕亥溘幘埇傖喍潊臘亥溘蔯埉誺準冽埇脘婉臯啹婺係䂘啺螆庖枕亥溘埻嚘䫘垄欔傯匂䔇庖枕㔗PostgreSQL 幽婉嚺彽認溇蓇彍嘖滇套悩嘹婯橕躻噌䔇臘垔幬埇傖启噽垄昄扞康係䂘噚垹闼幽嘹橔喘誻滇镕冻認溇蓇彍㔗婪麵䔇冋床幘埇傖認幽喍

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric,
    CHECK (price > 0),
    discounted_price numeric,
    CHECK (discounted_price > 0),
    CHECK (price > discounted_price)
);

潡蔙滇

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0),
    discounted_price numeric,
    CHECK (discounted_price > 0 AND price > discounted_price)
);

認埻滇鼯湚䔇婉劯㔗

启庖枕亥溘婔湙潏傸幘埇傖䂍臘亥溘蕋庽劉䓄桹濘幘䕩劯

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric,
    CHECK (price > 0),
    discounted_price numeric,
    CHECK (discounted_price > 0),
    CONSTRAINT valid_discount CHECK (price > discounted_price)
);

潏傸誻襕濘懟䔇滇嘷亥溘臘膆嚟螇䞖䂷悩婺 NULL 䔇施唍演昖亥溘嚔赆螴婺滇悇轿溇傽䔇㔗啹婺崓崔昄臘膆嚟婘劆橬 NULL 淉嘩昄䔇施唍䂷悩鄘滇 NULL 欔傖認底亥溘婉脘黂溵庖枕唚婺 NULL 㔗襕䇞媺婔婻庖枕唚婉婺 NULL 埇傖嘪䫘婋婔誗傋䂉䔇麂䷺亥溘㔗

5.3.2. 麂䷺亥溘

麂䷺亥溘埻滇䞔剘婄弄滯婔婻庖枕媙釂婉脘滇 NULL 㔗婋麵滇婔婻冋床

CREATE TABLE products (
    product_no integer NOT NULL,
    name text NOT NULL,
    price numeric
);

婔婻麂䷺亥溘攂滇喍潊婔婻庖枕亥溘㔗麂䷺亥溘婘媘脘婪京昽庯录傺婔婻演昖亥溘 CHECK (column_name IS NOT NULL) 嘖婘 PostgreSQL 麯录傺婔婻滯䇞䔇麂䷺亥溘昽䯺敘醻㔗䚺䗹滇嘹婉脘䂍垄婔婻滯䇞䔇劉庖㔗

嘷䇽婔婻庖枕埇傖橬崔婻亥溘㔗埻襕婔婻毖五婔婻喍儌埇傖庖

CREATE TABLE products (
    product_no integer NOT NULL,
    name text NOT NULL,
    price numeric NOT NULL CHECK (price > 0)
);

垄傸䔇釺废方欔脷㔗釺废幽婉嘌巉亥溘演昖䔇釺废㔗

NOT NULL 亥溘橬婻䕩埉䔇亥溘NULL 亥溘㔗垄幽婉懟叿五臖庖枕媙釂滇䷺啹婺認湙䔇庖枕幘澇䫘㔗垄埻滇垔幬庖臖庖枕埇傖婺䷺䔇認婻䚺䩕臯婺㔗婘 SQL 湺庖麯澇橬垔幬 NULL 亥溘啹溴婉庫臖婘埇䓂洉䔇庫䫘婺嘪䫘垄㔗婘 PostgreSQL 麯麵嵂媹認婻亥溘埻滇婺庖启噽垄昄扞康係䂘噚垹㔗婉誺橬底䫘潙塩渵垄啹婺認婻亥溘埇傖螷傡傸冽垹滷婘臔橸桺傽麯彺扵亥溘㔗懫套嘹埇傖傯婋麵認湙嚔哋

CREATE TABLE products (
    product_no integer NULL,
    name text NULL,
    price numeric NULL
);

䇽劯婘驔襕䔇施唍某噖 NOT 噿髞庖㔗

㔊柊䴺㔏婘崓崔昄昄扞康螆螇麯婂襕䔇庖枕鄘庫臖湺螄婺麂䷺㔗

5.3.3. 嫇婔亥溘

嫇婔亥溘媺臕婘婔婻庖枕潡蔙婔䂇庖枕麯䔇昄扞婯臘婺噽垄臯䔇昄扞䕩懫滇嫇婔䔇㔗垄䔇臺濘滇

CREATE TABLE products (
    product_no integer UNIQUE,
    name text,
    price numeric
);

婪麵滇喍潊庖枕亥溘婋麵認婻彍喍潊臘亥溘

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric,
    UNIQUE (product_no)
);

套悩婔婻嫇婔亥溘嚘䫘婔䂇庖枕闼幽認底庖枕䫘锖埙彖锫彖庺

CREATE TABLE example (
    a integer,
    b integer,
    c integer,
    UNIQUE (a, c)
);

認湙儌弄滯庖䬹垔庖枕唚䔇䂇劽婘昘婻臘评啘喙滇嫇婔䔇㔗嘖滇認底庖枕婺䔇昊婻剘䋸唚埇傖婉媙滇(幽婫锔婩幘䇞垂婉滇)嫇婔䔇㔗

嘹幘埇傖䂍嫇婔亥溘蕋庽婔婻躻噌垔幬䔇劉庖桹濘婯嬉麵䕩劯

CREATE TABLE products (
    product_no integer CONSTRAINT must_be_different UNIQUE,
    name text,
    price numeric
);

锔婩套悩寙劆婘嫇婔亥溘婺䔇闼庹婻庖枕婘臘婺橬崔婻䕩劯䔇臯儌誺埉庖嫇婔亥溘㔗嘖滇婘認䓉懫膄婺NULL 赆螴婺滇婉䕩京䔇㔗認儌懟叿五婘崔庖枕嫇婔亥溘䔇愙喕婋套悩婘躿儏婔婻庖枕婪庺䯄 NULL 闼幽潏傸誻滇埇傖庻嗘劯湙䔇認䓉昄扞臯㔗認䓉臯婺镕冻 SQL 湺庖嘖滇潏傸劸臘噽垄 SQL 昄扞康埇脘婉镕冻認婻湺庖㔗啹溴套悩嘹襕嚔埏埇䓂洉䔇䘋废闼幽橔喘傫䂖底㔗

5.3.4. 婂髞

傯檔橇婪螾婂髞亥溘埻滇嫇婔亥溘启麂䷺亥溘䔇䂇劽㔗欔傖婋麵婴婻臘垔幬滇京備䔇

CREATE TABLE products (
    product_no integer UNIQUE NOT NULL,
    name text,
    price numeric
);
CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

婂髞幘埇傖亥溘崔庯婔婻庖枕噽臺濘䌂嚚庯嫇婔亥溘

CREATE TABLE example (
    a integer,
    b integer,
    c integer,
    PRIMARY KEY (a, c)
);

婂髞臘䴺婔婻潡崔婻庖枕䔇䂇劽埇傖䫘庯嫇婔湺臖臘婺䔇昄扞臯㔗認滇垔幬婔婻婂髞䔇䕘毖䂷悩㔗臙濘懟婔婻嫇婔亥溘垂鍙婪幽婉脘柊冕婔婻嫇婔湺臖啹婺垄婉毐鍴 NULL 㔗認婻媘脘凹桺懼䕞䔇启垵潙庫䫘鄘冽橬䫘㔗懫套婔婻埇傖媞櫹臯昄唚䔇 GUI 庫䫘埇脘驔襕䘖長婔婻臘䔇婂髞欉脘嫇婔婄湺臖懟婔臯㔗

婔婻臘橔崔埇傖橬婔婻婂髞(嘖滇垄埇傖橬崔婻嫇婔启麂䷺亥溘)㔗噿係傋昄扞康䊖螺只臬潏傸懟婻臘鄘媙釂橬婔婻婂髞㔗PostgreSQL 幽婉嚺彽認婻蓇彍嘖潏傸橔喘誻滇镕冻垄㔗

5.3.5. 崡髞

崡髞亥溘弄滯婔婻庖枕(潡蔙婔䂇庖枕)䔇昄唚媙釂对陉埥崡婔婻臘婺庺䯄䔇昄唚㔗潏傸檪認婻臯婺䓄婺婴婻䕩噿臘幋閘䔇埗䙓垯昘攓㔗

啺螆嘹橬婻库巕臘潏傸埇脘嘪䫘庖喘庹渇

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

啺螆嘹橬婔婻庻嗘認底库巕䔇螵剘䔇臘㔗潏傸愿媺臕螵剘臘埻寙劆垂鍙庻婘䔇库巕㔗啹溴潏傸婘螵剘臘婺垔幬婔婻崡髞亥溘嚘䫘库巕臘

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_no integer REFERENCES products (product_no),
    quantity integer
);

䯄婘潏傸婉脘录傺傂嘘噽 product_no 澇橬婘库巕臘婺庺䯄䔇螵剘㔗

婘認䓉愙喕婋潏傸檪螵剘臘埆啔嚘䫘臘蔯库巕臘埆啔赆嚘䫘臘㔗劯湙幘橬嚘䫘庖枕启赆嚘䫘庖枕㔗

嘹幘埇傖檪婪麵䔇变傴䞔喍潊

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_no integer REFERENCES products,
    quantity integer
);

啹婺套悩䚺儏庖枕彖臘䔇臺儌嚔嚘䫘赆嚘䫘臘䔇婂髞㔗

婔婻崡髞幘埇傖亥溘启嚘䫘婔䂇庖枕㔗劯湙幘驔襕喍潊臘亥溘䔇嘵嚟㔗婋麵滇婔婻扟锹庺準䔇臺濘冋床

CREATE TABLE t1 (
  a integer PRIMARY KEY,
  b integer,
  c integer,
  FOREIGN KEY (b, c) REFERENCES other_table (c1, c2)
);

嘷䇽赆亥溘䔇庖枕昄䕞启䌂傋驔襕启赆嚘䫘庖枕昄䕞启䌂傋婔躘㔗

启广婩婔湙嘹幘埇傖䂍崡髞亥溘蕋庽躻垔幬䔇劉庖㔗

婔婻臘埇傖寙劆崔庯婔婻崡髞亥溘㔗認婻䬹攓䫘庯垂䯄臘幋閘䔇崔凹崔噿係㔗懫套嘹橬噿庯库巕启螵剘䔇臘嘖䯄婘嘹愿噕螩婔婻螵剘埇傖寙劆崔䓉库巕(婪麵闼婻䂷悇滇婉噕螩認幽啔䔇)嘹埇傖嘪䫘認湙䔇䂷悇

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    shipping_address text,
    ...
);

CREATE TABLE order_items (
    product_no integer REFERENCES products,
    order_id integer REFERENCES orders,
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);

濘懟橔劯䔇臘䔇婂髞启崡髞滇麉培䔇㔗

潏傸䘖長崡髞婉噕螩录傺启傂嘘库巕鄘方噿䔇螵剘㔗嘖滇套悩婔婻螵剘录傺幋劯噽嚘䫘䔇库巕赆役鍴庖支幽媂SQL 幘噕螩嘹崇䊖認婻閞鵻㔗䞔剘臘潏傸橬庹䓉锬拷

婺庖臘滯認婻閞鵻潏傸凹婪麵䔇崔凹崔噿係彽垔婋麵䔇亡䘖套悩橬庺愿役鍴婔䓉傉䇽赆昊婻螵剘嚘䫘䔇库巕(锔誺 order_items)闼幽儌婉噕螩認幽啔㔗套悩橬庺役鍴庖婔婻螵剘闼幽螵剘釹幘赆役鍴㔗

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    shipping_address text,
    ...
);

CREATE TABLE order_items (
    product_no integer REFERENCES products ON DELETE RESTRICT,
    order_id integer REFERENCES orders ON DELETE CASCADE,
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);

鍊彽启亓蕫役鍴滇婴䓉橔婩蓕䔇锬釹㔗RESTRICT 䥕溵役鍴赆嚘䫘䔇臯㔗NO ACTION 䔇懟攺滇套悩婘演昖亥溘䔇施唍誻庻婘傂嘘嚘䫘臯彍檕庺髍臇套悩嘹婉弄滯傂嘘婩薪闼幽垄儌滇䚺䩕䔇臯婺㔗認婴婻锬拷䔇垂鍙寺彆滇NO ACTION 噕螩亥溘演昖毘誘彄庋媇䔇捔底施唍蔯 RESTRICT 婉臯㔗CASCADE 弄滯婘役鍴婔婻赆嚘䫘䔇臯䔇施唍欔橬嚘䫘垄䔇臯幘嚔赆躻媘役鍴毬㔗婘崡髞庖枕婪䔇媘嘩誻橬婴婻锬釹SET NULL 启 SET DEFAULT 垄傸凚躘婘赆嚘䫘臯役鍴䔇施唍儖嚘䫘垄傸䔇庖枕彖彆螆䘞婺 NULL 启䚺䩕唚㔗臙濘懟認底锬釹幽婉脘螷嘹锄臌赆蓗凘启亥溘䔇嵄婄㔗懫套套悩婔婻媘嘩弄滯 SET DEFAULT 嘖滇䚺䩕唚幽婉脘悇轿崡髞闼幽臖媘嘩儌嚔崌蘖㔗

婯 ON DELETE 䌂嚚䔇誻橬 ON UPDATE 锬釹垄滇婘赆嚘䫘庖枕媞櫹(敘桄)䔇施唍脄䫘䔇埇䫘䔇媘嘩滇婔湙䔇㔗

橬噿敘桄启役鍴昄扞䔇敘崔媇敇埇傖婘䆹6麯欆彄㔗

橔劯潏傸庫臖臘滯䔇滇婔婻崡髞媙釂襕幽嚘䫘婔婻婂髞襕幽嚘䫘婔婻嫇婔亥溘㔗套悩崡髞嚘䫘庖婔婻嫇婔亥溘闼幽婘套嘘对陉 NULL 認婻閞鵻婪誻橬婔底噽垄䔇埇脘攓㔗認底婩薪鄘婘 CREATE TABLE 婺蓼麪㔗

劯锔饡釕嬉誕
䚺䩕唚婪婔亓係䂘庖枕