Les 05

5

Creating and Managing Tables

Copyright © 2004, Oracle. All rights reserved.

Objectives After completing this lesson you should be able to do the following: • Create and maintain tables by using the CREATE, ALTER, DROP, RENAME, and TRUNCATE statements • Use the data dictionary to view and maintain information about tables • Create and maintain integrity constraints • Discuss constraint states

5-2

Copyright © 2004, Oracle. All rights reserved.

Displaying Table Structure

Use the DESCRIBE command to display the structure of a table: DESC[RIBE] tablename

5-3

Copyright © 2004, Oracle. All rights reserved.

Displaying Table Structure

DESCRIBE employees

5-4

Copyright © 2004, Oracle. All rights reserved.

Referencing Another User’s Tables •

•

•

5-5

To refer to tables in schemas other than your own, prefix the table name with the schema name: schema.table For example, the DESC TABLE hr.employees statement displays the structure of the EMPLOYEES table in the HR schema. Constraints must reference tables in the same schema.

Copyright © 2004, Oracle. All rights reserved.

The CREATE TABLE Statement •

Syntax:

CREATE [GLOBAL TEMPORARY] TABLE [schema.]table (column datatype [DEFAULT expr] [column_constraint], ... [table_constraint]);

•

Example:

CREATE TABLE dept (deptno NUMBER(2), dname VARCHAR2(14), loc VARCHAR2(13) DEFAULT 'CA'); Table created.

•

To confirm table creation:

DESCRIBE dept 5-6

Copyright © 2004, Oracle. All rights reserved.

5-7

Copyright © 2004, Oracle. All rights reserved.

5-8

Copyright © 2004, Oracle. All rights reserved.

Creating a Table Using a Subquery Syntax: CREATE TABLE table [column(, column...)] AS subquery; CREATE TABLE dept30 AS SELECT * FROM dept WHERE deptno = 30; Table created. DESCRIBE dept30

5-9

Copyright © 2004, Oracle. All rights reserved.

5-10

Copyright © 2004, Oracle. All rights reserved.

Altering Table Structure

Use the ALTER TABLE statement to: • Add, modify, or remove columns • Add or remove constraints • Enable or disable constraints • Define a default value for a new column ALTER TABLE table ADD (column datatype [DEFAULT expr] [NOT NULL] [, column datatype]...); ALTER TABLE table MODIFY (column datatype [DEFAULT expr] [NOT NULL] [, column datatype]...);

5-11

Copyright © 2004, Oracle. All rights reserved.

Adding a Column • •

Add a column to an existing table by using the ALTER TABLE statement with the ADD clause. For example, to add a column named JOB to the DEPT30 table:

ALTER TABLE dept30 ADD (job VARCHAR2(9)); Table altered.

•

5-12

The JOB column becomes the last column in the table.

Copyright © 2004, Oracle. All rights reserved.

Modifying and Dropping Columns Modify a column definition by using the ALTER TABLE statement with the MODIFY clause. • You can change a column’s data type, size, default value, and NOT NULL column constraint. • A change to the default value affects only subsequent insertions to the table. ALTER TABLE dept30 MODIFY (job VARCHAR2(50)); Table altered.

•

You use the DROP COLUMN clause to free space in the database by dropping columns you no longer need.

ALTER TABLE dept30 DROP COLUMN job ; Table altered. 5-13

Copyright © 2004, Oracle. All rights reserved.

5-14

Copyright © 2004, Oracle. All rights reserved.

Marking a Column as Unused

• •

You use the SET UNUSED option to mark one or more columns as unused. You then use the DROP UNUSED COLUMNS option to remove the columns that are marked as unused.

ALTER TABLE table SET UNUSED (column); OR ALTER TABLE table SET UNUSED COLUMN column; ALTER TABLE table DROP UNUSED COLUMNS;

5-15

Copyright © 2004, Oracle. All rights reserved.

5-16

Copyright © 2004, Oracle. All rights reserved.

Guaranteeing Data Integrity

Data

Database trigger

Integrity constraint

Application code

Table

5-17

Copyright © 2004, Oracle. All rights reserved.

5-18

Copyright © 2004, Oracle. All rights reserved.

Understanding Constraints

5-19

Constraint

Description

NOT NULL

