Constraints are rules you can define in your Oracle tables to restrict the type of data you can place in the tables.


 
Two methods exist for defining constraints: the table constraint method and the column constraint method. The constraint is defined as a table constraint if the constraint clause syntax appears after the column and datatype definitions. The constraint is defined as a column constraint if the constraint definition syntax appears as part of an individual column’s definition. All constraints can be defined either as table constraints or as column constraints, with two exceptions:
  •       Not NULL constraints can only be defined as column constraints.
  •       Primary keys consisting of two or more columns (known also as composite primary keys) can only be defined as table constraints. However, single-column primary keys can be defined either as column or table constraints.
Primary Key
A constraint of this type identifies the column or columns whose singular or combined values identify uniqueness in the rows of your Oracle table. Every row in the table must have a value specified for the primary key column(s).
SQL> create table employee
(
 empid varchar2(5)  constraint pk_employee_01 primary key,
 lastname varchar2(25),
 firstname varchar2(25),
 salary number(10,4)
);
      //column constraint method
 
Or cab be simplified as
create table employee
(

 empid varchar2(5)  primary key,
 lastname varchar2(25),
 firstname varchar2(25),
 salary number(10,4)
);
      //column constraint method
Each primary key constraint was given a meaningful name when defined. Oracle strongly recommends that you give your constraints meaningful names in this way so that you can easily identify the constraint later
 create table employee
 (
 empid varchar2(5),
 lastname varchar2(25),
 firstname varchar2(25),
 salary number(10,4),
 constraint pk_employee_01  primary key (empid)
);
     //table constraint method

Composite Primary Keys
So that you understand the nature of composite primary keys in Oracle for OCP, the following code block shows how to define a composite primary key:
create table names
(
firstname varchar2(10),
lastname varchar2(10),
constraint pk_names_01 primary key (firstname, lastname)
);

Defining Foreign Key Constraints
To help you understand how to define foreign key constraints, let’s think in terms of an example. Let’s say we have our own table called DEPARTMENT, which we just created in the last code block. It lists the department number, name, and location for all departments in our own little company. Let’s also say that we want to create our EMPLOYEE table with another column, called DEPARTMENT_NUM. Because there is an implied parent-child relationship between these two tables with the shared column, let’s make that relationship official by using a foreign key constraint, shown in bold in the following block:
create table employee
(

empid varchar2(5) primary key,
lastname varchar2(25),
firstname varchar2(25),
department_num number(5) references department(department_num) on delete set null,
salary number(10,4)
);
  //references table_name(column_name) on delete set null,

For a foreign-key constraint to be valid, the same column appearing in both tables must have exactly the same datatype. You needn’t give the columns the same names, but it’s a good idea to do so. The foreign key constraint prevents the DEPARTMENT_NUM column in the EMP table from ever storing a value that can’t also be found in the DEPT table. The final clause, on delete set null, is an option relating to the deletion of data from the parent table. If someone attempts to remove a row from the parent table that contains a referenced value from the child table, Oracle sets all corresponding values in the child to NULL. The other option is on delete cascade, where Oracle allows remove all corresponding records from the child table when a referenced record from the parent table is removed.
Unique Key Constraints
Let’s say we also want our employee table to store social security or government ID information. The definition of a UNIQUE constraint on the GOVT_ID column prevents anyone from defining a duplicate government ID for any two employees in the table. Take a look at the following example, where the unique constraint definition is shown in bold:
create table employee
(
empid varchar2(5) primary key,
lastname varchar2(25),
firstname varchar2(25),
govt_id number(10) unique,
salary number(10,4),
department_num number(5) references department (department_num),
);

Defining Other Types of Constraints
The last two types of constraints are not NULL and CHECK constraints. By default, Oracle allows columns to contain NULL values. The not NULL constraint prevents the data value defined by any row for the column from being NULL. By default, primary keys are defined to be not NULL. All other columns can contain NULL data, unless you explicitly define the column to be not NULL. CHECK constraints allow Oracle to verify the validity of data being entered on a table against static criteria. For example, you could specify that the SALARY column cannot contain values over $250,000. If someone tries to create an employee row with a salary of $1,000,000 per year, Oracle would return an error message saying that the record data defined for the SALARY column has violated the CHECK constraint for that column. Let’s look at a code example where both not NULL and check constraints are defined in bold:
create table employee
(
empid varchar2(5) primary key,

department_num number(5) references department (department_num),
lastname varchar2(25) not null,
firstname varchar2(25) unique,
salary number(10,4) check (salary <=250000),
govt_id number(10) unique
);

Adding Integrity Constraints to Existing Tables

