ORACLE/PLSQL: CREATE TABLE STATEMENT

This Oracle tutorial explains how to use the Oracle CREATE TABLE statement with syntax, examples, and practice exercises.

DESCRIPTION

The Oracle CREATE TABLE statement allows you to create and define a table.

SYNTAX

The syntax for the Oracle CREATE TABLE statement is:

CREATE TABLE table_name
( 
  column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...
);

Parameters or Arguments

table_name is the name of the table that you wish to create.

column1, column2 are the columns that you wish to create in the table. Each column must have a datatype. The column should either be defined as "null" or "not null" and if this value is left blank, the database assumes "null" as the default.

EXAMPLE

Let's look at an Oracle CREATE TABLE example.

CREATE TABLE customers
( customer_id number(10) not null,
  customer_name varchar2(50) not null,
  city varchar2(50)
);

This Oracle CREATE TABLE example creates a table called customers which has 3 columns.

The first column is called customer_id which is created as a number datatype (maximum 10 digits in length) and can not contain null values.
The second column is called customer_name which is a varchar2 datatype (50 maximum characters in length) and also can not contain null values.
The third column is called city which is a varchar2 datatype but can contain null values.

Now the only problem with this Oracle CREATE TABLE statement is that you have not defined a primary key for the table. We could modify this CREATE TABLE statement and define the customer_id as the primary key as follows:

CREATE TABLE customers
( customer_id number(10) not null,
  customer_name varchar2(50) not null,
  city varchar2(50),
  CONSTRAINT customers_pk PRIMARY KEY (customer_id)
);

Learn about primary keys.

Learn about foreign keys.

PRACTICE EXERCISE #1:

Create an Oracle table called suppliers that stores supplier ID, name, and address information.

Solution for Practice Exercise #1:

The Oracle CREATE TABLE statement for the suppliers table is:

CREATE TABLE suppliers
( supplier_id number(10) not null,
  supplier_name varchar2(50) not null,
  address varchar2(50),
  city varchar2(50),
  state varchar2(25),
  zip_code varchar2(10)
);

PRACTICE EXERCISE #2:

Create an Oracle table called customers that stores customer ID, name, and address information.

But this time, the customer ID should be the primary key for the table.

Solution for Practice Exercise #2:

The Oracle CREATE TABLE statement for the customers table is:

CREATE TABLE customers
( customer_id number(10) not null,
  customer_name varchar2(50) not null,
  address varchar2(50),
  city varchar2(50),
  state varchar2(25),
  zip_code varchar2(10),
  CONSTRAINT customers_pk PRIMARY KEY (customer_id)
);

PRACTICE EXERCISE #3:

Based on the departments table below, create an Oracle table called employees that stores employee number, employee name, department, and salary information. The primary key for the employees table should be the employee number. Create aforeign key on the employees table that references the departments table based on the department_id field.

CREATE TABLE departments
( department_id number(10) not null,
  department_name varchar2(50) not null,
  CONSTRAINT departments_pk PRIMARY KEY (department_id)
);

Solution for Practice Exercise #3:

The Oracle CREATE TABLE statement for the employees table is:

CREATE TABLE employees
( employee_number number(10) not null,
  employee_name varchar2(50) not null,
  department_id number(10),
  salary number(6),
  CONSTRAINT employees_pk PRIMARY KEY (employee_number),
  CONSTRAINT fk_departments
    FOREIGN KEY (department_id)
    REFERENCES departments(department_id)
);

ORACLE/PLSQL: CREATE TABLE AS STATEMENT

This Oracle tutorial explains how to use the Oracle CREATE TABLE AS statement with syntax and examples.

DESCRIPTION

You can also use the Oracle CREATE TABLE AS statement to create a table from an existing table by copying the existing table's columns.

It is important to note that when creating a table in this way, the new table will be populated with the records from the existing table (based on the SELECT Statement).

CREATE TABLE - BY COPYING ALL COLUMNS FROM ANOTHER TABLE

Syntax

The syntax for the Oracle CREATE TABLE AS statement that copies all of the columns is:

CREATE TABLE new_table
  AS (SELECT * FROM old_table);

Example

Let's look at a CREATE TABLE AS example that shows how to create a table by copying all columns from another table.

CREATE TABLE suppliers
AS (SELECT *
    FROM companies
    WHERE company_id < 5000);

This example would create a new table called suppliers that included all columns from the companies table.

If there were records in the companies table, then the new suppliers table would be populated with the records returned by the SELECT statement.

CREATE TABLE - BY COPYING SELECTED COLUMNS FROM ANOTHER TABLE

Syntax

The syntax for the Oracle CREATE TABLE AS statement that copies the selected columns is:

CREATE TABLE new_table
  AS (SELECT column_1, column2, ... column_n
      FROM old_table);

Example

Let's look at a CREATE TABLE AS example that shows how to create a table by copying selected columns from another table.

For Example:

CREATE TABLE suppliers
  AS (SELECT company_id, address, city, state, zip
      FROM companies
      WHERE company_id < 5000);