Specifies that a column cannot contain null values

UNIQUE

Designates a column or combination of columns as unique

PRIMARY KEY

Designates a column or combination of columns as the table’s primary key

FOREIGN KEY

Designates a column or combination of columns as the foreign key in a referential integrity constraint

CHECK

Specifies a condition that each row of the table must satisfy

Copyright © 2004, Oracle. All rights reserved.

Defining Constraints

CREATE TABLE [schema.]table (column datatype [DEFAULT expr] [column_constraint], ... [table_constraint]);

Column-level constraint: column [CONSTRAINT constraint_name] constraint_type,

Table-level constraint: column,... [CONSTRAINT constraint_name] constraint_type (column, ...),

5-20

Copyright © 2004, Oracle. All rights reserved.

5-21

Copyright © 2004, Oracle. All rights reserved.

5-22

Copyright © 2004, Oracle. All rights reserved.

Guidelines for Defining Constraints •

Primary and unique constraints: – Place indexes in a separate tablespace – Use nonunique indexes if bulk loads are frequent

•

Self-referencing foreign keys: – Define or enable foreign keys after initial loading – Defer constraint checking

5-23

Copyright © 2004, Oracle. All rights reserved.

5-24

Copyright © 2004, Oracle. All rights reserved.

Adding a Constraint Add constraints to a table or column by using the ALTER TABLE statement with the ADD clause: Syntax: ALTER TABLE table ADD [CONSTRAINT constraint] type (column);

Example: Add a FOREIGN KEY constraint to the EMP table, indicating that a manager must already exist as a valid employee in the EMP table. ALTER TABLE ADD CONSTRAINT FOREIGN KEY(mgr) Table altered. 5-25

emp emp_mgr_fk REFERENCES emp(empno);

Copyright © 2004, Oracle. All rights reserved.

Disabling and Enabling Constraints

•

Execute the DISABLE clause of the ALTER TABLE statement to deactivate an integrity constraint.

ALTER TABLE emp DISABLE CONSTRAINT emp_empno_pk CASCADE; Table altered.

•

Activate an integrity constraint currently disabled in the table definition by using the ENABLE clause.

ALTER TABLE emp ENABLE CONSTRAINT emp_empno_pk; Table altered.

5-26

Copyright © 2004, Oracle. All rights reserved.

5-27

Copyright © 2004, Oracle. All rights reserved.

5-28

Copyright © 2004, Oracle. All rights reserved.

Deferring Constraint Checking

DML statement Check nondeferred constraints

COMMIT

5-29

Copyright © 2004, Oracle. All rights reserved.

Check deferred constraints

Changing the Enforcement of Constraints

• •

5-30

Use the SET CONSTRAINTS statement to make constraints either deferred or immediate. The ALTER SESSION statement also has clauses to define constraints as deferred or immediate.

Copyright © 2004, Oracle. All rights reserved.

Enforcing Primary and Unique Key Constraints

Key enabled?

Yes

Is an index available for use?

Yes

Constraint deferrable?

No Constraint deferrable?

No Do not use index

5-31

Is the index nonunique?

No Yes

Use existing index

No Create unique index

Yes

Create nonunique index

Copyright © 2004, Oracle. All rights reserved.

Yes

Maintaining Tables in a Foreign Key Relationship Consider the following factors in maintaining tables that are in a foreign key relationship:

5-32

Desired Action

Appropriate Solution

Drop parent table

Cascade constraints

Truncate parent table

Disable or drop foreign key

Drop tablespace containing parent table

Use the CASCADE CONSTRAINTS clause

Run DML operations on child table

Ensure the tablespace containing the parent key is online

Copyright © 2004, Oracle. All rights reserved.

5-33

Copyright © 2004, Oracle. All rights reserved.

Dropping a Constraint •

Remove the manager constraint from the EMP table.

ALTER TABLE emp DROP CONSTRAINT emp_mgr_fk; Table altered.

•

Remove the PRIMARY KEY constraint from the DEPT table and drop the associated FOREIGN KEY constraint into the EMP.DEPTNO column.

ALTER TABLE dept DROP PRIMARY KEY CASCADE; Table altered.

5-34

Copyright © 2004, Oracle. All rights reserved.

Cascading Constraints •

•

5-35

