Tables

In this section, you ll learn more about creating a table. You ll see how to modify and drop a table. You ll also learn how to get information about tables from the data dictionary, which contains information about the database itself.

Creating a Table

As you know from Chapter 1, you use the CREATE TABLE statement to create a table. The simplified syntax for the CREATE TABLE statement is as follows :

 CREATE [GLOBAL TEMPORARY] TABLE  table_name  (  column_name type  [CONSTRAINT  constraint_def  DEFAULT  default_exp  ]   [,  column_name type  [CONSTRAINT  constraint_def  DEFAULT  default_exp  ]...] ) [ON COMMIT {DELETE  PRESERVE} ROWS] TABLESPACE  tab_space;  

where

• GLOBAL TEMPORARY specifies that the table s rows are temporary and such tables are known as temporary tables. The duration of the contents are specified by the ON COMMIT clause. A temporary table is visible to all sessions, but rows are specific to a session.

• table_name specifies the name you assign to the table.

• column_name specifies the name you assign to a column.

• type specifies the type of a column.

• constraint_def specifies the definition of a constraint on a column.

• default_exp specifies the expression used to assign a default value to a column.

• ON COMMIT controls the duration of the rows in a temporary table. DELETE specifies the rows are deleted at the end of a transaction. PRESERVE specifies the rows are deleted at the end of a session. If you omit ON COMMIT for a temporary table, the default is DELETE.

• tab_space specifies the tablespace for the table. If you don t provide a tablespace, the table is stored in the user s default tablespace.

  Note  

  The full CREATE TABLE syntax is far richer than that shown above. For full details, see the SQL Reference manual from Oracle Corporation.

The following example connects as store and creates a table named order_status2:

 CONNECT store/store_password CREATE TABLE order_status2 (   id INTEGER     CONSTRAINT order_status2_pk PRIMARY KEY,   status VARCHAR2(10),   last_modified DATE DEFAULT SYSDATE ); 

Note  

If you want to follow along with the examples in this chapter, you ll need to enter and run the SQL statements using SQL*Plus.

The next example creates a temporary table named order_status_temp whose rows will be deleted at the end of a session by specifying ON COMMIT PRESERVE ROWS:

 CREATE GLOBAL TEMPORARY TABLE order_status_temp (   id INTEGER,   status VARCHAR2(10),   last_modified DATE DEFAULT SYSDATE ) ON COMMIT PRESERVE ROWS; 

The next example performs the following:

• Adds a row to order_status_temp.

• Disconnects from the database to end the session, which causes the row in order_status_temp to be deleted.

• Reconnects as store and queries order_status_temp to show there are no rows in this table.

    INSERT INTO order_status_temp (   id, status   ) VALUES (   1, 'New'   );  1 row created.  DISCONNECT   CONNECT store/store_password   SELECT *   FROM order_status_temp;  no rows selected 

Getting Information on Tables

You can get information about your tables by:

• Performing a DESCRIBE command on the table. You ve already seen examples that use the DESCRIBE command in earlier chapters.

• Querying user_tables, which forms part of the data dictionary.

Table 10-1 describes some of the columns in user_tables.

Table 10-1: Some Columns in user_tables

Column	Type	Description
table_name	VARCHAR2(30)	Name of the table.
tablespace_name	VARCHAR2(30)	Name of the tablespace in where the table is stored. A tablespace is an area used by the database to store objects such as tables.
temporary	VARCHAR2(1)	Whether the table is temporary. Set to Y if temporary or N if not temporary.

Note  

You can get information on all the tables you have access to using all_tables.

The following example retrieves the table_name , tablespace_name , and temporary columns from user_tables where table_name is order_status2 or order_status_temp :

  SELECT table_name, tablespace_name, temporary   FROM user_tables   WHERE table_name IN ('ORDER_STATUS2', 'ORDER_STATUS_TEMP');  TABLE_NAME                     TABLESPACE_NAME                T ------------------------------ ------------------------------ - ORDER_STATUS2                  SYSTEM                         N ORDER_STATUS_TEMP                                             Y 