This example would create a new table called suppliers, but the new table would only include the specified columns (ie: company_id, address, city, state, and zip) from the companies table.

Again, if there were records in the companies table, then the new suppliers table would be populated with the records returned by the SELECT statement.

CREATE TABLE - BY COPYING SELECTED COLUMNS FROM MULTIPLE TABLES

Syntax

The syntax for the Oracle CREATE TABLE AS statement that copies columns from multiple tables is:

CREATE TABLE new_table
  AS (SELECT column_1, column2, ... column_n
      FROM old_table_1, old_table_2, ... old_table_n);

Example

Let's look at a CREATE TABLE AS example that shows how to create a table by copying selected columns from multiple tables.

For example:

CREATE TABLE suppliers
  AS (SELECT companies.company_id, companies.address, categories.category_type
      FROM companies, categories
      WHERE companies.company_id = categories.category_id
      AND companies.company_id < 5000);

This example would create a new table called suppliers based on columns definitions from both the companies andcategories tables (ie: company_id, address, and category_type).

FREQUENTLY ASKED QUESTIONS

Question: How can I create an Oracle table from another table without copying any values from the old table?

Answer: To do this, the Oracle CREATE TABLE syntax is:

CREATE TABLE new_table
  AS (SELECT *
      FROM old_table WHERE 1=2);

For example:

CREATE TABLE suppliers
  AS (SELECT *
      FROM companies WHERE 1=2);

This would create a new table called suppliers that included all column definitions from the companies table, but no data from the companies table.

ORACLE/PLSQL: ALTER TABLE STATEMENT

This Oracle tutorial explains how to use the Oracle ALTER TABLE statement to add a column, modify a column, drop a column, rename a column or rename a table (with syntax, examples and practice exercises).

DESCRIPTION

The Oracle ALTER TABLE statement is used to add, modify, or drop/delete columns in a table. The Oracle ALTER TABLE statement is also used to rename a table.

ADD COLUMN IN TABLE

Syntax

To ADD A COLUMN in a table, the Oracle ALTER TABLE syntax is:

ALTER TABLE table_name
  ADD column_name column-definition;

Example

Let's look at an example that shows how to add a column in an Oracle table using the ALTER TABLE statement.

For example:

ALTER TABLE customers
  ADD customer_name varchar2(45);

This Oracle ALTER TABLE example will add a column called customer_name to the customers table.

ADD MULTIPLE COLUMNS IN TABLE

Syntax

To ADD MULTIPLE COLUMNS to an existing table, the Oracle ALTER TABLE syntax is:

ALTER TABLE table_name
  ADD (column_1 column-definition,
       column_2 column-definition,
       ...
       column_n column_definition);

Example

Let's look at an example that shows how to add multiple columns in an Oracle table using the ALTER TABLE statement.

For example:

ALTER TABLE customers
  ADD (customer_name varchar2(45),
       city varchar2(40));

This Oracle ALTER TABLE example will add two columns, customer_name as a varchar2(45) field and city as a varchar2(40) field to the customers table.

MODIFY COLUMN IN TABLE

Syntax

To MODIFY A COLUMN in an existing table, the Oracle ALTER TABLE syntax is:

ALTER TABLE table_name
  MODIFY column_name column_type;

Example

Let's look at an example that shows how to modify a column in an Oracle table using the ALTER TABLE statement.

For example:

ALTER TABLE customers
  MODIFY customer_name varchar2(100) not null;

This Oracle ALTER TABLE example will modify the column called customer_name to be a data type of varchar2(100) and force the column to not allow null values.

MODIFY MULTIPLE COLUMNS IN TABLE

SyntaX

To MODIFY MULTIPLE COLUMNS in an existing table, the Oracle ALTER TABLE syntax is:

ALTER TABLE table_name
  MODIFY (column_1 column_type,
          column_2 column_type,
          ...
          column_n column_type);

Example

Let's look at an example that shows how to modify multiple columns in an Oracle table using the ALTER TABLE statement.

For example:

ALTER TABLE customers
  MODIFY (customer_name varchar2(100) not null,
          city varchar2(75));

This Oracle ALTER TABLE example will modify both the customer_name and city columns.

DROP COLUMN IN TABLE

Syntax

To DROP A COLUMN in an existing table, the Oracle ALTER TABLE syntax is:

ALTER TABLE table_name
  DROP COLUMN column_name;

Example

Let's look at an example that shows how to drop a column in an Oracle table using the ALTER TABLE statement.

For example:

ALTER TABLE customers
  DROP COLUMN customer_name;

This Oracle ALTER TABLE example will drop the column called customer_name from the table called customers.

RENAME COLUMN IN TABLE
(NEW IN ORACLE 9I RELEASE 2)

Syntax

Starting in Oracle 9i Release 2, you can now rename a column.

To RENAME A COLUMN in an existing table, the Oracle ALTER TABLE syntax is:

ALTER TABLE table_name
  RENAME COLUMN old_name to new_name;

Example

