Creating a Table

To create a table, use the CREATE TABLE statement.

CREATE TABLE hotel.person

(pno FIXED(6),

name CHAR(20),

 city CHAR(20))

You specify the name of the table (person) after the keywords CREATE TABLE. Create the person table in the hotel schema.

In the list of column definitions, you have to enter the name and data type for each column. The list of column definitions is in parentheses.

See also: SQL Reference Manual, CREATE TABLE Statement (create_table_statement), Column Name (column_name), Data Type (data_type)

Data Integrity

The person  table is a very simple table. The only input restriction for values in the columns in the person table is that the data types are predefined. In reality, additional entries, restrictions, and objects, such as constraints, primary keys, domains, database procedures and database triggers are usually required to ensure data integrity.

See also: SQL Reference Manual, Ensuring Data Integrity

Changing a Table

You can use the ALTER TABLE statement to change the table definition while the database is in operation.

ALTER TABLE hotel.person  

ADD (code FIXED(5), fon FIXED(8))

Columns for the telephone dialing code and number are added to the person table.

See also: SQL Reference Manual, ADD Definition (add_definition)

ALTER TABLE hotel.person  

DROP (code)

The column for the telephone dialing code is deleted again from the customer table.

See also: SQL Reference Manual, DROP Definition (drop_definition)

ALTER TABLE hotel.person  

MODIFY fon FIXED(10)

The column definition is changed in the customer table. The column is redefined only if it corresponds to the values already stored.

See also: SQL Reference Manual, MODIFY Definition (modify_definition)

Additional changes can be made to existing tables.

See also: SQL Reference Manual, ALTER TABLE Statement (alter_table_statement)

Renaming a Table

To rename a table, use the RENAME TABLE statement.

RENAME TABLE hotel.person TO member

Rename the person  table to member

See also: SQL Reference Manual, RENAME TABLE Statement (rename_table_statement)

Deleting a Table

To delete a table, use the DROP TABLE statement.

DROP TABLE hotel.member

You can use this SQL statement to delete the table definition, the table contents, and all dependent objects (such as view tables and indexes). The table can be deleted by the table owner only.

See also: SQL Reference Manual, DROP TABLE Statement (drop_table_statement)