Notice the order_status_temp table is temporary.

Getting Information on Columns in Tables

You can get information about the columns in your tables from user_tab_columns. Table 10-2 describes some of the columns in user_tab_columns.

Table 10-2: Some Columns in user_tab_columns

Column	Type	Description
table_name	VARCHAR2(30)	Name of the table.
column_name	VARCHAR2(30)	Name of the column.
data_type	VARCHAR2(106)	Data type of the column.
data_length	NUMBER	Length of the data.
data_precision	NUMBER	Precision of a numeric column if a precision was specified for the column.
data_scale	NUMBER	Scale of a numeric column.

Note  

You can get information on all the columns in tables you have access to using all_tab_columns.

The following example retrieves the column_name, data_type, data_length, data_precision , and data_scale from user_tab_columns for the products table:

  COLUMN column_name FORMAT a15   COLUMN data_type FORMAT a10   SELECT   column_name, data_type, data_length, data_precision, data_scale   FROM user_tab_columns   WHERE table_name = 'PRODUCTS';  COLUMN_NAME     DATA_TYPE  DATA_LENGTH DATA_PRECISION DATA_SCALE --------------- ---------- ----------- -------------- ---------- PRODUCT_ID      NUMBER              22                         0 PRODUCT_TYPE_ID NUMBER              22                         0 NAME            VARCHAR2            30 DESCRIPTION     VARCHAR2            50 PRICE           NUMBER              22              5          2 

Altering a Table

You alter a table using the ALTER TABLE statement. You can use ALTER TABLE to perform tasks such as:

• Add, modify, or drop a column

• Add or drop a constraint

• Enable or disable a constraint

In the following sections, you ll learn how to use ALTER TABLE to perform each of these tasks. You ll also see how to view the details of constraints established on a table and its columns.

Adding a Column

The following example uses ALTER TABLE to add a column named modified_by to order_status2:

 ALTER TABLE order_status2 ADD modified_by INTEGER; 

Notice the data type of modified_by is INTEGER. The next example adds a column named initially_created to order_status2:

 ALTER TABLE order_status2 ADD initially_created DATE DEFAULT SYSDATE NOT NULL; 

You can verify the addition of the new column by executing a DESCRIBE command on order_status2:

  DESCRIBE order_status2  Name                                      Null?     Type  ----------------------------------------- -------- ------------  ID                                        NOT NULL NUMBER(38)  STATUS                                             VARCHAR2(10)  LAST_MODIFIED                                      DATE  MODIFIED_BY                                        NUMBER(38)  INITIALLY_CREATED                         NOT NULL DATE 

Modifying a Column

The following list shows some of the aspects of a column you can modify using ALTER TABLE:

• Change the size of a column if the column s data type is one whose length may be changed, such as CHAR or VARCHAR2 , for example

• Change the precision of a numeric column

• Change the data type of a column

• Change the default value of a column

You ll see examples of how to change these aspects of a column in the following sections.

Changing the Size of a Column

The following example uses ALTER TABLE to increase the length of the status column of order_status2 from 10 to 15 ( status is of type VARCHAR2 ):

 ALTER TABLE order_status2 MODIFY status VARCHAR2(15); 

You can only decrease the length of a column if there are no rows in the table or all the columns contain null values.

Changing the Precision of a Numeric Column

The following example uses ALTER TABLE to change the precision of the id column of order_status2 from 38 to 5 ( id is of type NUMBER ):

 ALTER TABLE order_status2 MODIFY id NUMBER(5); 

You can only decrease the precision of a numeric column if there are no rows in the table or the column contains null values.

Changing the Data Type of a Column

The following example uses ALTER TABLE to change the data type of the status column from VARCHAR2 to CHAR:

 ALTER TABLE order_status2 MODIFY status CHAR(15); 

If the table is empty or the column contains null values, you can change the column to any data type (including a data type that is shorter). Otherwise you can only change the data type of a column to a compatible data type. For example, you can change a VARCHAR2 to CHAR (and vice versa) as long as you don t make the column shorter, but you cannot change a DATE to a NUMBER.

