How to Use SQL’s DDL statements
SQL’s Data Definition Language (DDL) deals with the structure of a database. It’s distinct from the Data Manipulation Language, which deals with the data contained within that structure. The DDL consists of these three statements:
CREATE: You use the various forms of this statement to build the essential structures of the database.
ALTER: You use this statement to change structures that you have created.
DROP: You apply this statement to structures created with the CREATE statement, to destroy them.
You can apply the SQL CREATE statement to a large number of SQL objects, including schemas, domains, tables, and views. By using the CREATE SCHEMA statement, you can not only create a schema, but also identify its owner and specify a default character set. Here’s an example of such a statement:
CREATE SCHEMA SALES AUTHORIZATION SALES_MGR DEFAULT CHARACTER SET ASCII_FULL ;
Use the CREATE DOMAIN statement to apply constraints to column values. The constraints you apply to a domain determine what objects the domain can and cannot contain. You can create domains after you establish a schema. The following example shows how to use this statement:
CREATE DOMAIN Age AS INTEGER CHECK (AGE > 20) ;
You create tables by using the CREATE TABLE statement, and you create views by using the CREATE VIEW statement. When you use the CREATE TABLE statement, you can specify constraints on the new table’s columns at the same time.
Sometimes you may want to specify constraints that don’t specifically attach to a table but apply to an entire schema. You can use the CREATE ASSERTION statement to specify such constraints.
You also have CREATE CHARACTER SET,CREATE COLLATION, and CREATE TRANSLATION statements, which give you the flexibility of creating new character sets, collation sequences, or translation tables. (Collation sequences define the order in which you carry out comparisons or sorts. Translation tables control the conversion of character strings from one character set to another.)
After you create a table, you’re not necessarily stuck with that exact table forever. As you use the table, you may discover that it’s not everything you need it to be. You can use the ALTER TABLE statement to change the table by adding, changing, or deleting a column in the table. Besides tables, you can also ALTER columns and domains.
Removing a table from a database schema is easy. Just use a DROP TABLE<tablename> statement. You erase all data from the table, as well as the metadata that defines the table in the data dictionary. It’s almost as if the table never existed. You can also use the DROP statement to get rid of anything that was created by a CREATE statement.
DROP won’t work if it breaks referential integrity.