CREATE TABLE语法
CREATE [[ GLOBAL | LOCAL ]{ TEMPORARY | TEMP }] TABLE table_name
(
{ column_name data_type [ DEFAULT default_expr ][ column_constraint [...]]
| table_constraint
| LIKE parent_table [{ INCLUDING | EXCLUDING } DEFAULTS ]
}[,...]
)
[ INHERITS ( parent_table [,...])]
[ WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP }]
[ TABLESPACE tablespace ]
紧跟在字段定义后
column_constraint
|
和字段定义并列写在表定义中（约束子句）
table_constraint
|
|
|
[CONSTRAINT constraint_name]
CHECK (expression)
|
[CONSTRAINT constraint_name]
CHECK ( expression )
|
表约
束
|
检查
|
NOT NULL | NULL
|
|
字段
约束
（修
饰词）
|
非空
|
[CONSTRAINT constraint_name]
CHECK (colunmn is not null)
|
[CONSTRAINT constraint_name]
CHECK (colunmn is not null)
|
表约
束
|
非空
|
UNIQUE
[USING INDEX TABLESPACE tablespace]
|
[CONSTRAINT constraint_name]
UNIQUE (column_name [,...])
[USING INDEX TABLESPACE tablespace]
|
表索
引约
束
|
唯一
|
[CONSTRAINT constraint_name]
REFERENCES ref_table [( ref_column )]
[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE action ][ ON UPDATE action ]
|
[CONSTRAINT constraint_name]
FOREIGN KEY ( column_name [,...])
REFERENCES ref_table [( ref_column [,...])]
[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE action ][ ON UPDATE action ]
|
表外
键
|
外键
|
ADD [ COLUMN ]
column type [ column_constraint [...]]
ALTER [ COLUMN ] column SET DEFAULT expression
ALTER [ COLUMN ] column DROP DEFAULT
ALTER [ COLUMN ] column { SET | DROP } NOT NULL
|
ADD table_constraint
DROP CONSTRAINT constraint_name
[ RESTRICT | CASCADE ] CLUSTER ON index_name
|
相关
DDL
|
|
非空约束not null有两种方式实现,一种是修饰词“非空”,如
creata table a(a int not null);
alter table a alter a drop not null;
一种是check，而check又分为字段约束和表约束，如
create table a(a int check(a is not null));
alter table a drop constraint a_a_check;
默认值default 也是通过alter语句的set和drop来进行修改的。
唯一约束unique 可以通过DROP CONSTRAINT 约束名称来删除。
在使用alter table 语句添加新字段的时候只能使用column_constraint，只有单纯添加约束才能使用table_constraint，两种情况下unique约束就存在区别。