Page 99 - PowerPoint Presentation

CAVITE STATE UNIVERSITY
T3 CAMPUS
Department of Information Technology DCIT 55 – Advance Database System

- You cannot delete a column that has PRIMARY KEY or FOREIN KEY constraints or other
dependencies except when using the Table Designer.

SQL ALTER TABLE (MODIFY Column)
- We can modify the data type as well as the size of a column using ALTER TABLE
statement with ALTER option.
- Modifying the data type of a column that already contains data can allow us to modify if
data present in that column is compatible to new datatype.
- Decreasing the size of a column that already contains data can allow us to decrease if
data present in that column is less than the new size.

SQL RENAME Statement (For TABLE)
The SQL RENAME statement is used to rename a table.
Syntax: ALTER TABLE table_name RENAME TO new_table_name;

Example: Consider the ‘Persons’ table having the following records.
ID Name Age
1 Christian 22
2 John Mark 22
3 Jayson 23
4 Nigel 22
The following SQL Statement rename a table called ‘Persons’ to the new name ‘Candidates’.
ALTER TABLE Persons RENAME TO Candidates;

SQL RENAME Statement (For COLUMN)
The SQL RENAME statements is used to rename a table’s name.
Syntax: ALTER TABLE table_name RENAME COLUMN column_name TO
new_column_name;

Example: Consider the ‘Persons’ table having the following records:
ID Name Age
1 Christian 22
2 John Mark 22
3 Jayson 23
4 Nigel 22
The following SQL Statement renames a column ‘Name’ to ‘PersonName’.
ALTER TABLE Persons RENAME COLUMN Name TO PersonName;

Now, the ‘Persons’ table would become as below:
ID PersonName Age
1 Christian 22
2 John Mark 22
3 Jayson 23
4 Nigel 22

SQL DROP Statement
The SQL DROP statement is used to drop an existing table from a database.
Be careful before dropping/deleting a table. Dropping/Deleting a table results in loss of all
information stored in the table.
Syntax: DROP TABLE table_name;

Consider the ‘Persons’ table having the following records:

Page | 15