Let's look at an example that shows how to rename a column in an Oracle table using the ALTER TABLE statement.

For example:

ALTER TABLE customers
  RENAME COLUMN customer_name to cname;

This Oracle ALTER TABLE example will rename the column called customer_name to cname.

RENAME TABLE

Syntax

To RENAME A TABLE, the Oracle ALTER TABLE syntax is:

ALTER TABLE table_name
  RENAME TO new_table_name;

Example

Let's look at an example that shows how to rename a table in Oracle using the ALTER TABLE statement.

For example:

ALTER TABLE customers
  RENAME TO contacts;

This Oracle ALTER TABLE example will rename the customers table to contacts.

PRACTICE EXERCISE #1:

Based on the departments table below, rename the departments table to depts.

CREATE TABLE departments
( department_id number(10) not null,
  department_name varchar2(50) not null,
  CONSTRAINT departments_pk PRIMARY KEY (department_id)
);

Solution for Practice Exercise #1:

The following Oracle ALTER TABLE statement would rename the departments table to depts:

ALTER TABLE departments
  RENAME TO depts;

PRACTICE EXERCISE #2:

Based on the employees table below, add a column called bonus that is a number(6) datatype.

CREATE TABLE employees
( employee_number number(10) not null,
  employee_name varchar2(50) not null,
  department_id number(10),
  CONSTRAINT employees_pk PRIMARY KEY (employee_number)
);

Solution for Practice Exercise #2:

The following Oracle ALTER TABLE statement would add a bonus column to the employees table:

ALTER TABLE employees
  ADD bonus number(6);

PRACTICE EXERCISE #3:

Based on the customers table below, add two columns - one column called contact_name that is a varchar2(50) datatype and one column called last_contacted that is a date datatype.

CREATE TABLE customers
( customer_id number(10) not null,
  customer_name varchar2(50) not null,
  address varchar2(50),
  city varchar2(50),
  state varchar2(25),
  zip_code varchar2(10),
  CONSTRAINT customers_pk PRIMARY KEY (customer_id)
);

Solution for Practice Exercise #3:

The following Oracle ALTER TABLE statement would add the contact_name and last_contacted columns to the customerstable:

ALTER TABLE customers
  ADD (contact_name varchar2(50),
       last_contacted date);

PRACTICE EXERCISE #4:

Based on the employees table below, change the employee_name column to a varchar2(75) datatype.

CREATE TABLE employees
( employee_number number(10) not null,
  employee_name >varchar2(50) not null,
  department_id number(10),
  CONSTRAINT employees_pk PRIMARY KEY (employee_number)
);

Solution for Practice Exercise #4:

The following Oracle ALTER TABLE statement would change the datatype for the employee_name column to varchar2(75):

ALTER TABLE employees
  MODIFY employee_name varchar2(75);

PRACTICE EXERCISE #5:

Based on the customers table below, change the customer_name column to NOT allow null values and change the statecolumn to a varchar2(2) datatype.

CREATE TABLE customers
( customer_id number(10) not null,
  customer_name varchar2(50),
  address varchar2(50),
  city varchar2(50),
  state varchar2(25),
  zip_code varchar2(10),
  CONSTRAINT customers_pk PRIMARY KEY (customer_id)
);

Solution for Practice Exercise #5:

The following Oracle ALTER TABLE statement would modify the customer_name and state columns accordingly in thecustomers table:

ALTER TABLE customers
  MODIFY (customer_name varchar2(50) not null,
          state varchar2(2));

PRACTICE EXERCISE #6:

Based on the employees table below, drop the salary column.

CREATE TABLE employees
( employee_number number(10) not null,
  employee_name varchar2(50) not null,
  department_id number(10),
  salary number(6),
  CONSTRAINT employees_pk PRIMARY KEY (employee_number)
);

Solution for

ORACLE/PLSQL: DROP TABLE STATEMENT

This Oracle tutorial explains how to use the Oracle DROP TABLE statement with syntax and examples.

DESCRIPTION

The Oracle DROP TABLE statement allows you to remove or delete a table from the Oracle database.

SYNTAX

The syntax for the Oracle DROP TABLE statement is:

DROP [schema_name].TABLE table_name
[ CASCADE CONSTRAINTS ]
[ PURGE ];

Parameters or Arguments

schema_name is the name of the schema that owns the table.

table_name is the name of the table to remove from the Oracle database.

CASCADE CONSTRAINTS is optional. If specified, all referential integrity constraints will be dropped as well.

PURGE is optional. If specified, the table and its dependent objects will be purged from the recycle bin and you will not be able to recover the table. If not specified, the table and its dependent objects are placed in the recycle bin and can be recovered later, if needed.

NOTE

If there are referential integrity constraints on table_name and you do not specify the CASCADE CONSTRAINTSoption, the DROP TABLE statement will return an error and Oracle will not drop the table.

EXAMPLE

Let's look at an example that shows how to drop a table in Oracle by using the DROP TABLE statement.

For example:

DROP TABLE customers;

This Oracle DROP TABLE example would drop the table called customers.

