Data Constraints

by E-Computer Concepts September 28, 2019 at 2:00 pm

Oracle allows data constraints to be attached to columns with the help of SQL syntax that checks data for integrity prior storage. Once data constraints are part of a table column construct, the oracle database engine checks for the data being entered into a table column against the data constraints. If the data passes this check, it is stored in the table column, else the data is rejected. So a constraints refers to a condition or a check that is applied to a column or set of columns in a table. In other words constraints are used to maintain integrity of data known as integrity constraints.

Two basic types of constraints are column constraints and table constraints. The column constraints are applied only to individual columns whereas group constraints are applied to groups of one or more columns.

Declaring Constraints

Column constraints are declared to the end of column definition after the data type and before the comma. Table constraints are placed at the end of the table definition after the last column definition, but before the closing parenthesis. The syntax for CREATE TABLE command, expanded to include constraints.

CREATE TABLE <tablename>
(<column name><data type> <column constraints>,
<column name><data type> <column constraints>…..
<table constraints>(<column name>[,<column name>….])…);

Types of Constraints

Oracle provide various type of constraints as follows:

  • NOT NULL Constraint
  • UNIQUE Constraint
  • PRIMARY KEY Constraint
  • CHECK Constraint
  • FOREIGN KEY Constraint

NOT NULL Constraint

NOT NULL Constraint is used to prevent a field from allowing NULL values i.e. the column can never have empty values. NULLS are special designation that mark the column a empty. But sometimes you want to ensure against some columns like primary key should never be NULL. Fields like NAME should be required to have definite values. So if you place NOT NULL immediately after the data type including size of a column, any attempt to put NULL values in that field will be rejected. Otherwise SQL will assume that NULLS are permitted.

When a column is defined as NOT NULL, then that column becomes a mandatory column. It implies that a value must be entered into the column if the record is to be accepted for storage in the table. The syntax is :

<column name> <data type> ( <size> ) NOT NULL

For example

CREATE TABLE Student
(
Stu-id Integer NOT NULL,
Roll No Integer NOT NULL,
Name Char(20) NOT NULL,
Class Char(10),
Subjects Char(15),
Percent Integer
);

It is important to remember that any column with NOT NULL constraints must be assigned values in every INSERT clause. In the above table student, columns Stu-id, RollNo, Name are followed by keywords NOT NULL. Any attempt to put NULL values in these columns will be rejected.

UNIQUE Constraint

Unique Constraint is used when you want to make sure that all of the values entered into a column are different from one another. For example, UNIQUE constraint applied on stu-id of student table ensure that no rows have the same stu-id value. This constraint can be applied only to field that have also been declared NOT NULL.

The syntax is:-

<column name> <data type> (< size >) UNIQUE

For example :

CREATE TABLE Student
(
Stu-id Integer NOT NULL UNIQUE
Roll No Integer NOT NULL UNIQUE
Name Char(20) NOT NULL,
Class Char(10),
Subjects Char(15),
Percent Integer
);

After application of this constraint, all subsequent SQL statements are executed ensuring that the conditions posed by this constraint should be met.

PRIMARY KEY Constraint

The primary key constraint declares a column as the primary key of the table. It is functionally the same as the UNIQUE constraint, except that only one primary key can be defined for a given table. primary keys can’t allow NULL values, this means that like fields in UNIQUE constraints, any field used in a PRIMARY KEY Constraint must already be declared NOT NULL.

The syntax is:

<column name> <data type (<size>) NOT NULL PRIMARY KEY;

For example, Primary key can be applied on the field stu-id student table, as follows :

CREATE TABLE Student
(
Stu-id Integer PRIMARY KEY,
Roll No Integer NOT NULL,
Name Char(20) NOT NULL,
Class Char(10),
Subjects Char(15),
Percent Integer
);

As you see that UNIQUE field is also declared in the same table. It is best to put the PRIMARY KEY constraint on the field(s) that will constitute your UNIQUE row identifier and UNIQUE constraint should be for those fields that supposed to be UNIQUE for logical reasons such as phone number, name etc.

DEFAULT Constraint

DEFAULT clause is used to specify a default value for a column. If the user does not enter a value for a column then automatically default value is inserted in the field. The syntax is :

<column name> <data type> (<size>) DEFAULT = <default value>

There can be any number of restrictions that you can place on the data entered into your tables. For this purpose, SQL provides CHECK constraint, which allows you to define a condition that a value entered into the table has to satisfy before it can be accepted.

For example :

CREATE TABLE Student
(
Stu-id Integer PRIMARY KEY
Roll No Integer NOT NULL, UNIQUE
Name Char(20) NOT NULL,
Class Char(10),
Subjects Char(15), DEFAULT = ‘SCIENCE’,
Percent Integer
);

FOREIGN KEY Constraint

When all of the values in one field of a table have to be present in a field of another table, then you can say first table refers to or references the second. It indicates a direct relationship between the meaning of the two fields. For example, the students in the student table, each have assigned an Id (identification) number and in other table student-rec, against each stu-id, there is a record for that student. So there is a link between these two tables. Let us consider the following two tables:

Student (Stu-id, RollNo, Name, Class, Subject, Percentage)
Student-rec (RollNo, City, State, Pincode)

Both the above said tables are related through common column RollNo. The column RollNo is primary key in student-rec table and can be declared as foreign key in student table. So this concept enforce the referential integrity. The syntax of the FOREIGN KEY constraint is

<column name> <Data type> ( <size> ) REFERENCES
<PK table> [<column list>]

Here PKtable is the table containing the parent key. Both the tables should be created as follows :

CREATE TABLE Student-rec
(ROLLNO integer NOT NULL PRIMARY KEY,
………………………………………..
……………………………………….
……………………………………….);

CREATE TABLE Student
(Stu_id Integer Not Null Primary Key,
RollNo Integer References student-rec (RollNo)
……………………………………….
………………………………………
………………………………………);

Maintaining referential integer imposes some restriction, on the values that can be present in field declared as foreign and present keys. The parent key must be defined in such a way that each foreign-key value will match one specific row. This means that it must be unique and contain no NULL value.

The foreign key may contain only values that are actually present in the parent key or NULLs. Any other values you attempt to enter into the key will be rejected i.e. any value you put into these fields with an INSERT or UPDATE command must already be present in their parent keys. If you want to change or remove the current referenced parent-key value, there are essentially three possibilities.

  • You can restrict the change which means the change on the parent key is restricted.
  • You can make the change in the parent key and have that same change made in the foreign key automatically, which means the change cascades.
  • You can make the change in the parent key and set the foreign key to NULL automatically, which is to say the change Nulls the foreign key.

Add Comment