Online Test Banks
Score higher
See Online Test Banks
eLearning
Learning anything is easy
Browse Online Courses
Mobile Apps
Learning on the go
Explore Mobile Apps
Dummies Store
Shop for books and more
Start Shopping

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.

CREATE

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

ALTER

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.

DROP

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.

  • Add a Comment
  • Print
  • Share
blog comments powered by Disqus
Advertisement

Inside Dummies.com

Dummies.com Sweepstakes

Win $500. Easy.