Purge

Let's look at how to use the PURGE option with the DROP TABLE statement in Oracle.

When issuing a DROP TABLE statement in Oracle, you can specify the PURGE option. The PURGE option will purge the table and its dependent objects so that they do not appear in the recycle bin. The risk of specifying the PURGE option is that you will not be able to recover the table. However, the benefit of using PURGE is that you can ensure that sensitive data will not be left sitting in the recycle bin.

For example:

DROP TABLE customers PURGE;

This DROP TABLE statement would drop the table called customers and issue a PURGE so that the space associated with the customers table is released. In other words, the customers table is not placed into the recycle bin and, therefore, can not be recovered later if required.

ORACLE/PLSQL: VIEW

This Oracle tutorial explains how to create, update, and drop Oracle VIEWS with syntax and examples.

WHAT IS A VIEW IN ORACLE?

An Oracle VIEW, in essence, is a virtual table that does not physically exist. Rather, it is created by a query joining one or more tables.

CREATE VIEW

Syntax

The syntax for the Oracle CREATE VIEW Statement is:

CREATE VIEW view_name AS
  SELECT columns
  FROM tables
  WHERE conditions;

view_name is the name of the Oracle VIEW that you wish to create.

Example

Here is an example of how to use the Oracle CREATE VIEW:

CREATE VIEW sup_orders AS
  SELECT suppliers.supplier_id, orders.quantity, orders.price
  FROM suppliers
  INNER JOIN orders
  ON suppliers.supplier_id = orders.supplier_id
  WHERE suppliers.supplier_name = 'Microsoft';

This Oracle CREATE VIEW example would create a virtual table based on the result set of the SELECT statement. You can now query the Oracle VIEW as follows:

SELECT *
FROM sup_orders;

UPDATE VIEW

You can modify the definition of an Oracle VIEW without dropping it by using the Oracle CREATE OR REPLACE VIEW Statement.

Syntax

The syntax for the Oracle CREATE OR REPLACE VIEW Statement is:

CREATE OR REPLACE VIEW view_name AS
  SELECT columns
  FROM table
  WHERE conditions;

Example

Here is an example of how you would use the Oracle CREATE OR REPLACE VIEW Statement:

CREATE or REPLACE VIEW sup_orders AS
  SELECT suppliers.supplier_id, orders.quantity, orders.price
  FROM suppliers
  INNER JOIN orders
  ON suppliers.supplier_id = orders.supplier_id
  WHERE suppliers.supplier_name = 'Apple';

This Oracle CREATE OR REPLACE VIEW example would update the definition of the Oracle VIEW called sup_orderswithout dropping it. If the Oracle VIEW did not yet exist, the VIEW would merely be created for the first time.

DROP VIEW

Once an Oracle VIEW has been created, you can drop it with the Oracle DROP VIEW Statement.

Syntax

The syntax for the Oracle DROP VIEW Statement is:

DROP VIEW view_name;

view_name is the name of the view that you wish to drop.

Example

Here is an example of how to use the Oracle DROP VIEW Statement:

DROP VIEW sup_orders;

This Oracle DROP VIEW example would drop/delete the Oracle VIEW called sup_orders.

FREQUENTLY ASKED QUESTIONS

Question: Can you update the data in an Oracle VIEW?

Answer: A VIEW in Oracle is created by joining one or more tables. When you update record(s) in a VIEW, it updates the records in the underlying tables that make up the View.

So, yes, you can update the data in an Oracle VIEW providing you have the proper privileges to the underlying Oracle tables.

Question: Does the Oracle View exist if the table is dropped from the database?

Answer: Yes, in Oracle, the VIEW continues to exist even after one of the tables (that the Oracle VIEW is based on) is dropped from the database. However, if you try to query the Oracle VIEW after the table has been dropped, you will receive a message indicating that the Oracle VIEW has errors.

If you recreate the table (the table that you had dropped), the Oracle VIEW will again be fine.

ORACLE/PLSQL: GLOBAL TEMPORARY TABLES

This Oracle tutorial explains how to use the Oracle GLOBAL TEMPORARY TABLES with syntax and examples.

DESCRIPTION

GLOBAL TEMPORARY TABLES in Oracle are tables that are created distinct within the Oracle sessions.

SYNTAX

The syntax for Oracle CREATE GLOBAL TEMPORARY TABLE is:

CREATE GLOBAL TEMPORARY TABLE table_name
( column1 datatype null/not null,
  column2 datatype null/not null,
  ...
);

Parameters or Arguments

table_name is the name of the global temporary table that you wish to create.

column1, column2 are the columns that you wish to create in the global temporary table. Each column must have a datatype. The column should either be defined as "null" or "not null" and if this value is left blank, the database assumes "null" as the default.

EXAMPLE

Let's look at an Oracle CREATE GLOBAL TEMPORARY TABLE example:

CREATE GLOBAL TEMPORARY TABLE suppliers
( supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50)
);

This example would create a GLOBAL TEMPORARY TABLE called suppliers.

