oracle 创建表、修改表、添加约束

创建表

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;

例：

CREATE GLOBAL TEMPORARY TABLE order_status_temp (

id INTEGER,

status VARCHAR2(10),

last_modified DATE DEFAULT SYSDATE

)

ON COMMIT PRESERVE ROWS

获得有关表的信息

对表执行DESCRIBE命令。

SELECT * FROM user_tables(all_tables)

获得表中列的信息

SELECT * column_name,data_type,data_length,data_precision,data_scale FROM user_tab_columns(all_tab_columns)

修改表

添加列

ALTER TABLE order_status2

ADD modified_by INTEGER

例： ALTER TABLE order_status2

ADD  initially_created DATE DEFAULT SYSDATE NOT NULL

添加虚拟列  只引用表中已有的其他列

ALTER TABLE salary_grades

ADD (average_salary AS ((low_salary + high_salary) / 2));

修改列的长度（只有在表中还没有任何行或所有列都为空值时才可以减小列的长度）

ALTER TABLE order_status

MODIFY status VARCHAR2(15)

修改数字列的精度（只有在表中还没有任何行或列为空值时才可以降低数字列的精度）

ALTER TABLE order_status2

MODIFY id NUMBER(5);

修改列的数据类型（如果表中还没有任何行或列为空值，就可以将列修改为任何一种数据类型。否则就只能将列的数据类型修改为一种兼容的数据类型。）

ALTER TABLE order_status2

MODIFY status CHAR(15);

修改列的默认值（默认值只适用于新插入表中的行。）

ALTER TABLE  order_status2

MODIFY last_modifyd DEFAULT SYSDATE - 1;

删除列

ALTER TABLE order_status2

DROP COLUMN initially_created;

添加约束

CHECK约束： 指定一列或一组列的值必须满足某种条件。

ALTER TABLE order_status

ADD CONSTRAINT order_status2_status_ck

CHECK (status IN ('PLACED','PENDING','SHIPPED'))

要添加一个约束，表中现有的所有行都必须满足这个约束条件。（有一个例外：在最初添加约束时可以禁用约束，并可以通过指定ENABLE NOVALIDATE选项来让约束只适用于新添加的数据。）

NOT NULL 约束

ALTER TABLE  order_status2

MODIFY status CONSTRAINT order_status_nn NOT NULL;

FOREIGN KEY约束

ALTER TABLE order

ADD CONSTRAINT order_modified_by_fk

modified_by REFERENCES employees(employee_id) ON DELETE CASCADE

ON DELETE CASCADE 指定在父表中删除一行记录时，子表中匹配的所有行也都将被删除。所以当employees表中删除一行记录时，order中所有匹配的行也都将被删除。

ON DELETE SET NULL 指定在父表删除一行记录时，order表中所匹配的行也都将被删除。

UNIQUE约束  任何现有的行或新行始终必须有一个唯一的status列值

ALTER TABLE order

ADD CONSTRAINT order_status_unique UNIQUE (status);

删除约束

ALTER TABLE order

DROP CONSTRAINT order_stasus_unique

禁用约束

添加约束但是禁用这个约束。

ALTER TABLE order

ADD CONSTRAINT order_status_unique UNIQUE (status) DISABLE  

DISABLE CONSTRAINT子句禁用现有约束

ALTER TABLE order

DISABLE CONSTRAINT order_status_unique (末尾添加CASCADE可以禁用依赖于指定约束的任何约束。在禁用主键或作为外键约束的一部分的唯一性约束，必须使用CASCADE.)

启用约束

ALTER TABLE order 

ENABLE CONSTRAINT order_status_unique;

要启用约束，表中的所有行必须都能满足约束条件。

通过指定ENABLE NOVALIDATE 可以选择只对新数据应用某个约束。约束默认是ENABLE VALIDATE,意味着现有的行必须经过约束检查。

延迟约束deferred constraint

事务被提交时强制执行的约束。在最初添加约束时可以使用DEFERRABLE指定约束是延迟约束。

约束一旦添加后，就不能再修改为DEFERRABLE，只能将其删除，再重新创建这个约束。

ALTER TABLE order

ADD CONSTRAINT order_status_unique (status)

DEFERRABLE INITIALLY DEFERRED;

如果向order表中添加行，那么只有在执行commit命令时，才会对order_status_unique约束进行检查。

INITIALLY DEFERRED的意思是只有在事务被提交时，才会检查这个约束。

INITIALLY IMMEDIATE的意思是每次向表中添加数据、修改表的数据或从表中删除数据都要检查这个约束（与约束默认行为相同）。

获得有关约束的信息

通过查询user_constraints视图可以获得有关约束的信息。(all_constraints)

SELECT * FROM user_constraints WHERE table_name = 'table name'

获得有关列的约束的信息

查询user_cons_columns视图（all_cons_columns）

重命名表

RENAME order TO order_change;

如果已经在约束名中使用了表名，则应该修改约束名。

向表中添加注释使用COMMENT语句可以为表或列添加注释

为表添加注释：

COMMENT ON TABLE order IS

'order stores the state of an order';

为列添加注释：

COMMENT ON COLUMN order.last_modified IS

’last_modified stores the date and time the order was modified last'

1.获得表的注释

通过user_tab_comments视图可以获得有关表的注释。

SELECT * 

FROM user_tab_comments

WHERE table_name = 'ORDER_STATUS'

2.获得列的注释 user_col_comments

截断表

TRUNCATE删除表中所有的行，并重置表的存储空间

TRUNCATE TABLE order

删除表

DROP TABLE order