You use the CASCADE CONSTRAINTS option to drop all referential integrity constraints that refer to the primary and unique keys on the dropped columns. CASCADE CONSTRAINTS is an option in the ALTER TABLE DROP statement.

Copyright © 2004, Oracle. All rights reserved.

5-36

Copyright © 2004, Oracle. All rights reserved.

Renaming a Table

• •

To change the name of a table, execute the RENAME statement. You must be the owner of the object.

RENAME dept TO department; Table renamed.

5-37

Copyright © 2004, Oracle. All rights reserved.

Truncating a Table

•

The TRUNCATE TABLE statement: – Removes all rows from a table – Releases the storage space used by that table

TRUNCATE TABLE department; Table truncated.

• •

5-38

You cannot roll back row removal when using TRUNCATE. Alternatively, you can remove rows by using the DELETE statement.

Copyright © 2004, Oracle. All rights reserved.

Dropping a Table

The DROP TABLE PURGE statement: • Deletes all data and the table structure • Commits any pending transactions • Drops all indexes DROP TABLE dept30 PURGE; Table dropped.

You cannot roll back this statement.

5-39

Copyright © 2004, Oracle. All rights reserved.

Adding Comments to a Table •

You can add comments to a table or column by using the COMMENT statement.

COMMENT ON TABLE emp IS 'Employee Information'; Comment created.

•

Comments can be viewed through the following data dictionary views: – – – –

5-40

ALL_COL_COMMENTS USER_COL_COMMENTS ALL_TAB_COMMENTS USER_TAB_COMMENTS

Copyright © 2004, Oracle. All rights reserved.

Understanding the Types of Tables in the Oracle Database •

User tables: – Are created and maintained by the user – Contain user information

•

Data dictionaries: – Are created and maintained by the Oracle server – Contain database information

5-41

Copyright © 2004, Oracle. All rights reserved.

5-42

Copyright © 2004, Oracle. All rights reserved.

Querying the Data Dictionary •

Describe tables owned by the user.

SELECT * FROM user_tables;

•

View distinct object types owned by the user.

SELECT DISTINCT object_type FROM user_objects;

•

View tables, views, synonyms, and sequences owned by the user.

SELECT * FROM user_catalog;

5-43

Copyright © 2004, Oracle. All rights reserved.

Viewing Constraints

•

Query the USER_CONSTRAINTS table to view all constraint definitions and names.

SELECT FROM WHERE

•

View the columns associated with the constraint names in the USER_CONS_COLUMNS view.

SELECT FROM WHERE

5-44

constraint_name, constraint_type, search_condition user_constraints table_name = 'EMP';

constraint_name, column_name user_cons_columns table_name = 'EMP';

Copyright © 2004, Oracle. All rights reserved.

Summary In this lesson, you should have learned how to do the following: • Create and maintain tables with these statements:

5-45

Statement

Description

CREATE TABLE

Creates a table

ALTER TABLE

Modifies table structures

DROP TABLE

Removes the rows and table structures

RENAME

Changes the name of a table, view, sequence, or synonym

TRUNCATE

Removes all rows from a table and releases the storage space

COMMENT

Adds comments to a table or view Copyright © 2004, Oracle. All rights reserved.

Summary •

Create the following types of constraints: – – – – –

•

5-46

NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY CHECK

Use data dictionary views to retrieve table, column, and constraint information.

Copyright © 2004, Oracle. All rights reserved.

5-47

Copyright © 2004, Oracle. All rights reserved.

5-48

Copyright © 2004, Oracle. All rights reserved.

5-49

Copyright © 2004, Oracle. All rights reserved.

Practice 5: Overview This practice covers the following topics: • Creating a new table: – Containing constraints – Using the CREATE TABLE AS syntax

•

Altering existing tables by: – Adding a column – Adding a column constraint – Modifying an existing column definition

• • •

5-50

Dropping a table Adding comments to a table Verifying that tables exist and displaying information in the data dictionary Copyright © 2004, Oracle. All rights reserved.

5-51

Copyright © 2004, Oracle. All rights reserved.

5-52

Copyright © 2004, Oracle. All rights reserved.

5-53

Copyright © 2004, Oracle. All rights reserved.

5-54

Copyright © 2004, Oracle. All rights reserved.