ORACLE/PLSQL: LOCAL TEMPORARY TABLES

This Oracle tutorial explains how to use the Oracle LOCAL TEMPORARY TABLES with syntax and examples.

DESCRIPTION

Oracle LOCAL TEMPORARY TABLES are distinct within modules and embedded SQL programs within Oracle sessions.

SYNTAX

The syntax for Oracle DECLARE LOCAL TEMPORARY TABLE is:

DECLARE LOCAL TEMPORARY TABLE table_name
( column1 datatype null/not null,
  column2 datatype null/not null,
  ...
);

Parameters or Arguments

table_name is the name of the local temporary table that you wish to create.

column1, column2 are the columns that you wish to create in the local temporary table. Each column must have a datatype. The column should either be defined as "null" or "not null" and if this value is left blank, the database assumes "null" as the default.

EXAMPLE

Let's look at an Oracle DECLARE LOCAL TEMPORARY TABLE example:

DECLARE LOCAL TEMPORARY TABLE suppliers_temp
( supplier_id number(10) not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50)
);

This example would create a LOCAL TEMPORARY TABLE called suppliers_temp in Oracle.

ORACLE/PLSQL: PRIMARY KEYS

This Oracle tutorial explains how to create, drop, disable, and enable a primary key in Oracle with syntax and examples.

WHAT IS A PRIMARY KEY IN ORACLE?

In Oracle, a primary key is a single field or combination of fields that uniquely defines a record. None of the fields that are part of the primary key can contain a null value. A table can have only one primary key.

NOTE

In Oracle, a primary key can not contain more than 32 columns.

A primary key can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.

CREATE PRIMARY KEY - USING CREATE TABLE STATEMENT

You can create a primary key in Oracle with the CREATE TABLE statement.

Syntax

The syntax to create a primary key using the CREATE TABLE statement in Oracle/PLSQL is:

CREATE TABLE table_name
(
  column1 datatype null/not null,
  column2 datatype null/not null,
  ...
  
  CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ... column_n)
);

Example

Let's look at an example of how to create a primary key using the CREATE TABLE statement in Oracle:

CREATE TABLE supplier
(
  supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50),
  CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);

In this example, we've created a primary key on the supplier table called supplier_pk. It consists of only one field - the supplier_id field.

We could also create a primary key with more than one field as in the example below:

CREATE TABLE supplier
(
  supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50),
  CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name)
);

CREATE PRIMARY KEY - USING ALTER TABLE STATEMENT

You can create a primary key in Oracle with the ALTER TABLE statement.

Syntax

The syntax to create a primary key using the ALTER TABLE statement in Oracle/PLSQL is:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ... column_n);

Example

Let's look at an example of how to create a primary key using the ALTER TABLE statement in Oracle.

ALTER TABLE supplier
ADD CONSTRAINT supplier_pk PRIMARY KEY (supplier_id);

In this example, we've created a primary key on the existing supplier table called supplier_pk. It consists of the field called supplier_id.

We could also create a primary key with more than one field as in the example below:

ALTER TABLE supplier
ADD CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name);

DROP PRIMARY KEY

You can drop a primary key in Oracle using the ALTER TABLE statement.

Syntax

The syntax to drop a primary key using the ALTER TABLE statement in Oracle/PLSQL is:

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

Example

Let's look at an example of how to drop a primary key using the ALTER TABLE statement in Oracle.

ALTER TABLE supplier
DROP CONSTRAINT supplier_pk;

In this example, we're dropping a primary key on the supplier table called supplier_pk.

DISABLE PRIMARY KEY

You can disable a primary key in Oracle using the ALTER TABLE statement.

Syntax

The syntax to disable a primary key using the ALTER TABLE statement in Oracle/PLSQL is:

ALTER TABLE table_name
DISABLE CONSTRAINT constraint_name;

Example

Let's look at an example of how to disable a primary using the ALTER TABLE statement in Oracle.

ALTER TABLE supplier
DISABLE CONSTRAINT supplier_pk;

In this example, we're disabling a primary key on the supplier table called supplier_pk.

ENABLE PRIMARY KEY

You can enable a primary key in Oracle using the ALTER TABLE statement.

Syntax

The syntax to enable a primary key using the ALTER TABLE statement in Oracle/PLSQL is:

ALTER TABLE table_name
ENABLE CONSTRAINT constraint_name;

Example

Let's look at an example of how to enable a primary key using the ALTER TABLE statement in Oracle.

ALTER TABLE supplier
ENABLE CONSTRAINT supplier_pk;

In this example, we're enabling a primary key on the supplier table called supplier_pk.

The following Oracle ALTER TABLE statement would drop the salary column from the employees table:

ALTER TABLE employees
  DROP COLUMN salary;

PRACTICE EXERCISE #7:

Based on the departments table below, rename the department_name column to dept_name.

CREATE TABLE departments
( department_id number(10) not null,
  department_name varchar2(50) not null,
  CONSTRAINT departments_pk PRIMARY KEY (department_id)
);

