Oracle table, column, and constraint operations

Get information about a table

can be directly describe

DESC[RIBE] table_name;

You can use the data dictionary

SELECT * FROM User_tableswhere table_name =xxxx; [or  WHERE table_name in (' xxxx ', ' xxxx ')]

Renaming a table

RENAME Table_old_name to Table_new_name;

TRUNCATE TABLE

Deletes all rows in the table, resets the table's storage space, and preserves the table column name properties.  

TRUNCATE table_name;  

Delete a table

DROP TABLE table_name;

Add comments to tables and columns

COMMENT on TABLE table_name is ' xxxxx '; COMMENT on COLUMN table_name.column_name is ' xxxx ';

Get the table, column information

SELECT * from user_tab_commentswhere table_name= ' xxx '; SELECT *from user_col_commentswhere table_name= ' xxx ';  

Get information about columns in a table

SELECT *from user_tab_columnswhere table_name= ' xxxx ';

Adding columns

ALTER TABLE table_nameadd column_name type;

renaming columns

ALTER TABLE table_namerename COLUMN column_old_name to Column_new_name;  

Modifying Columns

ALTER TABLE table_namemodify column_name type;

If there are no rows or columns in a table with null values, you can modify the table column to any data type, Tpye to data type.

to modify the default values for a column

ALTER TABLE table_namemodify column_name DEFAULT xxxx;

Delete Column

ALTER TABLE table_namedrop COLUMN column_name;

to add a constraint to a column

Add a CHECK Constraint

ALTER TABLE table_nameadd CONSTRAINT Constraint_namecheck (column_name in (' xxx ', ' xxxx ')); [or CHECK (column_name >0)]

Add a constraint in which the existing rows in the table must conform to this constraint.

Add not NULL constraint

CREATE TABLE table_namemodify column_name [CONSTRAINT constraint_name] not NULL;

Not NULL is added with modify, and the above [] is to add a name to the constraint.

Add FOREIGN KEY constraint

ALTER TABLE table_namedrop COLUMN column_name; ALTER TABLE Table_nameadd [CONSTRAINT constraint_name]column_name REFERENCES table_name1 (column_name1) [on DELETE { CASCADE | SET NULL}];

It is usually necessary to delete the column first and then add it together by adding the column foreign KEY.

On Delete CASCADE is a cascade delete.

On delete Set NULL when the foreign key is deleted.

Add a UNIQUE Constraint

ALTER TABLE table_nameadd CONSTRAINT constraint_name UNIQUE (column_name);

Delete Constraint

ALTER TABLE table_namedrop CONSTRAINT constraint_name;

disabling constraints

By default, constraints are disabled when create is started and can be created again.

ALTER TABLE table_nameadd CONSTRAINT constraint_name UNIQUE (column_name) DISABLE;

Start a constraint, disable a constraint

ALTER TABLE table_name{enable| DISABLE} CONSTRAINT constraint_name;

When you start a constraint, the data in the table must meet the constraints, and by making the Enable Novalidate, you can choose to apply only one constraint to the new data.

ALTER TABLE table_nameenable novalidate CONSTRAINT  constraint_name;

The constraint is ENABLE valiadte by default;

Delay constraint

A constraint that is enforced when a transaction is committed. When you add a constraint that is specified deferrable, the constraint cannot be modified after it is added and can only be added again by deleting it. There are two types of identification,

Initially innediate: Every time you add data to a table, modify a table's data, or delete data from a table, it detects a constraint.

initally DEFERRED: This constraint is detected only if a transaction is committed.

ALTER TABLE table_nameadd CONSTRAINT constraint_name UNIQUE (column_name) deferrable initially DEFERRED;

Get information about constraints

SELECT *from user_constraintswhere table_name = ' xxx ';

All accessible constraint information can be obtained through the table all_constraints.

Get information about the constraints on a column

SELECT * FROM User_con_columnswhere table_name = ' xxx ';

Oracle table, column, and constraint operations