Changing the Default Value of a Column

The following example uses ALTER TABLE to change the default value for the last_modified column to SYSDATE - 1:

 ALTER TABLE order_status2 MODIFY last_modified DEFAULT SYSDATE - 1; 

The default value only applies to new rows added to the table.

Dropping a Column

The following example uses ALTER TABLE to drop the initially_created column from order_status2:

 ALTER TABLE order_status2 DROP COLUMN initially_created; 

Adding a Constraint

In earlier chapters, you ve seen examples of tables with PRIMARY KEY , FOREIGN KEY , and NOT NULL constraints. These constraints, along with the other types of constraints, are summarized in Table 10-3. The constraint type in Table 10-3 identifies how an Oracle database represents the type of the constraint.

Table 10-3: Constraints

Constraint	Constraint Type	Meaning
CHECK	C	Specifies the value for a column, or group of columns, must satisfy a certain condition.
NOT NULL	C	Specifies a column doesn't allow storage of null values. This is actually enforced as a CHECK constraint. You can check NOT NULL columns using the DESCRIBE command.
PRIMARY KEY	P	Specifies the primary key of a table. A primary key is made up of one or more columns that uniquely identify each row in a table.
FOREIGN KEY	R	Specifies a foreign key for a table. A foreign key references a column in another table, or a column in the same table in the case of a self-reference.
UNIQUE	U	Specifies a column, or group of columns, can only store unique values.
CHECK OPTION	V	Specifies that DML operations on a view must satisfy the subquery. You'll learn about views later in the section Views.
READ ONLY	O	Specifies that a view may only be read from.

You ll see how to add some of the constraints shown in Table 10-3 in the following sections.

Adding a CHECK Constraint

The following example uses ALTER TABLE to add a CHECK constraint to the status column of the order_status2 table:

 ALTER TABLE order_status2 ADD CONSTRAINT order_status2_status_ck CHECK (status IN ('PLACED', 'PENDING', 'SHIPPED')); 

Notice the constraint enforces the status value is in the supplied list of values in the IN clause. The following INSERT adds a row to the order_status2 table whose status value is PENDING:

 INSERT INTO order_status2 (   id, status, last_modified, modified_by ) VALUES (   1, 'PENDING', '01-JAN-2005', 1 ); 

