Shulou(Shulou.com)06/01 Report--

Use the ALTER TABLE statement:

Add field

Modify field

Modify field default values

Delete a field

1. Add a field, and the new field will become the last column:

Alter table table

Add (column datatype [default expr]

[, column datatype]...)

Alter table dept80 add (job_id varchar2 (9))

2. Modify the data type, size and default value of the field (the modified default value only affects the data inserted into the table after modification)

Alter table table

Modify (column datatype [default expr]

[, column datatype]...)

Alter table dept80 modify (last_name varchar2 (30))

3. Delete a field

Alter table table drop (column [, column]..)

Alter table dept80 drop column job_id

SET UNUSED option

You can use the SET UNUSED option to mark one or more fields as unavailable.

You can use DROP UNUSED COLUMNS to delete fields that are marked as unavailable.

Alter table

Set unused ([,])

Or

Alter table

Set unused column [,]

Alter table drop unused columns

Management constraint

Add constraint syntax

Use the ALTER TABLE statement:

Add or remove constraints without changing their structure

Enable and disable constraints

Add NOT NULL constraints by using the MODIFY clause

Syntax:

Alter table

Add [constraint]

Type ()

Add constraint

Add a FOREIGN KEY constraint to the EMP2 table indicating that a manager must already exist as a valid employee in the EMP2 table. Modify table EMP2 to add FOREIGN KEY constraint, provided that the parent node

Alter table emp2 modify employee_id primary key

Alter table emp2

Add constraint emp_mgr_fk

Foreign key (manager_id)

References emp2 (employee_id)

ON DELETE statement

Use the ON DELETE CASCADE clause to cascade delete:

Alter table emp2 add constraint emp_dt_fk

Foreign key (department_id)

References departments (department_id) on delete cascade

Using ON DELETE SET NULL when the parent node record is deleted, the child node

Record is empty:

Alter table emp2 add constraint emp_dt_fk

Foreign key (department_id)

References departments (department_id) on delete set null

Delay constraint

Delay constraints have the following properties:

Deferrable or not deferrable

Initially deferred or initially immediate

Alter table dept2 add constraint dept2_id_pk primary key (department_id)

Deferrable initially deferred

Set constraint dept2_id_pk immediate

Alter session set constraints=immediate

The difference between INITIALLY DEFERRED and INITIALLY IMMEDIATE

Verify immediately after the end of the INITIALLY DEFERRED transaction when the INITIALLY IMMEDIATE statement is executed

Create table emp_new_sal (salary number

Constraint sal_ck

Check (salary > 100)

Deferrable initially immediate

Bonus number

Constraint bonus_ck

Check (bonus > 0)

Deferrable initially deferred)

Delete constraint

Delete the emp_mgr_fk constraint for table EMP2:

Alter table emp2 drop constraint emp_mgr_fk

Delete the PRIMARY KEY constraint on the DEPT2 table and delete the associated FOREIGN KEY constraint:

Alter table dept2 drop primary key cascade

Disable constraint

Disable integrity constraints using the DISABLE clause of the ALTER TABLE statement

Apply the CASCADE option to disable related integrity constraints

Alter table emp2 disable constraint emp_dt_fk

Enable constraint

Use the ENABLE clause to activate the integrity constraints defined in the current disabled table.

Alter table emp2 enable constraint emp_dt_fk

If a unique key or primary key constraint is enabled, a unique index is automatically created

Cascade constraint

The CASCADE CONSTRAINTS clause is used in the DROP COLUMN clause.

Deleting the field CASCADE CONSTRAINTS specified clause also removes all defined multi-column constraints.

If you specify the CASCADE CONSTRAINTS clause when you delete the PRIMARY and UNIQUE constraints defined on a column, all integrity constraints that reference it are deleted

Example:

Alter table emp2 drop column employee_id cascade constraints

Alter table test1 drop (col1_pk, col2_fk, col1) cascade constraints

Cascade summary

Cascading deleted objects when deleting a user

Drop user test cascade

Cascading delete constraints when deleting tables

Drop table test cascade constraints

Cascading delete constraints (pk,fk,.c) when deleting fields

Alter table test drop column (id) cascade constraints

Alter table test disable constraint pk_test cascade

Create table child (C1 number primary key,c2 number references parent (C1) on delete cascade)

Columns and constraints of renamed tables

1. Rename the table column using the ALTER TABLE statement of the RENAME COLUMN clause.

Alter table marketing rename column team_id to id

2. Use the ALTER TABLE statement of the RENAME CONSTRAINT clause to rename any existing constraints in the table.

Alter table marketing rename constraint mktg_pk to new_mktg_pk

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.