Solution for Practice Exercise #7:

The following Oracle ALTER TABLE statement would rename the department_name column to dept_name in the departmentstable:

ALTER TABLE departments
  RENAME COLUMN department_name to dept_name;

ORACLE/PLSQL: FOREIGN KEYS

This Oracle tutorial explains how to use Foreign Keys in Oracle with syntax and examples.

WHAT IS A FOREIGN KEY IN ORACLE?

A foreign key is a way to enforce referential integrity within your Oracle database. A foreign key means that values in one table must also appear in another table.

The referenced table is called the parent table while the table with the foreign key is called the child table. The foreign key in the child table will generally reference a primary key in the parent table.

A foreign key can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.

USING A CREATE TABLE STATEMENT

Syntax

The syntax for creating a foreign key using a CREATE TABLE statement is:

CREATE TABLE table_name
(
  column1 datatype null/not null,
  column2 datatype null/not null,
  ...

  CONSTRAINT fk_column
    FOREIGN KEY (column1, column2, ... column_n)
    REFERENCES parent_table (column1, column2, ... column_n)
);

Example

CREATE TABLE supplier
( supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50),
  CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);

CREATE TABLE products
( product_id numeric(10) not null,
  supplier_id numeric(10) not null,
  CONSTRAINT fk_supplier
    FOREIGN KEY (supplier_id)
    REFERENCES supplier(supplier_id)
);

In this example, we've created a primary key on the supplier table called supplier_pk. It consists of only one field - the supplier_id field. Then we've created a foreign key called fk_supplier on the products table that references the supplier table based on the supplier_id field.

We could also create a foreign key with more than one field as in the example below:

CREATE TABLE supplier
( supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50),
  CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name)
);

CREATE TABLE products
( product_id numeric(10) not null,
  supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  CONSTRAINT fk_supplier_comp
    FOREIGN KEY (supplier_id, supplier_name)
    REFERENCES supplier(supplier_id, supplier_name)
);

In this example, our foreign key called fk_foreign_comp references the supplier table based on two fields - the supplier_id and supplier_name fields.

USING AN ALTER TABLE STATEMENT

Syntax

The syntax for creating a foreign key in an ALTER TABLE statement is:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name
   FOREIGN KEY (column1, column2, ... column_n)
   REFERENCES parent_table (column1, column2, ... column_n);

Example

ALTER TABLE products
ADD CONSTRAINT fk_supplier
  FOREIGN KEY (supplier_id)
  REFERENCES supplier(supplier_id);

In this example, we've created a foreign key called fk_supplier that references the supplier table based on the supplier_id field.

We could also create a foreign key with more than one field as in the example below:

ALTER TABLE products
ADD CONSTRAINT fk_supplier
  FOREIGN KEY (supplier_id, supplier_name)
  REFERENCES supplier(supplier_id, supplier_name);

ORACLE/PLSQL: FOREIGN KEYS WITH CASCADE DELETE

This Oracle tutorial explains how to use Foreign Keys with cascade delete in Oracle with syntax and examples.

WHAT IS A FOREIGN KEY WITH CASCADE DELETE IN ORACLE?

A foreign key with cascade delete means that if a record in the parent table is deleted, then the corresponding records in the child table with automatically be deleted. This is called a cascade delete in Oracle.

A foreign key with a cascade delete can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.

USING A CREATE TABLE STATEMENT

Syntax

The syntax for creating a foreign key with cascade delete using a CREATE TABLE statement in Oracle/PLSQL is:

CREATE TABLE table_name
(
  column1 datatype null/not null,
  column2 datatype null/not null,
  ...
  
  CONSTRAINT fk_column
     FOREIGN KEY (column1, column2, ... column_n)
     REFERENCES parent_table (column1, column2, ... column_n)
     ON DELETE CASCADE
);

Example

Let's look at an example of how to create a foreign key with cascade delete using the CREATE TABLE statement in Oracle/PLSQL.

For example:

CREATE TABLE supplier
( supplier_id numeric(10) >not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50),
  CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);

CREATE TABLE products
( product_id numeric(10) not null,
  supplier_id numeric(10) not null,
  CONSTRAINT fk_supplier
    FOREIGN KEY (supplier_id)
    REFERENCES supplier(supplier_id)
    ON DELETE CASCADE
);

Because of the cascade delete, when a record in the supplier table is deleted, all records in the products table will also be deleted that have the same supplier_id value.

We could also create a foreign key (with a cascade delete) with more than one field as in the example below:

CREATE TABLE supplier
( supplier_id numeric(10) not null,
  supplier_name varchar2(50) >not null,
  contact_name varchar2(50),
  CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name)
);

CREATE TABLE products
( product_id numeric(10) not null,
  supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  CONSTRAINT fk_supplier_comp
    FOREIGN KEY (supplier_id, supplier_name)
    REFERENCES supplier(supplier_id, supplier_name)
    ON DELETE CASCADE
);

In this example, our foreign key called fk_foreign_comp references the supplier table based on two fields - the supplier_id and supplier_name fields.

