SQL: ALTER TABLE

ALTER TABLE (SQL)

The ALTER TABLE (SQL) statement modifies existing tables by:

• Rename a table
• Adding/Modifying/Dropping one or more columns in a table
• Adding or dropping one or more constraints
• Altering the length of a column

and many more ways.

Renaming a Table:
The ALTER TABLE command can be used to rename a table. Only the table owner, a DBA, or a user with the ALTER privilege for that table may execute the command on it.

Example:

The following example renames the table Emp  to Employee.

ALTER TABLE Emp RENAME TO Employee;

SQL: ALTER TABLE ADD Column/DROP Column

Adding a column:
The ALTER TABLE ADD COLUMN command modifies the definition of an existing table and adds new columns. Only the table owner, a DBA, or a user with the ALTER privilege for that table may execute the command on it.

Example 1:

The following example adds the hire_date column with the DATE data type to the Employee table.

ALTER TABLE Employee ADD (hire_date DATE);

Example 2:

The following adds the same hire_date column from the previous example, but adds the NOT NULL keyword to require a value is entered for this column when inserting a new row. Also adds the DEFAULT keyword to insert a default value if no value is entered.  The NOT NULL constraint can only be added if there are no records in table.

ALTER TABLE Employee ADD (hire_date DATE NOT NULL DEFAULT SYSDATE);

Modifying a column:
The ALTER TABLE MODIFY COLUMN command modifies the definition of existing columns in a table. Only the table owner, a DBA, a SYSADM, or a user with the ALTER privilege for that table may execute the command.

The ALTER TABLE statement can be used to modify one or more columns from an existing table. You can modify a column to:

• change its datatype
• to change it storage size
• to rename the column

Example 1:
The following modifies the length of the Phone column in the Employees table by changing the data type from CHAR(15) to CHAR(20). Also adds the NOT NULL keyword which requires a value to be entered for this column, when inserting a new row. The constraint can only be added if there are no records in table or the column has all non null values.

ALTER TABLE Employees MODIFY (Phone CHAR(20) NOT NULL );

Example 2:
This following example will rename column dno to deptno in employee table

ALTER TABLE employee RENAME COLUMN dno TO dept_no;

Dropping a column:
The ALTER TABLE statement can be used to drop one or more columns from an existing table when they are no longer needed.

The SQL standard forces you to issue a separate ALTER TABLE statement if you want to drop several columns, but several of the major DBMS brands allow you to drop multiple columns with a single statement.

You can specify one of two drop rules:

• RESTRICT If any other objects in the database (foreign keys, constraints, and so on) depend on the column to be dropped, the ALTER TABLE statement fails with an error and the column is not dropped.

• CASCADE: If any other objects in the database (foreign keys, constraints, and so on) that depend on the column are also dropped as a cascaded effect of the ALTER TABLE statement. The CASCADE effect can cause quite dramatic changes in the database; therefore, use it with care. It’s usually a better idea to use the RESTRICT mode (explicitly drop the dependent foreign keys and constraints, using the appropriate ALTER or DROP statements) before dropping the column.

Example 1:

The following command drops the birth_date column from the employee table.

ALTER TABLE Employees DROP (birth_date);

Example 2:

The following command drops the birth_date and hire_date columns from the
Employees table.

ALTER TABLE Employees DROP (birth_date, hire_date);

SQL: ALTER TABLE ADD/DROP a foreign key

ADD a foreign key:
A foreign key is a key that corresponds to the primary key or a unique index of another table. This establishes a parent-child relationship between two tables that are represented by common data values. The parent table contains the primary key or unique index, and the child table contains the foreign key.

Example:

The following creates the foreign key fkey1 and specifies the CustNo column as the parent key. The CustNo column can be the primary key of the Accounts table or any other unique index. The primary key or other unique index of the Customers table must be defined before executing this command.

ALTER TABLE Accounts ADD CONSTRAINT fkey1 FOREIGN KEY (CustNo) REFERENCES Customers (CustNo);

DROP a foreign key:
The ALTER TABLE DROP FOREIGN KEY command modifies the definition of an existing table and drops a foreign key that was previously defined. Only the table owner, a DBA, or a user with the ALTER privilege for the table may execute the command.

Example:

The following drops foreign key fkey1 from the Salary table.

ALTER TABLE Salary DROP CONSTRAINT fkeyl;

SQL: ALTER TABLE ADD/DROP Primary Key

The ALTER TABLE PRIMARY KEY command modifies the definition of an existing table and adds/drop a primary key. Only the table owner, a DBA, or a user with both the ALTER and INDEX privileges for the table may execute the command.

A primary key is a key that uniquely identifies each row in a table. Without a primary key, it is impossible to distinguish between specific rows in a table because rows may contain duplicate values. The DBMS will not define a primary key on columns that contain duplicate values, or enter a duplicate value in a primary key that already exists.

Example:

The following example creates a primary key on column CustNo in the Customers table. The CustNo column must be defined with the NOT NULL constraint, and all values in the CustNo column must be unique, or the table must be empty.

ALTER TABLE Customers ADD PRIMARY KEY (CustNo);

The following example creates a primary key on column CustNo in the Customers table. There is always one primary key for a table so you don’t have to mention column name:

ALTER TABLE Customers DROP PRIMARY KEY CASCADE;

• CASCADE: If any other objects in the database (foreign keys, constraints, and so on) that depend on the primary key are also dropped as a cascaded effect of the ALTER TABLE statement. The CASCADE effect can cause quite dramatic changes in the database; therefore, use it with care. It’s usually a better idea to use the RESTRICT mode (explicitly drop the dependent foreign keys and constraints, using the appropriate ALTER or DROP statements) before dropping the column.