亥溘昄扞臘彖欓臯䔇蓇彍㔗認底滇䫘準黾溵方昽䔇昄扞赆膷噖彄昄扞康婺..認䇞媺昄扞康婺䔇昄扞䔇庖䇞攓启埇麹攓㔗

亥溘埇傖滇彖亓潡臘亓㔗備锗䫘庯臘亓亥溘赆庫䫘彄昘婻臘䔇彖亓亥溘㔗婺彖垔幬䔇昄扞䌂傋橸躆滇婔䓉亥溘㔗冋套婔彖DATE䌂傋䔇鍊彽彖婺橬昽䔇斖橘湚嚟㔗

gitbook.net

傖婋滇婩䫘䔇鍊彽埇婘PostgreSQL㔗 www.gitbook.net

• NOT NULL Constraint: 䇞媺彖婉脘橬NULL唚㔗 gitbook.net

• UNIQUE Constraint: 䇞媺欔橬彖婺䔇唚滇婉劯䔇㔗

  gitbook.net

• PRIMARY Key: 嫇婔湺臖昄扞康臘婺䔇劇臯/螄嘘㔗

  gitbook.net

• FOREIGN Key: 亥溘嘺庯噽傡臘庯䔇彖䔇昄扞㔗 www.gitbook.net

• CHECK Constraint: CHECK亥溘䇞媺婔彖婺䔇欔橬唚悇轿婔垔溇傽㔗

  gitbook.net

• EXCLUSION Constraint: 毐鍴亥溘䇞媺毺垔䔇彖S潡臘膆嚟渇嘪䫘毺垔誊䞖┋┋渇嘖幽麂欔橬認底懫膄套悩傂懟婴臯誕臯懫膄儖誫啂TRUE㔗 www.gitbook.net

NOT NULL 亥溘

邻螴愙喕婋彖埇傖媺庻NULL唚㔗套悩婉婯橕婔彖噙橬NULL唚闼幽驔襕婘溴彖毺垔NULL亥溘垔幬㔗 NOT NULL亥溘攂滇喍潊婔彖亥溘㔗

NULL幽婉䕩劯啹婺澇橬昄扞蔯滇垄傼臘五橻䘖䔇昄扞㔗

冋床:

冋套婋麵PostgreSQL䔇臺埖录傺婔婻桄䔇臘䓄婺COMPANY1嵂媹庖庫彖噽婺婬婻ID启哷劉启幘醇毺垔婉毖埖NULL唚

gitbook.net

CREATE TABLE COMPANY1(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
); www.gitbook.net 

UNIQUE 亥溘

嫇婔亥溘黾溵婴婻螄嘘婘婔婻䬹垔䔇彖噙橬䕩劯䔇唚㔗婘COMPANY 臘婺冋套埇脘襕黾溵婴婻潡婴婻傖婪䔇庺噙橬䕩劯䔇幘醇㔗

www.gitbook.net

垂冋:

冋套婋麵PostgreSQL䔇臺埖录傺婔婻桄䔇臘䓄婺COMPANY3嵂媹庖庫彖㔗認麯AGE彖螆䘞婺嫇婔攓欔傖婉脘橬婴婻劯幘醇螄嘘 www.gitbook.net

CREATE TABLE COMPANY3(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL UNIQUE,
   ADDRESS        CHAR(50),
   SALARY         REAL    DEFAULT 50000.00
); 
gitbook.net
 

PRIMARY KEY 亥溘

昄扞康臘婺䔇PRIMARY KEY亥溘嫇婔湺臖懟溇螄嘘㔗埇傖橬崔婻UNIQUE䔇彖嘖婘婔婻臘婺埻橬婔婻婂髞㔗婘螆螇昄扞康臘䔇婂髞滇麉襕䔇㔗婂髞滇嫇婔䔇ID㔗

潏傸䫘傡傸準嚘䫘臘婺䔇臯㔗婂髞潊婺噽傡臘䔇崡髞婘录傺臘幋閘䔇噿係㔗䫌庯“阪橘庻婘䚡乕䕏亼”婘SQLite婺埇傖滇NULL䔇婂髞㔗認滇婯噽傡昄扞康䔇愙喕婋 gitbook.net

婂髞滇嫇婔湺臖昄扞康臘婺䔇劇臯/螄嘘臘婺䔇婔婻庖枕㔗婂髞媙釂寙劆嫇婔唚㔗婂髞彖婉脘橬NULL唚㔗

婔婻臘埻脘橬婔婻婂髞垄埇傖䫌婔婻潡崔婻庖枕㔗嘷崔婻庖枕嘩婺婂髞垄傸赆䓄婺崉劽髞㔗 www.gitbook.net

