SQL Base Management Scenario Object (17)

Use the ALTER TABLE statement:

• Add Field

• modifying fields

• Modify field default values

• Delete a field

1. Add a field and the new field will be 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 affects only the data inserted in the table after the modification)

ALTER TABLE Table

Modify (column datatype [default expr]

[, column datatype] ...);

ALTER TABLE DEPT80 Modify (last_name varchar2 (30));

3. Delete fields

ALTER TABLE Table Drop (column [, column]:);

ALTER TABLE dept80 drop column job_id;

SET UNUSED Options

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

• You can use the drop UNUSED COLUMNS to remove fields that are marked as unavailable.

ALTER TABLE <table_name>

Set unused (<column_name> [, <column_name>]);

Or

ALTER TABLE <table_name>

Set unused column <column_name> [, <column_name>];

ALTER TABLE <table_name> drop unused columns;

Administrative constraints

Add constraint syntax

Use the ALTER TABLE statement:

• Add or remove a constraint without modifying its structure

• Enabling and disabling constraints

• To add a not NULL constraint by using the MODIFY clause

Grammar:

ALTER TABLE <table_name>

add [constraint <constraint_name>]

Type (<column_name>);

Add constraint

ADD a FOREIGN KEY constraint to the EMP2 table indicating, a manager must already exist as a valid employee in the EMP 2 table. Modify Tables EMP2 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

Cascade Delete using the ON delete CASCADE clause:

ALTER TABLE EMP2 add constraint EMP_DT_FK

Foreign KEY (DEPARTMENT_ID)

References departments (department_id) on DELETE cascade;

Use on delete SET NULL when parent node record is deleted child node

Record blank:

ALTER TABLE EMP2 add constraint EMP_DT_FK

Foreign KEY (DEPARTMENT_ID)

References departments (department_id) on delete set null;

Delay constraint

The delay constraint has 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

Initially DEFERRED	Validation after transaction end
Initially IMMEDIATE	Validate statement as soon as it executes

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

Remove the EMP_MGR_FK constraint for table EMP2:

ALTER TABLE EMP2 drop constraint emp_mgr_fk;

Remove the PRIMARY key constraint for the DEPT2 table and remove the associated FOREIGN KEY constraint:

ALTER TABLE DEPT2 drop PRIMARY key cascade;

disabling constraints

• Disabling 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 constraints

Use the Enable clause to activate the integrity constraints defined in the currently disabled table.

ALTER TABLE EMP2 enable constraint EMP_DT_FK;

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

cascading constraints

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

• Deleting a field CASCADE CONSTRAINTS the specified clause and also deletes all defined multicolumn constraints.

• When you specify a cascade CONSTRAINTS clause when you delete the PRIMARY and UNIQUE constraints defined on a column, all integrity constraints that reference it are removed

Example:

ALTER TABLE EMP2 DROP column employee_id cascade constraints;

ALTER TABLE test1 drop (COL1_PK, COL2_FK, col1) cascade constraints;

Cascade Summary

• Cascade Delete Objects when users are deleted

Drop user test cascade;

• Cascade Delete constraint when table is deleted

DROP TABLE test cascade constraints;

• When deleting a field, cascade delete constraint (PK,FK,.C)

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);

Renaming a table's columns and constraints

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;

This article is from the "record a bit of learning life" blog, please make sure to keep this source http://ureysky.blog.51cto.com/2893832/1910400

SQL Base Management Scenario Object (17)