The cascade delete on the foreign key called fk_foreign_comp causes all corresponding records in the products table to be cascade deleted when a record in the supplier table is deleted, based on supplier_id and supplier_name.

USING AN ALTER TABLE STATEMENT

Syntax

The syntax for creating a foreign key with cascade delete in an ALTER TABLE statement in Oracle/PLSQL is:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name
   FOREIGN KEY (column1, column2, ... column_n)
   REFERENCES parent_table (column1, column2, ... column_n)
   ON DELETE CASCADE;

Example

Let's look at an example of how to create a foreign key with cascade delete using the ALTER TABLE statement in Oracle/PLSQL.

For example:

ALTER TABLE products
ADD CONSTRAINT fk_supplier
  FOREIGN KEY (supplier_id)
  REFERENCES supplier(supplier_id)
  ON DELETE CASCADE;

In this example, we've created a foreign key (with a cascade delete) called fk_supplier that references the supplier table based on the supplier_id field.

We could also create a foreign key (with a cascade delete) with more than one field as in the example below:

ALTER TABLE products
ADD CONSTRAINT fk_supplier
  FOREIGN KEY (supplier_id, supplier_name)
  REFERENCES supplier(supplier_id, supplier_name)
  ON DELETE CASCADE;

ORACLE/PLSQL: FOREIGN KEYS WITH "SET NULL ON DELETE"

This Oracle tutorial explains how to use Foreign Keys with "set null on delete" in Oracle with syntax and examples.

WHAT IS A FOREIGN KEY WITH "SET NULL ON DELETE" IN ORACLE?

A foreign key with "set null on delete" means that if a record in the parent table is deleted, then the corresponding records in the child table will have the foreign key fields set to null. The records in the child table will not be deleted.

A foreign key with a "set null on delete" can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.

USING A CREATE TABLE STATEMENT

Syntax

The syntax for creating a foreign key using a CREATE TABLE statement is:

CREATE TABLE table_name
(
  column1 datatype null/not null,
  column2 datatype null/not null,
  ...
  
  CONSTRAINT fk_column
     FOREIGN KEY (column1, column2, ... column_n)
     REFERENCES parent_table (column1, column2, ... column_n)
     ON DELETE SET NULL
);

Example

CREATE TABLE supplier
( supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50),
  CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);

CREATE TABLE products
( product_id numeric(10) not null,
  supplier_id numeric(10),
  CONSTRAINT fk_supplier
    FOREIGN KEY (supplier_id)
    REFERENCES supplier(supplier_id)
    ON DELETE SET NULL
);

Because of the set null on delete, when a record in the supplier table is deleted, all corresponding records in the products table will have the supplier_id values set to null.

We could also create a foreign key "set null on delete" with more than one field as in the example below:

CREATE TABLE supplier
( supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50),
  CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name)
);

CREATE TABLE products
( product_id numeric(10) not null,
  supplier_id numeric(10),
  supplier_name varchar2(50),
  CONSTRAINT fk_supplier_comp
    FOREIGN KEY (supplier_id, supplier_name)
    REFERENCES supplier(supplier_id, supplier_name)
    ON DELETE SET NULL
);

In this example, our foreign key called fk_foreign_comp references the supplier table based on two fields - the supplier_id and supplier_name fields.

The delete on the foreign key called fk_foreign_comp causes all corresponding records in the products table to have the supplier_id and supplier_name fields set to null when a record in the supplier table is deleted, based on supplier_id and supplier_name.

USING AN ALTER TABLE STATEMENT

Syntax

The syntax for creating a foreign key in an ALTER TABLE statement is:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name
   FOREIGN KEY (column1, column2, ... column_n)
   REFERENCES parent_table (column1, column2, ... column_n)
   ON DELETE SET NULL;

Example

ALTER TABLE products
ADD CONSTRAINT fk_supplier
  FOREIGN KEY (supplier_id)
  REFERENCES supplier(supplier_id)
  ON DELETE SET NULL;

In this example, we've created a foreign key "with a set null on delete" called fk_supplier that references the supplier table based on the supplier_id field.

We could also create a foreign key "with a set null on delete" with more than one field as in the example below:

ALTER TABLE products
ADD CONSTRAINT fk_supplier
  FOREIGN KEY (supplier_id, supplier_name)
  REFERENCES supplier(supplier_id, supplier_name)
  ON DELETE SET NULL;

ORACLE/PLSQL: DROP A FOREIGN KEY

This Oracle tutorial explains how to drop a foreign key in Oracle with syntax and examples.

DESCRIPTION

Once a foreign key has been created, you may find that you wish to drop the foreign key from the table. You can do this with the ALTER TABLE statement in Oracle.

SYNTAX

The syntax to drop a foreign key in Oracle/PLSQL is:

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

EXAMPLE

If you had created a foreign key as follows:

CREATE TABLE supplier
( supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50),
  CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);