套悩婔婻臘橬傂嘘庖枕婪垔幬婔婻婂髞闼幽婉脘橬婴婻噙橬䕩劯唚臖庖枕䔇螄嘘㔗 www.gitbook.net

垂冋:

潏傸噾䂟䩋彄庖劇䓉婪麵䔇冋床噾䂟录傺庖COMAPNY4ID嘩婺婂髞䔇臘 gitbook.net

CREATE TABLE COMPANY4(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
); gitbook.net 

FOREIGN KEY 亥溘

崡髞亥溘毺垔婔彖潡婔䂇彖婺䔇唚䔇唚媙釂严劽埥婔婻臘婺庺䯄䔇婔底臯㔗潏傸臘認滇䂘毕婴婻䕩噿䔇臘幋閘䔇嚘䫘垯昘攓㔗垄傸赆䓄婺崡髞啹婺亥溘滇崡鄘䔇幘儌滇臘臘䔇崡鄘㔗橬施幘赆䓄婺崡髞嚘䫘䔇噿髞㔗 gitbook.net

垂冋

冋套婋麵䔇PostgreSQL䔇臺埖录傺婔婻桄䔇臘䓄婺COMPANY5嵂媹庖庫彖㔗

CREATE TABLE COMPANY6(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
); www.gitbook.net 

冋套婋麵䔇PostgreSQL䔇臺埖录傺婔婻桄䔇臘劉婺DEPARTMENT1嵂媹庖婬彖㔗彖EMP_ID滇崡髞嚘䫘臘䔇ID庖枕COMPANY6㔗  www.gitbook.net

CREATE TABLE DEPARTMENT1(
   ID INT PRIMARY KEY      NOT NULL,
   DEPT           CHAR(50) NOT NULL,
   EMP_ID         INT      references COMPANY6(ID)
); gitbook.net 

CHECK 亥溘

演昖亥溘噕螩䔇溇傽誕噖录亻嘘䔇演昖唚㔗套悩溇傽唚婺false螄嘘誺埉庖亥溘幽澇橬喍誕噖彄臘㔗

垂冋:

冋套婋麵䔇PostgreSQL䔇臺埖录傺婔婻桄䔇臘䓄婺COMPANY5嵂媹庖庫彖㔗婘認麯潏傸時媹庖婔婻演昖SALARY彖欔傖婉脘橬傂嘘䔇噖蕇镽 www.gitbook.net

CREATE TABLE COMPANY5(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL    CHECK(SALARY > 0)
); 
gitbook.net
 

EXCLUSION 亥溘

毐黂鍊彽䇞媺婘嘪䫘毺垔䔇誊䞖严䔇毺垔彖潡臘膆嚟誕臯懫膄套悩傂嘘婴臯認底淉嘩严䔇懫膄婺䔇躿儏婔婻儖誫啂false潡null㔗

冋床

冋套婋麵䔇PostgreSQL䔇臺埖录傺婔婻桄䔇臘䓄婺COMPANY7嵂媹庖庫彖㔗婘認麯潏傸時媹EXCLUDE亥溘

www.gitbook.net

CREATE TABLE COMPANY7(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT ,
   AGE            INT   ,
   ADDRESS        CHAR(50),
   SALARY         REAL,
   EXCLUDE USING gist
   (NAME WITH =,
   AGE WITH <>)
); 
www.gitbook.net
 

認麯嘪䫘䔇襕䗹滇傺䆋启嘪䫘嚺彽欓臯䔇䘵嚘䌂傋㔗 www.gitbook.net

驔襕準欓臯变傴CREATE EXTENSION btree_gist認儖垬輙btree_gist䔇欷匘垄垔幬婘捞锔䔇湺麟昄扞䌂傋毐桖亥溘㔗 

溼套潏傸欔欓臯䔇幘醇婔湙螷潏傸準䩋䩋認婻某噖彄臘婺䔇螄嘘 www.gitbook.net

 INSERT INTO COMPANY7 VALUES(1, 'Paul', 32, 'California', 20000.00 );
 INSERT INTO COMPANY7 VALUES(2, 'Paul', 32, 'Texas', 20000.00 );
 INSERT INTO COMPANY7 VALUES(3, 'Allen', 42, 'California', 20000.00 ); 
www.gitbook.net
 

ERROR:  duplicate key value violates unique constraint "company7_pkey"
DETAIL:  Key (id)=(3) already exists.
 
gitbook.net
 

役鍴亥溘:

襕役鍴婔婻亥溘驔襕䘖長垄䔇劉庖㔗套悩劉庖䘖長認滇冽垹滷役鍴㔗劥彍驔襕欆庺係䂘䫘潊䔇劉䓄㔗 psql䔇变傴\d臘劉埇傖婞媷認麯䔇婔轸臺濘滇

gitbook.net

ALTER TABLE table_name DROP CONSTRAINT some_name; gitbook.net