Input Output Constraints in Oracle

"Create table" and "alter table" are the command which can be used to attach the constraints onto the table. Oracle permits the data constraints to be attached to table column via these "SQL" query. Once data constraints are attached with the table then the oracle data base engine checks the data being entered into the table column against the data constraints. If the data passes these data constraints then the data will be stored into the table otherwise the data is rejected. Even if you want to store a single column of record and the data will not satisfy the constraints then the whole record will be rejected and the record is not stored din the table.

Once the constraints are attached on the table then the "insert" and "update" statements will automatically apply these constraints and data I checked for the conditions prior to insert the data in table. Two type if constraints are there:

· Input output constraint and;

· Business rule constraint

Here you will study about the input output constraint.

Primary key constraint

Primary key is the column or a set of records which can identify the tuple uniquely. None of the row can have the "null" values. A table can have only one primary key. Features of primary key are:

· The main purpose of primary key is to have record uniqueness.

· Primary key will not allow having duplicate value in the record.

· Null values are also not allowed in primary key record.

· It is not compulsory to have a primary key but it is recommended.

· Primary key also helps in relating the table with other tables.

· The table can have only one primary key.

· Only up to 16 columns can be combined to have a composite key.

The syntax for primary key at column level is:

() primary key

Example:

Create table student (roll_no number(4) primary key, name varcher2(30), fees number (5), marks number (4));

Now if you try to insert duplicate data into the column roll_no then the error will be raised by the oracle engine.

The primary key defined at table level is like:

Primary key (, )

Example:

Create table student (roll_no number(4) , name varcher2(30), fees number (5), marks number (4), primary key (roll_no));

This will also works as above. Any violation will be raised as error by oracle engine.

Foreign key

This constraint represents relationship between tables. A foreign key is a column which derives all its values from the primary key of another table. The table from which the column derives its value is called master table and the table which derive the values is known as detail table. Features of foreign keys are:

· Foreign key can be in the same table also.

· Foreign key is a reference key.

· Parent is always a primary key.

· Parent record can be deleted until the record from the child table

· Master table an/t be updated until the child is not updated

Syntax for foreign key at column level is:

() reference [()] [on delete cascade]

Example:

Create table class (roll_no number(5) foreign key references university (reg_no), name varchar2(30), fees number(5), marks number(5));

Here roll_no is the foreign key from the table university and from column reg_no.

Syntax for foreign key at table level is:

Foreign key() references {, )

Example:

Create table class (roll_no number(5), name varchar2(30), fees number(5), marks number(5), Foreign key roll_no references university (reg_no));

It is also same as above syntax.

Foreign key with "on delete cascade"

The "on delete cascade" is the tag with foreign key. It will simply set the null values in the child table when the records from the parent table are deleted. The record in the child table is not deleted.

Syntax:

Foreign key references () on delete cascade

Unique key constraint

Unique key constraint is the constraint which is similar to the primary key constraint but permits null value to enter into the record. It means that the unique key constraint permits the null value entry but it doesn't allow the duplicate values into the table. Features of unique key constraint are:

· It does not allow the duplicate values.

· Unique index is created automatically.

· You can have only one primary key in the table but you can have more than one unique key in a table.

· 16 columns can be combined to have a unique key.

Syntax of unique key constraint at column level is:

() unique

Example:

Create table employee (empid varchar2(3) unique, sal number(5), name varchar2(30), address varchar2(30));

So now you can't repeat the values but can have null values.

Syntax of unique key constraint at table level:

Unique (, )

Example:

Create table employee (empid varchar2(3), number(5), name varchar2(30), address varchar2(30), unique key empid);

So we have primary key, foreign key and unique key constraint in a table. The data through insert and modify command is first passes all the constraint and if the data is valid the record is entered else an error is raised.