CREATE TABLE products
( product_id numeric(10) not null,
  supplier_id numeric(10) not null,
  CONSTRAINT fk_supplier
    FOREIGN KEY (supplier_id)
    REFERENCES supplier(supplier_id)
);

If we then wanted to drop the foreign key called fk_supplier, we could execute the following command:

ALTER TABLE products
DROP CONSTRAINT fk_supplier;

ORACLE/PLSQL: DISABLE A FOREIGN KEY

This Oracle tutorial explains how to disable a foreign key in Oracle with syntax and examples.

DESCRIPTION

Once you have created a foreign key in Oracle, you may encounter a situation where you are required to disable the foreign key. You can do this using the ALTER TABLE statement in Oracle.

SYNTAX

The syntax to disable a foreign key in Oracle/PLSQL is:

ALTER TABLE table_name
DISABLE CONSTRAINT constraint_name;

EXAMPLE

If you had created a foreign key as follows:

CREATE TABLE supplier
( supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50),
  CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);

CREATE TABLE products
( product_id numeric(10) not null,
  supplier_id numeric(10) not null,
  CONSTRAINT fk_supplier
    FOREIGN KEY (supplier_id)
    REFERENCES supplier(supplier_id)
);

If we then wanted to disable the foreign key called fk_supplier, we could execute the following command:

ALTER TABLE products
DISABLE CONSTRAINT fk_supplier;

ORACLE/PLSQL: ENABLE A FOREIGN KEY

This Oracle tutorial explains how to enable a foreign key in Oracle with syntax and examples.

DESCRIPTION

You may encounter a foreign key in Oracle that has been disabled. You can enable the foreign key using the ALTER TABLE statement.

SYNTAX

The syntax for enabling a foreign key in Oracle/PLSQL is:

ALTER TABLE table_name
ENABLE CONSTRAINT constraint_name;

EXAMPLE

If you had created a foreign key as follows:

CREATE TABLE supplier
( supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50),
  CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);

CREATE TABLE products
( product_id numeric(10) not null,
  supplier_id numeric(10) not null,
  CONSTRAINT fk_supplier
    FOREIGN KEY (supplier_id)
    REFERENCES supplier(supplier_id)
);

If the foreign key had been disabled and we wanted to enable it, we could execute the following command:

ALTER TABLE products
ENABLE CONSTRAINT fk_supplier;

Discover Millions of Products your daily Need

Amazon::::Discover Millions of Products your daily Need

Amway Products in Hyderabad. All India Distributors (Certified Dietitian / Nutrition Advisor )

Amway Products in Kakinada. All India Distributors ( Certified Dietitian / Nutrition Advisor)

Monday, December 1, 2014

Oracle Tutorial 4

ORACLE/PLSQL: CREATE TABLE STATEMENT

DESCRIPTION

SYNTAX

Parameters or Arguments

EXAMPLE

PRACTICE EXERCISE #1:

Solution for Practice Exercise #1:

PRACTICE EXERCISE #2:

Solution for Practice Exercise #2:

PRACTICE EXERCISE #3:

Solution for Practice Exercise #3:

ORACLE/PLSQL: CREATE TABLE AS STATEMENT

DESCRIPTION

CREATE TABLE - BY COPYING ALL COLUMNS FROM ANOTHER TABLE

Syntax

Example

CREATE TABLE - BY COPYING SELECTED COLUMNS FROM ANOTHER TABLE

Syntax

Example

CREATE TABLE - BY COPYING SELECTED COLUMNS FROM MULTIPLE TABLES

Syntax

Example

FREQUENTLY ASKED QUESTIONS

ORACLE/PLSQL: ALTER TABLE STATEMENT

DESCRIPTION

ADD COLUMN IN TABLE

Syntax

Example

ADD MULTIPLE COLUMNS IN TABLE

Syntax

Example

MODIFY COLUMN IN TABLE

Syntax

Example

MODIFY MULTIPLE COLUMNS IN TABLE

SyntaX

Example

DROP COLUMN IN TABLE

Syntax

Example

RENAME COLUMN IN TABLE(NEW IN ORACLE 9I RELEASE 2)

Syntax

Example

RENAME TABLE

Syntax

Example

PRACTICE EXERCISE #1:

Solution for Practice Exercise #1:

PRACTICE EXERCISE #2:

Solution for Practice Exercise #2:

PRACTICE EXERCISE #3:

Solution for Practice Exercise #3:

PRACTICE EXERCISE #4:

Solution for Practice Exercise #4:

PRACTICE EXERCISE #5:

Solution for Practice Exercise #5:

PRACTICE EXERCISE #6:

Solution for

ORACLE/PLSQL: DROP TABLE STATEMENT

DESCRIPTION

SYNTAX

Parameters or Arguments

NOTE

EXAMPLE

Purge

ORACLE/PLSQL: VIEW

WHAT IS A VIEW IN ORACLE?

CREATE VIEW

Syntax

Example

UPDATE VIEW

Syntax

Example

DROP VIEW

RENAME COLUMN IN TABLE
(NEW IN ORACLE 9I RELEASE 2)