The UPDATE, INSERT, and DELETE statements update the contents of a table. With SQL, we can also change the structure of a table, even when that table contains millions of rows. We can add columns, change the data type of an existing column, add integrity constraints, and can even delete entire tables. This chapter describes all the features to drop tables (with the DROP TABLE statement), to rename them (the RENAME statement), and to change them (the ALTER TABLE statement).
Note
In most examples in this book, we assume that each table contains its original contents. If you execute the statements in this chapter with SQL, you change the structure and the contents, of course. Because of this, the results of your statements in the following examples could differ from those in the book. On the Web site of the book, www.r20.nl, you can find information on how the tables can be restored to their original state.
The DROP TABLE statement deletes a table. SQL also removes the descriptions of the table from all relevant catalog tables, along with all integrity constraints, indexes, and privileges that are “linked” to that table. In fact, SQL removes each database object that has no right to exist after the table has been deleted.
<drop table statement> ::= DROP TABLE <table specification> |
Explanation: After this statement has been processed, the table no longer exists. Furthermore, all linked database objects, such as indexes, views, and privileges, have been removed as well.
A table can be removed only if there are no foreign keys pointing to the table—or, in other words, the table cannot be a referenced table. In that case, either the relevant foreign key or the entire referencing table must be removed first.
Portability
Following the SQL2 standard, several SQL products have added an option called CASCADE to the DROP TABLE statement. If this option is used, all tables that are “linked” to this table via foreign keys are removed. Thus, the following statement removes the PLAYERS table, but also, among other things, removes the PENALTIES and TEAMS tables:
DROP TABLE PLAYERS CASCADE
The RENAME TABLE statement gives an existing table a new name.
Rename table statement> ::= RENAME TABLE <table name change> <table name change> ::= <table name> TO <table name> |
Example 18.2. Change the name of the PLAYERS table to TENNIS_PLAYERS.
RENAME TABLE PLAYERS TO TENNIS_PLAYERS
All other database objects that refer to this table are changed accordingly. Assigned privileges do not disappear, foreign keys remain, and views that use this renamed table keep on working.
You can change many aspects of the table structure. SQL supports the ALTER TABLE statement for this. Because this statement offers so many possibilities, we describe its features in several sections. This section describes the possibilities for altering the table itself. The following section discusses the ways to change the specifications of columns. Section 18.6 covers the possibilities of changing integrity constraints. How we should change existing indexes is described in Section 20.5, in Chapter 20, “Using Indexes,” (after we explain how indexes are created).
Portability
Most SQL products support the ALTER TABLE statement, but they differ in what can be done with this statement. For example, some products do not allow data types of existing columns to be changed, others do not allow columns to be deleted, and with others you cannot change the name of a table.
<alter table statement> ::= ALTER TABLE <table specification> <table |
structure change>
<table structure change> ::=
<table change> |
<column change> |
<integrity constraint change> |
<index change>
<table change> ::=
RENAME [ TO | AS ] <table name>
Example 18.3. Change the name of the PLAYERS table to TENNIS_PLAYERS.
ALTER TABLE PLAYERS TO TENNIS_PLAYERS
Explanation: The result of this statement is, of course, equal to that of the RENAME TABLE statement; see Section 18.3. The word TO can be replaced by AS.
If you want to change the character set of existing columns, use the CONVERT feature of the ALTER TABLE statement.
Many properties of columns can be changed with the ALTER TABLE statement.
<alter table statement> ::=
ALTER TABLE <table specification>
<table structure change>
<table structure change> ::=
<table change> |
<column change |
<integrity constraint change> |
<index change>
<column change> ::=
ADD [ COLUMN ] <column definition>
[ FIRST | AFTER <column name> ]
|
Example 18.5. Add a new column called TYPE to the TEAMS table. This column shows whether it is a ladies’ or a men’s team.
ALTER TABLE TEAMS ADD TYPE CHAR(1)
The TEAMS table now looks like this:
TEAMNO PLAYERNO DIVISION TYPE
------ -------- -------- ----
1 6 first ?
2 27 second ?
Explanation: In all rows, the TYPE column is filled with the NULL value. This is the only possible value that SQL can use to fill the column. (How would SQL know whether, for example, team 1 is a men’s team?)
Because you may specify a full column definition, you may also enter a null specification, integrity constraints, and column options.
The word COLUMN may be added but does not change the result. The new column automatically becomes the last column unless the “position” is specified.
Example 18.6. Add a new column called TYPE to the TEAMS table. This column shows whether it is a ladies’ or a men’s team. The column must be placed right behind the TEAMNO column.
ALTER TABLE TEAMS ADD TYPE CHAR(1) AFTER TEAMNO
This TEAMS table now looks like this:
TEAMNO TYPE PLAYERNO DIVISION
------ ---- -------- --------
1 ? 6 first
2 ? 27 second
Explanation. By replacing AFTER TEAMNO with FIRST, the new column will be positioned at the beginning.
With a somewhat different formulation, you can add two or more new columns at one time.
Example 18.7. Add two new columns to the TEAMS table.
ALTER TABLE TEAMS
ADD (CATEGORY VARCHAR(20) NOT NULL,
IMAGO INTEGER DEFAULT 10)
Explanation: The CATEGORY column has been defined as NOT NULL. This means that SQL cannot assign a NULL value to each row for this column. Depending on the data type, SQL fills in an actual value: the value 0 for numeric columns, the empty string for alphanumeric columns, the date 0000-00-00 for date data types, and the time 00:00:00 for time data types.
Explanation: All other database objects that depend on this column, such as privileges, indexes, and views, will also be deleted.
Example 18.9. In the TEAMS table, change the column name BIRTH_DATE to DATE_OF_BIRTH.
ALTER TABLE PLAYERS CHANGE BIRTH_DATE DATE_OF_BIRTH DATE
Explanation. Behind the column name, a new column definition is specified. Because we want to change only the column name, we’ll leave the other specifications the way they are, so they remain equal to those of the original column. But we are allowed to change those as well.
Example 18.10. Increase the length of the TOWN column from 30 to 40.
ALTER TABLE PLAYERS CHANGE TOWN TOWN VARCHAR(40) NOT NULL
The length of a data type may be increased or reduced. In the case of the latter, the existing values are shortened.
Example 18.11. Shorten the length of the TOWN column to five characters.
ALTER TABLE PLAYERS CHANGE TOWN TOWN VARCHAR(5) NOT NULL
Example 18.12. Change the data type of the PLAYERNO column in the PLAYERS table from INTEGER to TINYINT.
ALTER TABLE PLAYERS CHANGE PLAYERNO PLAYERNO TINYINT
When data types are changed, the usual rule is that it must be possible to transform the values in the column into the new data type. So, the previous example will be executed correctly because the current player numbers fit into the TINYINT data type.
Example 18.13. Move the TOWN column to the second position.
ALTER TABLE PLAYERS CHANGE TOWN TOWN VARCHAR(5) NOT NULL AFTER PLAYERNO
Specifications that are not mentioned, such as the comment and the character set, remain unchanged.
ALTER TABLE MODIFY can also be used to change properties of columns. The only thing is that the column name does not have to be mentioned first. That also means that, when using MODIFY, the column name itself cannot be changed.
Example 18.14. Rewrite Example 18.13 with MODIFY.
ALTER TABLE PLAYERS MODIFY TOWN VARCHAR(5) NOT NULL AFTER PLAYERNO
Example 18.15. Assign the default value Member to the POSITION column of the COMMITTEE_MEMBERS table.
ALTER TABLE COMMITTEE_MEMBERS ALTER POSITION SET DEFAULT 'Member'
Example 18.16. Delete the default value of the POSITION column in the COMMITTEE_MEMBERS table.
ALTER TABLE COMMITTEE_MEMBERS ALTER POSITION DROP DEFAULT
In Chapter 16, “Specifying Integrity Constraints,” we extensively discussed all different kinds of integrity constraints that can be added to a table. With the ALTER TABLE statement, constraints can be added or deleted afterward.
<alter table statement> ::=
ALTER TABLE <table specification>
<table structure change>
<table structure change> ::=
<table change> |
<column change> |
<integrity constraint change> |
<index change>
<integrity constraint change> ::=
ADD <table integrity constraint> |
DROP PRIMARY KEY |
DROP CONSTRAINT <constraint name>
<table integrity constraint> ::=
[ CONSTRAINT [ <constraint name> ] ]
{ <primary key> |
<alternate key> |
<foreign key> |
<check integrity constraint> }
<primary key> ::= PRIMARY KEY <column list>
<alternate key> ::= UNIQUE <column list>
<foreign key> ::=
FOREIGN KEY <column list> <referencing
|
The syntax that is needed to add integrity constraints with an ALTER TABLE statement is identical to the syntax for table integrity constraints in the CREATE TABLE statement. We refer to Chapter 16 for this.
There is a special situation that we would like to discuss here. Imagine that there are two tables: T1 and T2. And imagine that both have a foreign key referring to the other table. This is called cross-referential integrity. Cross-referential integrity can cause problems. If T1 is defined and T2 does not yet exist, the foreign key cannot be defined. This problem can be solved by adding one of the two foreign keys later with an ALTER TABLE statement.
Example 18.17. Create the two tables T1 and T2.
CREATE TABLE T1
(A INTEGER NOT NULL PRIMARY KEY,
B INTEGER NOT NULL)
CREATE TABLE T2
(A INTEGER NOT NULL PRIMARY KEY,
B INTEGER NOT NULL CONSTRAINT C1 CHECK (B > 0),
CONSTRAINT FK1 FOREIGN KEY (A) REFERENCES T1 (A))
ALTER TABLE T1
ADD CONSTRAINT FK2 FOREIGN KEY (A) REFERENCES T2 (A)
Explanation: After these three statements, the cross-referential integrity is defined.
To remove integrity constraints, you can use the DROP version of the ALTER TABLE statement. Here are some examples:
Example 18.19. Delete the foreign key called FK2 that refers from the T1 to the T2 table; see the previous example.
ALTER TABLE T1 DROP CONSTRAINT FK2
With DROP CONSTRAINT, you can remove all kinds of integrity constraints, including primary and alternate keys and check integrity constraints as well.
Example 18.20. Delete the check integrity constraint called C1 that is defined on the B column of the T2 table.
ALTER TABLE T2 DROP CONSTRAINT C1
It is easier to delete an integrity constraint later if a name has explicitly been specified because then it is not necessary to find out which name SQL has assigned to it.