To create a table, you use the SQL CREATE TABLE command. The basic format for table creation is:

These are the basic elements of the create table command:

Case does not matter in creating tables. Individual column definitions are separated by commas. There is no comma after the last column definition.

Column names must be unique within the table, can be up to 30 characters in length, and cannot be an ORACLE reserved word. All table and column names must start with a letter of the alphabet but may also include numbers and underscores within their names.

The data types you are most likely to use in ORACLE are NUMBER, VARCHAR2 and DATE. Note that CHAR is also available, but VARCHAR2 is more efficient because it stores only the string entered in the column. CHAR pads strings to fill the space specified by size.

The [not null] is optional, columns can be null or not null. If you do not specify 'NOT NULL' the default is 'NULL' and ORACLE will allow the column to contain a null value.

The primary key of a table consists of one or more attributes that will uniquely identify a row in a table. The attribute(s) of the primary key must be not null. For a single column primary key, you may specify the primary key on the column as illustrated above, creating a column constraint. However, to define a composite primary key, you must create a table constraint as shown below.

Recall the ENROLLMENT table discussed in chapters 9 and 10 of the IS 331 textbook. The primary key is the combination of the attributes StudentNumber and ClassName. This table could be created in ORACLE with the following command.

REFERENCES identifies the column as a foreign key from the table table_name. A foreign key is a column or combination of columns with values based on the primary key of another table. A foreign key constraint, also known as referential integrity or RI, dictates that the value of a foreign key agrees with the actual value of the primary key in the table it references. If the foreign key is composed of more than one attribute, a table constraint must be created as illustrated in the previous command. The following command would create a table with a composite primary key and a composite foreign key.

When creating a table, returning after each column definition is not necessary but does promote readability. When you have finished typing in the last line including the ';' then hit return and the table will be created. A 'table created' message should come back to you. You will be the owner of this table and you are the only person (other than a DBA) who can access the table.

To view the table definition use the SQL*Plus DESCRIBE <table_name> command. If you identified a primary key during table creation, it does not appear in the table description.

A table can be created from an existing table. As long as you have select privileges to a table, you can make an image copy of that table under your own user-id. You can also choose to select only specified columns from an existing table for your new table. To create a new table based on an existing table use the following command:

Issue the describe command for MANAGER.

Look at the records in the MANAGER table.

Notice that this table has inherited the column characteristics from the EMPLOYEE table for the columns specified. All the rows from the EMPLOYEE table where the job title was 'Mngr' were also copied into the new table.

Be aware that creating a table in this manner introduces data duplication into the database and exposes the database to anomalies. For example, if a new manager is added to the EMPLOYEE table and the MANAGER table is not updated at the same time, then the database is inconsistent. A better alternative may be to create a view off the EMPLOYEE table rather than create a new table.

A view is a database object, but it is a virtual table. Each time it is queried it will be built off the base table(s). It has no storage of its own but can be used in much the same manner as a table. Views remain dynamic and always reflect the data from the underlying tables. Whenever data within a table is changed any views created from that table will also change. Through a view you can limit the information specific users see. For instance, we are working with the EMPLOYEE table and wish to limit who sees the salary information. Therefore, we can create a view from the EMPLOYEE table that does not contain the salary information.

FROM EMPLOYEE;

Look at the results table returned when you select all columns from this view.

Last revised 12/4/1997 by J. Warren, Kennesaw State University

Department of Computer Science and Information Systems