oracle:表、列、约束等操作,table,constraint,column

创建表的语法

CREATE [GLOBAL TEMPORARY] TABLE table_name(
  column_name type [CONSTRAINT constraint_def DEFAULT default_exp]
 [,column_name type [CONSTRAINT constraint_def DEFAULT default_exp]…]
)
[ON COMMIT {DELETE| PRESERVE }ROWS]
TABLESPACE table_space;

GLOBAL TEMPORARY 说明该表为临时表。行的有效期有 ON COMMIT 子句决定。临时表对于说有会话都可见，但是这些行则是特定于某个会话的。

constraint_def 指定了对某个列的约束定义，可以用于删除指定的约束或重复使用该约束。

ON COMMIT 控制临时表中行的有效期。DELETE 说明这些行在事务的末尾要被删除。 PRESERVE说明这些行在会话的末尾要被删除。如果没有指定ON COMMIT ，默认为 DELETE

table_space 为该表指定表空间，如果没有指定，该表会被存储在该用户的默认表空间中。

获得有关表的信息

可以直接DESCRIBE

DESC[RIBE] table_name;

可以通过数据字典

SELECT * 
FROM user_tables
WHERE table_name =xxxx;
[or  WHERE table_name IN ('xxxx','xxxx')]

重命名表

RENAME table_old_name TO table_new_name;

截断表

删除表中的所有行，并重置表的存储空间，表的表名列属性等会保留。 

TRUNCATE table_name; 

删除表

DROP TABLE table_name;

 向表、列添加注释

COMMENT ON TABLE table_name IS 'xxxxx';

COMMENT ON COLUMN table_name.column_name IS 'xxxx';

获得表、列的信息

SELECT * 
FROM user_tab_comments
WHERE table_name='xxx';

SELECT *
FROM user_col_comments
WHERE table_name='xxx'; 

获得表中的列的信息

SELECT *
FROM user_tab_columns
WHERE table_name='xxxx';

 添加列

ALTER TABLE table_name
ADD column_name type;

重命名列

ALTER TABLE table_name
RENAME COLUMN column_old_name TO column_new_name; 

修改列 

ALTER TABLE table_name
MODIFY column_name type;

如果一个表中没有任何行或列为空值，可以将表列修改为任意一种数据类型，tpye 为数据类型。

修改列的默认值 

ALTER TABLE table_name
MODIFY column_name DEFAULT xxxx;

 删除列

ALTER TABLE table_name
DROP COLUMN column_name;

对列添加约束 

 添加CHECK 约束

ALTER TABLE table_name
ADD CONSTRAINT constraint_name
CHECK ( column_name IN ('xxx','xxxx'));
[or CHECK ( column_name >0)]

 添加一个约束，表中现有的行一定要符合这个约束条件。

添加NOT NULL 约束

CREATE TABLE table_name
MODIFY column_name [CONSTRAINT constraint_name] NOT NULL;

 NOT NULL 是用MODIFY 添加的， 上述[]是为这个约束添加一个名字。

添加FOREIGN KEY约束

ALTER TABLE table_name
DROP COLUMN column_name;

ALTER TABLE table_name
ADD [CONSTRAINT constraint_name]
column_name REFERENCES table_name1(column_name1) [ON DELETE {CASCADE | SET NULL} ];

通常需要先删除该列然后通过添加列时吧FOREIGN KEY 一起添加。 

 ON DELETE CASCADE 为级联删除。

ON DELETE SET NULL 为外键被删除时该位置设 NULL。

 添加UNIQUE 约束

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE(column_name);

删除约束

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

禁用约束

默认情况下，约束在创建是启动，可以再创建时禁用约束。

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE(column_name) DISABLE;

启动约束、禁用约束

ALTER TABLE table_name
{ENABLE|DISABLE } CONSTRAINT constraint_name;

启动约束时表中数据必须满足约束条件，通过制定 ENABLE NOVALIDATE 可以选择只对新数据应用某个约束。

ALTER TABLE table_name
ENABLE NOVALIDATE CONSTRAINT  constraint_name;

约束默认情况下是 ENABLE VALIADTE;

延时约束

在事务被提交是强制执行的约束。在添加约束是指定 DEFERRABLE ,约束添加后不能修改，只能通过删除重新添加。有两种标识，

INITIALLY INNEDIATE :每次向表中添加数据、修改表的数据或从表中删除数据是都检测则个约束。

INITALLY DEFERRED :只在有事务被提交是，才检测这个约束。

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE(column_name) DEFERRABLE INITIALLY DEFERRED;

获得有关约束的信息

SELECT *
FROM user_constraints
WHERE table_name = 'xxx';

可以通过表 all_constraints 获得所有可以访问的约束信息。

获得有关列的约束的信息

SELECT *
 FROM user_con_columns
WHERE table_name ='xxx';