Another constraint-related activity that you may need to do involves adding new constraints to an existing table. This can be easy if there is no data in the table already, but it can be a nightmare if data already exists in the table that doesn’t conform to the constraint criteria. The simplest scenario for adding the constraint is to add it to the database before data is inserted. Take a look at the following code block:

SQL> create table employee
  2  (empid varchar2(5),
  3   lastname varchar2(25),
  4   firstname varchar2(25),
  5   salary number(10,4),
  6   department_num number(5),
  7   govt_id number(10));
Table created.
alter table employee
add constraint pk_employee_01 primary key (empid);

alter table employee
add constraint fk_employee_01 foreign key (department_num) references department (department_num);
alter table employee
add constraint ck_employee_01 check (salary <=250000);
alter table employee
add constraint uk_employee_01 unique (govt_id);
alter table employee modify
(lastname not null);

Disabling Constraints
A constraint can be turned on and off. When the constraint is disabled, it will no longer do its job of enforcing rules on the data entered into the table. The following code block demonstrates some sample statements for disabling constraints:
alter table employee
disable primary key;

alter table employee

disable constraint uk_employee_01;

You may experience a problem if you attempt to disable a primary key when existing foreign keys depend on that primary key. This problem is shown in the following situation:
Enabling a Disabled Constraint
When the constraint is later enabled, the rules defined for the constraint are once again enforced, rendering the constraint as effective as it was when it was first added to the table. You can enable a disabled constraint as follows:

alter table department
enable primary key;

alter table employee
enable uk_employee_01;

Removing Constraints
Usually, there is little about a constraint that will interfere with your ability to remove it, so long as you either own the table or have been granted appropriate privileges to do so. When a constraint is dropped, any index associated with that constraint (if there is one) is also dropped. Here is an example:
alter table employee

drop unique (govt_id);

alter table employee

drop primary key cascade;

alter table employee
drop constraint ck_employee_01;

An anomaly can be found when disabling or dropping not NULL constraints. You cannot disable a not NULL constraint, per se—a column either accepts NULL values or it doesn’t. Therefore, you must use the alter table modify clause in all situations where the not NULL constraints on a table must be added or removed. Here’s an example:
alter table employee
modify (lastname null);
alter table employee
modify (lastname not null);

Global temporary tables
Global temporary tables are distinct within SQL sessions.
The basic syntax is:
    CREATE GLOBAL TEMPORARY TABLE table_name ( …);
For example:
    CREATE GLOBAL TEMPORARY TABLE supplier
    (     supplier_id     numeric(10)     not null,
        supplier_name     varchar2(50)     not null,
        contact_name     varchar2(50)    
    )            
This would create a global temporary table called supplier .
Local Temporary tables
Local temporary tables are distinct within modules and embedded SQL programs within SQL sessions.
The basic syntax is:
    DECLARE LOCAL TEMPORARY TABLE table_name ( …);
Views are queries stored in Oracle that dynamically assemble data into a virtual table. To the person using the view, manipulating the data from the view is just like manipulating the data from a table. In some cases, it is even possible for the user to change data in a view as though the view were a table. Let’s now explore the topic of creating, using, and managing views in more detail.

Creating a VIEW

The syntax for creating a VIEW is:
    CREATE VIEW view_name AS
    SELECT columns
    FROM table
    WHERE predicates;
A view will not be created if the base table you specify does not exist. However, you can overcome this restriction by using the force keyword in the create view command. This keyword forces Oracle to create the view anyway. However, the view will be invalid because no underlying table data is available to draw from.
For example:
Create VIEW XX_PO_DETAILS_v AS
Select a.PO_ID, a.PO_NUMBER, b.ITEM_NAME, b.NEED_DATE
From XX_PO_HEADERS_ALL a, XX_PO_LINES_ALL b
Where a.PO_ID=b.PO_ID

This would create a virtual table based on the result set of the select statement. You can now query the view as follows:
    SELECT *
    FROM  XX_PO_DETAILS_vs;

Creating Views That Enforce Constraints
Tables that underlie views often have constraints that limit the data that can be added to those tables. As I said earlier, views cannot add data to the underlying table that would violate the table’s constraints. However, you can also define a view to restrict the user’s ability to change underlying table data even further, effectively placing a special constraint for data manipulation through the view. This additional constraint says that insert or update statements issued against the view are cannot create rows that the view cannot subsequently select. In other words, if after the change is made, the view will not be able to select the row you changed, the view will not let you make the change. This viewability constraint is configured when the view is defined by adding the with check option to the create view statement. Let’s look at an example to clarify my point:

 create or replace view emp_view as
 (select empno, ename, job, deptno
   from emp
   where deptno = 10)
  with check option constraint emp_view_constraint;

Updating a VIEW
You can update a VIEW without dropping it by using the following syntax:
    CREATE OR REPLACE VIEW view_name AS
    SELECT columns
    FROM table
    WHERE predicates;