If you attempt to add a row that doesn t satisfy a check constraint, the database returns the error ORA-02290. For example, the following INSERT attempts to add a row whose status value isn t in the list of valid values:

  INSERT INTO order_status2 (   id, status, last_modified, modified_by   ) VALUES (   2, 'CLEARED', '01-JAN-2005', 2   );  INSERT INTO order_status2 ( * ERROR at line 1: ORA-02290: check constraint (STORE.ORDER_STATUS2_STATUS_CK) violated 

You can use other comparison operators with a CHECK constraint. The next example adds a CHECK constraint that enforces the id value is greater than zero:

 ALTER TABLE order_status2 ADD CONSTRAINT order_status2_id_ck CHECK (id > 0); 

To add a constraint, any existing rows in the table must satisfy the constraint. For example, if the order_status2 table contained rows, to add the order_status2_id_ck constraint the id column would have to be greater than zero; otherwise the attempt to enable the constraint would fail with an error.

Note  

There is an exception to the previous rule: you can disable a constraint when you add it. You ll learn how to disable and enable constraints later in the sections Disabling a Constraint and Enabling a Constraint. You can also choose to apply a constraint to new data only by specifying ENABLE NOVALIDATE , which you ll learn more about in the Enabling a Constraint section.

Adding a NOT NULL Constraint

The following example uses ALTER TABLE to add a NOT NULL constraint on the status column of the order_status2 table:

 ALTER TABLE order_status2 MODIFY status CONSTRAINT order_status2_status_nn NOT NULL; 

Notice you use MODIFY to add a NOT NULL constraint rather than ADD CONSTRAINT. The next example adds a NOT NULL constraint on the modified_by column:

 ALTER TABLE order_status2 MODIFY modified_by CONSTRAINT order_status2_modified_by_nn NOT NULL; 

The next example adds a NOT NULL constraint on the last_modified column:

 ALTER TABLE order_status2 MODIFY last_modified NOT NULL; 

Notice I didn t supply a name for the constraint. The database automatically assigns a name to the constraint like SYS_C003381, which isn t particularly meaningful to a human being. You ll see how to view the details of constraints later in the section Getting Information on Constraints.

Tip  

Always specify a meaningful name to your constraints so that when a constraint error occurs in an application you can easily identify the problem.

Adding a FOREIGN KEY Constraint

The following example uses ALTER TABLE to first drop the modified_by column from order_status2 and then adds a FOREIGN KEY constraint that references the employees .employee_id column:

 ALTER TABLE order_status2 DROP COLUMN modified_by; ALTER TABLE order_status2 ADD CONSTRAINT order_status2_modified_by_fk modified_by REFERENCES employees(employee_id); 

You use the ON DELETE CASCADE clause with a FOREIGN KEY constraint to specify that when a row in the parent table is deleted, any matching rows in the child table are also deleted. The following example drops the modified_by column and rewrites the previous example to include the ON DELETE CASCADE clause:

 ALTER TABLE order_status2 DROP COLUMN modified_by; ALTER TABLE order_status2 ADD CONSTRAINT order_status2_modified_by_fk modified_by REFERENCES employees(employee_id) ON DELETE CASCADE; 

When a row is deleted from employees, any matching rows in order_status2 are also deleted.

You use the ON DELETE SET NULL clause with a FOREIGN KEY constraint to specify that when a row in the parent table is deleted, the foreign key column for the row (or rows) in the child table is set to null. The following example drops the modified_by column from order_status2 and rewrites the previous example to include the ON DELETE SET NULL clause:

 ALTER TABLE order_status2 DROP COLUMN modified_by; ALTER TABLE order_status2 ADD CONSTRAINT order_status2_modified_by_fk modified_by REFERENCES employees(employee_id) ON DELETE SET NULL; 

When a row is deleted from employees , the modified_by column for any matching rows in order_status2 is set to null. To clean up, the following statement drops the modified_by column:

 ALTER TABLE order_status2 DROP COLUMN modified_by; 

Adding a UNIQUE Constraint

The following example uses ALTER TABLE to add a UNIQUE constraint to the status column of the order_status2 table:

 ALTER TABLE order_status2 ADD CONSTRAINT order_status2_status_uq UNIQUE (status); 

Dropping a Constraint

You drop a constraint using the DROP CONSTRAINT clause of ALTER TABLE. The following example drops the order_status2_status_uq constraint:

 ALTER TABLE order_status2 DROP CONSTRAINT order_status2_status_uq; 

Disabling a Constraint

By default, a constraint is enabled when you create it. You can disable a constraint when you create it by adding DISABLE to the end of the CONSTRAINT clause. The following example adds a constraint to order_status2 but disables it:

 ALTER TABLE order_status2 ADD CONSTRAINT order_status2_status_uq UNIQUE (status) DISABLE; 

You can disable an existing constraint using the DISABLE CONSTRAINT clause of ALTER TABLE. The following example disables the order_status2_status_nn constraint:

 ALTER TABLE order_status2 DISABLE CONSTRAINT order_status2_status_nn; 

You can add CASCADE to the end of a DISABLE CONSTRAINT clause to disable any integrity constraints that depend on the specified integrity constraint. You must use CASCADE when you disable a primary key or unique constraint that is part of a foreign key constraint.

Enabling a Constraint

You can enable an existing constraint using the ENABLE CONSTRAINT clause of ALTER TABLE. The following example enables the order_status2_status_uq constraint:

 ALTER TABLE order_status2 ENABLE CONSTRAINT order_status2_status_uq; 

To enable a constraint, all the rows in the table must satisfy the constraint. For example, if the order_status2 table contained rows, to enable the order_status2_status_uq constraint the status column would have to contain unique values, otherwise the attempt to enable the constraint would fail with an error.

You can also choose to apply a constraint to new data only by specifying ENABLE NOVALIDATE. For example:

 ALTER TABLE order_status2 ENABLE NOVALIDATE CONSTRAINT order_status2_status_uq; 

Note  

The default is ENABLE VALIDATE .

Deferred Constraints

A deferred constraint is one that is enforced when a transaction is committed. You specify a constraint is deferrable using the DEFERRABLE clause when you initially add a constraint. Once you ve added a constraint, you cannot change it to DEFERRABLE ; instead, you must drop and recreate the constraint.

When you add a DEFERRABLE constraint, you can mark it as INITIALLY IMMEDIATE or INITIALLY DEFERRED. INITIALLY IMMEDIATE means that the constraint is checked whenever you add, update, or delete rows from a table (this is the same as the default behavior of a constraint). INITIALLY DEFERRED means that the constraint is only checked when a transaction is committed. Let s take a look at an example.

The following statement drops the order_status2_status_uq constraint:

 ALTER TABLE order_status2 DROP CONSTRAINT order_status2_status_uq; 

The next example adds the order_status2_status_uq constraint, setting it to DEFERRABLE INITIALLY DEFERRED:

 ALTER TABLE order_status2 ADD CONSTRAINT order_status2_status_uq UNIQUE (status) DEFERRABLE INITIALLY DEFERRED; 

If you add rows to order_status2, the order_status2_status_uq constraint isn t enforced until you perform a commit.

Getting Information on Constraints

You can get information on your constraints by querying user_constraints. Table 10-4 describes some of the columns in user_constraints.

Table 10-4: Some Columns in user_constraints

Column	Type	Description
owner	VARCHAR2(30)	Owner of the constraint.
constraint_name	VARCHAR2(30)	Name of the constraint.
constraint_type	VARCHAR2(1)	Constraint type. Set to P , R , C , U , V , or O ”see Table 10-3 shown earlier for the constraint type meanings.
table_name	VARCHAR2(30)	Name of the table on which the constraint is defined.
status	VARCHAR2(8)	Constraint status. Set to ENABLED or DISABLED .
deferrable	VARCHAR2(14)	Whether the constraint is deferrable. Set to DEFERRABLE or NOT DEFERRABLE .
deferred	VARCHAR2(9)	Whether the deferred. Set to IMMEDIATE or DEFERRED .

Note  

You can get information on all the constraints you have access to using all_constraints.

The following example retrieves the constraint_name, constraint_type, status , deferrable , and deferred from user_constraints for the order_staus2 table:

  SELECT   constraint_name, constraint_type, status, deferrable, deferred   FROM user_constraints   WHERE table_name = 'ORDER_STATUS2';  CONSTRAINT_NAME                C STATUS   DEFERRABLE     DEFERRED ------------------------------ - -------- -------------- --------- ORDER_STATUS2_PK               P ENABLED  NOT DEFERRABLE IMMEDIATE ORDER_STATUS2_STATUS_CK        C ENABLED  NOT DEFERRABLE IMMEDIATE ORDER_STATUS2_ID_CK            C ENABLED  NOT DEFERRABLE IMMEDIATE ORDER_STATUS2_STATUS_NN        C DISABLED NOT DEFERRABLE IMMEDIATE ORDER_STATUS2_STATUS_UQ        U ENABLED  DEFERRABLE     DEFERRED SYS_C004807                    C ENABLED  NOT DEFERRABLE IMMEDIATE 

Notice all the constraints except one have the name that was supplied when the constraint was added by the ALTER TABLE statement. One constraint has the database-generated name of SYS_C004807. Since this name is automatically generated, the name of the constraint in your database will be different.

Getting Information on the Constraints on a Column

You can get information on the constraints on a column by querying user_cons_columns. Table 10-5 describes some of the columns in user_cons_columns.

Table 10-5: Some Columns in user_cons_columns

Column	Type	Description
owner	VARCHAR2(30)	Owner of the constraint.
constraint_name	VARCHAR2(30)	Name of the constraint.
table_name	VARCHAR2(30)	Name of the table on which the constraint is defined.
column_name	VARCHAR2(4000)	Name of the column on which the constraint is defined.

Note  

You can get information on all the column constraints you have access to using all_cons_columns.

The following example retrieves the constraint_name and column_name from user_cons_columns for the order_staus2 table:

  COLUMN column_name FORMAT a15   SELECT constraint_name, column_name   FROM user_cons_columns   WHERE table_name = 'ORDER_STATUS2';  CONSTRAINT_NAME                COLUMN_NAME ------------------------------ --------------- ORDER_STATUS2_ID_CK            ID ORDER_STATUS2_PK               ID ORDER_STATUS2_STATUS_CK        STATUS ORDER_STATUS2_STATUS_NN        STATUS ORDER_STATUS2_STATUS_UQ        STATUS SYS_C004807                    LAST_MODIFIED 

The next example joins user_constraints and user_cons_columns to get the column_name , constraint_name, constraint_type, and status:

  SELECT   ucc.column_name, ucc.constraint_name, uc.constraint_type, uc.status   FROM user_constraints uc, user_cons_columns ucc   WHERE uc.table_name = ucc.table_name   AND uc.constraint_name = ucc.constraint_name   AND ucc.table_name = 'ORDER_STATUS2';  COLUMN_NAME     CONSTRAINT_NAME                C STATUS --------------- ------------------------------ - -------- ID              ORDER_STATUS2_ID_CK            C ENABLED ID              ORDER_STATUS2_PK               P ENABLED STATUS          ORDER_STATUS2_STATUS_CK        C ENABLED STATUS          ORDER_STATUS2_STATUS_NN        C DISABLED STATUS          ORDER_STATUS2_STATUS_UQ        U ENABLED LAST_MODIFIED   SYS_C004807                    C ENABLED 

Renaming a Table

You rename a table using the RENAME statement. The following example renames order_status2 to order_state:

 RENAME order_status2 TO order_state; 

The next example changes the table name back to the original:

 RENAME order_state TO order_status2; 

Note  

If you use the table name in your constraint name, renaming the table will also mean you should change the names of your constraints.

Adding a Comment to a Table

A comment can help you remember what the table or column is used for. You add a comment to a table or column using the COMMENT statement. The following example adds a comment to the order_status2 table:

 COMMENT ON TABLE order_status2 IS 'order_status2 stores the state of an order'; 

The next example adds a comment to the order_status2.last_modified column:

 COMMENT ON COLUMN order_status2.last_modified IS 'last_modified stores the date and time the order was modified last'; 

Getting Table Comments

You can get the comments on your tables using user_tab_comments. For example:

  SELECT *   FROM user_tab_comments   WHERE table_name = 'ORDER_STATUS2';  TABLE_NAME                     TABLE_TYPE ------------------------------ ----------- COMMENTS ------------------------------------------ ORDER_STATUS2                  TABLE order_status2 stores the state of an order 

Getting Column Comments

You can get the comments on your columns using user_col_comments. For example:

  SELECT *   FROM user_col_comments   WHERE table_name = 'ORDER_STATUS2';  TABLE_NAME                     COLUMN_NAME ------------------------------ ------------------------------ COMMENTS ------------------------------------------------------------------ ORDER_STATUS2                  ID ORDER_STATUS2                  STATUS ORDER_STATUS2                  LAST_MODIFIED last_modified stores the date and time the order was modified last 

Truncating a Table

You truncate a table using the TRUNCATE statement. This removes all the rows from a table and resets the storage area for a table. The following example truncates order_status2:

 TRUNCATE TABLE order_status2; 

Tip  

If you need to remove all the rows from a table, you should use TRUNCATE rather than DELETE. This is because TRUNCATE resets the storage area for a table ready to receive new rows. Issuing a TRUNCATE statement also doesn t require any undo space in the database and doesn t require you issue a COMMIT to make the delete permanent.

Dropping a Table

You drop a table using the DROP TABLE statement. The following example drops the order_status2 table:

 DROP TABLE order_status2; 

This concludes the discussion of tables. In the next section, you ll learn about sequences.