Section D.3. Schema Manipulation Commands


D.3. Schema Manipulation Commands

SQL includes two broad classes of commands. The first class is schema manipulation commands , which allow the creation, modification, and deletion of high-level database objects such as tables. This section describes these commands. We've provided a syntax summary for each command. In case you aren't familiar with the style, items in square brackets are optional or not always required, while items in curly braces are either always required or required within the context of some optional item. A vertical bar (|) indicates a choice, while an ellipsis indicates that an entry may be repeated any number of times. Items in uppercase constant width are part of the SQL statement, while items in lowercase constant width italic represent names and values that you supply when using the statement.

D.3.1. CREATE TABLE

As its name says, the CREATE TABLE command creates a table. Here's the syntax:

 CREATE [ [ GLOBAL | LOCAL ] TEMPORARY ] TABLE table_name  ( { column_name { data_type | domain_name } [ column_size ]       [ column_constraint... ] ... }     [ DEFAULT default_value], ...     [ table_constraint ], ...     [ ON COMMIT { DELETE | PRESERVE } ROWS ] ) 

Here's a simple example:

 CREATE TABLE BOOKS  (    TITLE VARCHAR (25) PRIMARY KEY,    AUTHOR VARCHAR(25) NOT NULL DEFAULT 'Unknown',    EDITION INTEGER,    PRICE NUMBER(6,2)  ) 

The PRIMARY KEY and NOT NULL identifiers are column constraints. The NOT NULL constraint prevents any entry in a column from being set to null. Here, it's combined it with a default value. PRIMARY KEY identifies the column that's used as the primary key (or main unique identifier) for the table. If a table has a primary key column (it doesn't have to), there can be only one such column; no row has a null value in the primary key column, and no two rows have the same primary key.

A table constraint affects every row in the table. UNIQUE is a common example:

 CREATE TABLE BOOKS  (    TITLE VARCHAR (25),    AUTHOR VARCHAR(25),    EDITION INTEGER,    PRICE NUMBER(6,2),    UNIQUE  ) 

Used as a table constraint, UNIQUE indicates that each row in the table must have a unique combination of values. You can also specify particular columns that must form a unique combination:

 UNIQUE(TITLE, AUTHOR, EDITION) 

This mandates only unique title/author/edition combinations. Note that UNIQUE can also be used as a column constraint.

We can use PRIMARY KEY as a table constraint to specify more than one column as the primary key:

 CREATE TABLE BOOKS  (    TITLE VARCHAR (25) NOT NULL,    AUTHOR VARCHAR(25) NOT NULL,    EDITION INTEGER NOT NULL,    PRICE NUMBER(6,2),    PRIMARY KEY (TITLE, AUTHOR, EDITION)  ) 

Since entry-level SQL-92 requires that primary keys remain not null, we use NOT NULL column constraints on the primary key columns in this case.

D.3.2. ALTER TABLE

The ALTER TABLE command allows you to modify the structure of an existing table. Here's the syntax:

 ALTER TABLE table_name    { ADD [COLUMN] column_name definition }    { ALTER [COLUMN] column_name definition       { SET DEFAULT default_value } | { DROP DEFAULT } }    { DROP [COLUMN] COLUMN_NAME [ RESTRICT | CASCADE ] }    { ADD table_constraint_definition}    { DROP constraint_name [ RESTRICT | CASCADE] } 

Note that the modifications you can make are somewhat limited. While you can add and remove columns (subject to the requirements of any constraints that may have been placed on the table), you can't reorder columns. To perform major changes, you generally need to create a new table and move the existing data from the old table to the new table.

Here's a statement that adds two columns to a table:

 ALTER TABLE BOOKS   ADD PUBLISHED_DATE DATE,   ADD PUBLISHER CHAR (30) NOT NULL 

Note that the ability to specify multiple operations in an ALTER TABLE command is not part of the SQL specification, although most databases support this functionality.

Here's how to change the type of a column:[3]

[3] If you look back at the syntax for ALTER TABLE, you'll see that the official syntax for this kind of operation is ALTER, although most databases use MODIFY instead.

 ALTER TABLE BOOKS   MODIFY PUBLISHER VARCHAR (25) 

When this statement runs, the database attempts to convert all existing data into the new format. If this is impossible, the modification fails. In the previous example, if any record has a publisher entry of more than 30 characters, the statement might fail (exact behavior depends on the implementation). If you are converting from a character field to, say, an integer field, the whole ALTER TABLE command might fail entirely. At the minimum, such a change requires that all entries contain a valid string representation of an integer.

To allow null values in the PUBLISHER column, use MODIFY:

 ALTER TABLE BOOKS   MODIFY PUBLISHER NULL 

To remove the PUBLISHER column entirely, use DROP:

 ALTER TABLE BOOKS   DROP PUBLISHER 

The ALTER TABLE command is not required for entry-level SQL-92 conformance. Due to its extreme usefulness, however, it is supported by most DBMS packages, although it often varies from the standard. More esoteric features, such as the RENAME command, aren't supported by most packages. In general, it is not safe to count on anything beyond the basic ADD, DROP, and MODIFY (ALTER) commands.

D.3.3. DROP

The DROP command allows you to permanently delete an object within the database. For example, to drop the BOOKS table, execute this statement:

 DROP TABLE BOOKS 

DROP also can delete other database objects, such as indexes, views, and domains:

 DROP INDEX index_name DROP VIEW view_name DROP DOMAIN domain_name 

Once something has been dropped, it is usually gone for goodcertainly once the current transaction has been committed, but often before.



Java Enterprise in a Nutshell
Java Enterprise in a Nutshell (In a Nutshell (OReilly))
ISBN: 0596101422
EAN: 2147483647
Year: 2004
Pages: 269

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net