For example:
    CREATE or REPLACE VIEW sup_orders AS
    SELECT suppliers.supplier_id, orders.quantity, orders.price
    FROM suppliers, orders
    WHERE suppliers.supplier_id = orders.supplier_id
    and suppliers.supplier_name = ‘Microsoft’;
Dropping a VIEW
The syntax for dropping a VIEW is:
    DROP VIEW view_name;
For example:
    DROP VIEW sup_orders;
Creating Simple Views That Can’t Change Underlying Table Data
In some cases, you may find that you want to create views that don’t let your users change data in the underlying table. In this case, you can use the with read only clause. This clause will prevent any user of the view from making changes to the base table. Let’s say that after reprimanding SCOTT severely for calling him a fool, KING wants to prevent all employees from ever changing data in EMP via the EMP_VIEW again. The following shows how he would do it:
create or replace view emp_view
 as (select * from emp)
 with read only;

Frequently Asked Questions
Question:  Can you update the data in a view?
Answer:  A view 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 a view providing you have the proper privileges to the underlying tables.
Question: Does the 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 view is based on) is dropped from the database. However, if you try to query the view after the table has been dropped, you will receive a message indicating that the view has errors.If you recreate the table (that you had dropped), the view will again be fine.
The DROP TABLE statement allows you to remove a table from the database.
The basic syntax for the DROP TABLE statement is:
    DROP TABLE table_name;
For example:  DROP TABLE XX_supplier;
This would drop table called XX_supplier.
Sometimes objects are associated with a table that exists in a database along with the table. These objects may include indexes, constraints, and triggers. If the table is dropped, Oracle automatically drops any index, trigger, or constraint associated with the table as well. Here are two other factors to be aware of with respect to dropping tables:

  1. You cannot roll back a drop table command.
  2. To drop a table, the table must be part of your own schema, or you must have the drop any table privilege granted to you.

Truncating Tables
Let’s move on to discuss how you can remove all data from a table quickly using a special option available in Oracle. In this situation, the DBA or developer may use the truncate table statement. This statement is a part of the data definition language (DDL) of Oracle, much like the create table statement and completely unlike the delete statement. Truncating a table removes all row data from a table quickly, while leaving the definition of the table intact, including the definition of constraints and any associated database objects such as indexes, constraints, and triggers on the table. The truncate statement is a high-speed data-deletion statement that bypasses the transaction controls available in Oracle for recoverability in data changes. Truncating a table is almost always faster than executing the delete statement without a where clause, but once this operation has been completed, the data cannot be recovered unless you have a backed-up copy of the data. Here’s an example:
SQL> truncate table tester;
Table truncated.

The ALTER TABLE statement allows you to rename an existing table. It can also be used to add, modify, or drop a column from an existing table.
Renaming a table
The basic syntax for renaming a table is:
    ALTER TABLE table_name
     RENAME TO new_table_name;

For example:
    ALTER TABLE suppliers
     RENAME TO vendors;
This will rename the suppliers table to vendors.
Adding column(s) to a table
Syntax #1
To add a column to an existing table, the ALTER TABLE syntax is:
    ALTER TABLE table_name
     ADD column_name column-definition;

For example:
    ALTER TABLE supplier
     ADD supplier_name  varchar2(50);
This will add a column called supplier_name to the supplier table.
Syntax #2
To add multiple columns to an existing table, the ALTER TABLE syntax is:
    ALTER TABLE table_name
    ADD (column_1     column-definition,
          column_2     column-definition,
          …    
          column_n     column_definition );

Drop column(s) in a table
Syntax #1
To drop a column in an existing table, the ALTER TABLE syntax is:
    ALTER TABLE table_name
     DROP COLUMN column_name;
For example:
    ALTER TABLE supplier
     DROP COLUMN supplier_name;

Modifying column(s)(datatypes) in a table

Syntax #1
To modify a column in an existing table, the ALTER TABLE syntax is:
    ALTER TABLE table_name
     MODIFY column_name column_type;


For example:
    ALTER TABLE supplier
     MODIFY supplier_name   varchar2(100)     not null;

This will modify the column called supplier_name to be a data type of varchar2(100) and force the column to not allow null values.
Syntax #2
To modify multiple columns in an existing table, the ALTER TABLE syntax is:
    ALTER TABLE table_name
    MODIFY (     column_1     column_type,
          column_2     column_type,
          …    
          column_n     column_type );
Rename column(s) in a table
(NEW in Oracle 9i Release 2)
Syntax #1
Starting in Oracle 9i Release 2, you can now rename a column.
To rename a column in an existing table, the ALTER TABLE syntax is:
    ALTER TABLE table_name
     RENAME COLUMN old_name to new_name;