Constraints in Oracle

Constraints:

Data integrity of the database must be maintained. In order to ensure data has integrity we have to implement certain rules or constraints. As these constraints are used to maintain integrity they are called as integrity constraints.

Constraints can be given at two different levels. If the constraint is related to a single column the constraint is given at the column level otherwise constraint is to be given at the table level. Base on the where a constraint is given, constraint are of two types:

1. Column Constraints
2. Table Constraints

Column Constraint:

A constraint given at the column level is called as Column Constraint. It defines a rule for a single column. It cannot refer to column other than the column at which it is defined. A typical example is PRIMARY KEY constraint when a single column is the primary key of the table.

Table Constraint:

A constraint given at the table level is called as Table Constraint. It may refer to more than one column of the table. A typical example is PRIMARY KEY constraint that is used to define composite primary key. A column level constraint can be given even at the table level, but a constraint that deals with more than one column must be given only at the table level. The following is the syntax of CONSTRAINT clause used with CREATE TABLE and ALTER TABLE commands.

[CONSTRAINT constraint] { [NOT] NULL | {UNIQUE | PRIMARY KEY} | REFERENCES [schema.] table [(column)] [ON DELETE CASCADE] | CHECK (condition) }

The following is the syntax of table constraint.

[CONSTRAINT constraint] { {UNIQUE | PRIMARY KEY} (column [,column] ...) | FOREIGN KEY (column [,column] ...) REFERENCES [schema.] table [(column [,column] ...)] [ON DELETE CASCADE] | CHECK (condition) }

The main difference between column constraint and table constraint is that in table constraint we have to specify the name of the column for which the constraint is defined whereas in column constraint it is not required as constraint is given on immediately after the column.

Creating Integrity Constraints
In the following few sections we will see how to integrity constraints.
NOT NULL Constraint:
Used to prevent any null value from entering into column. This is automatically defined forcolumn with PRIMARY KEY constraint.
CREATE TABLE COURSES( ...,name varchar2(20) CONSTRAINT courses_name_nn NOT NULL,.. );
CONSTRAINT option is used to given a name to constraint. The convention followed here is TABLENAME_COLUMN_TYPE.


PRIMARY KEY Constraint This constraint is used to define the primary key of the table. A primary key is used to uniquely identify rows in a table. There can be only one primary key in a table. It may consist of more than one column. If primary key is consisting of only one column, it can be given as column constraints otherwise it is to be given as table constraint. 
Note: You have to use table constraint to define composite primary key. Oracle does the following for the column that has PRIMARY KEY constraint .Creates a unique index to enforce uniqueness. 
CREATE TABLE COURSES( ccode varchar2(5) CONSTRAINT courses_pk PRIMARY KEY,... );
The following example shows how to define composite primary key using PRIMARY KEY constraint at the table level.
CREATE TABLE COURSE_FACULTY( ...,CONSTRAINT COURSE_FACULTY_PK PRIMARY KEY (ccode,faccode) );


UNIQUE Constraint Enforces uniqueness in the given column(s). Oracle automatically creates a unique index for this column.
CREATE TABLE courses ( ... ,name varchar2(20) CONSTRAINT courses_name_u UNIQUE, ... );
If two or more columns collective should be unique then UNIQUE constraint must be given at the table level.


FOREIGN KEY Constraint A foreign key is used to join the child table with parent table. FOREIGN KEY constraint is used to provide referential integrity, which makes sure that the values of a foreign key are derived from parent key. It can be defined either at the table level or at the column level. If a foreign key is defined on the column in child table then Oracle does not allow the parent row to be deleted, if it contains any child rows. However, if ON DELETE CASCADE option is given at the time of defining foreign key, Oracle deletes all child rows while parent row is being deleted.
CREATE TABLE course_faculty (ccode varchar2(5) CONSTRAINT course_faculty_ccode_fk REFERENCES courses(ccode), ... );
Note: When the name of the column in the referenced table is same as the foreign key then column need not be given after the table name. It means REFERENCES courses in the above example will suffice.
Table level constraint is used when foreign key is a composite foreign key.


ON DELETE CASCADE option As mentioned earlier, after a foreign key is defined, Oracle will NOT allow any parent row to be deleted if it has dependent rows in the child table. For example, if CCODE in COURSE_FACULTY table is defined as foreign key referencing CCODE column of COURSES table then it is NOT possible to delete rows from COURSES table if dependent rows exists in COURSE_FACULTY table. However, by using ON DELETE CASCADE it is possible to delete all child rows while parent row is being deleted.
CREATE TABLE course_faculty (ccode varchar2(5)CONSTRAINT course_faculty_ccode_fk REFERENCES courses(ccode) ON DELETE CASCADE, ..  );


CHECK Constraint Defines the condition that should be satisfied before insertion or updation is done.The condition used in CHECK constraint may NOT contain: A reference to pseudo column SYSDATE Subquery If it is given as column constraint, it can refer only to current column. But if it is given as table constraint, it can refer to more than one column of the table. In neither case it can refer to a column of other tables. The following example shows how to create CHECK constraint to make sure GRADE column of COURSE_FACULTY contains letters A, B and C only.
CREATE TABLE course_faculty( ..., grade char(1) CONSTRAINT course_faculty_grade_chk CHECK ( grade in (‘A’,’B’,’C’) ), ... );
The above CHECK constraint does not allow any other characters other than A, B and C. It must be noted that character comparison is always case sensitive. So to ignore case differences you can convert GRADE to uppercase before comparison made as follows:
CREATE TABLE course_faculty ( ..., grade char(1) CONSTRAINT course_faculty_grade_chk
CHECK ( upper(grade) in (‘A’,’B’,’C’) ), ... );
The following is an example of CHECK constraint at table level. The constraint makes sure the starting date (STDATE) of a batch is less than or equal to ending date (ENDDATE) of the batch.
CREATE TABLE batches ( ..., CONSTRAINT batches_dates_chk CHECK ( stdate <= enddate), );

SQL Operators

SQL Operators:

Apart from standard relational operators (= and >), SQL has some other operators that can

be used in conditions.

BETWEEN value-1 AND value-2 Checks whether the value is in the given range including the min and max values. This supports DATE type data also.The range is inclusive of the given values.

IN(list) Checks whether the value is matching with any one of the values given in the list. List contains values separated by comma(,). If the value is matching with any of the values given in the list then condition is taken as true.

LIKE pattern Checks whether the given string is matching with the given pattern.This operator is used to search for values when the exact value is not known. It selects rows that match the given pattern. The pattern can contain the following special characters.

IS NULL and IS NOT NULL Checks whether the value is null or not null.

To display the list of course where DURATION is in the range 20 to 25 days, enter:

select name from courses where duration between 20 and 25;

NAME

--------------------

Oracle database

C programming

Java Language

Note: BETWEEN.. AND is alternative to using >= and <= operators.

The following command will retrieve all courses where duration is either 20 or 30 days.

select name from courses where duration in (20,30);

NAME

--------------------

VB.NET

C programming

The same condition can be formed even without IN operator using logical operator OR as follows:

Select name from courses where duration = 20 or duration = 30;

However, it will be more convenient to user IN operator compared with multiple conditions compared with OR operator.

% Zero or more characters can take the place of %. _ (underscore) Any single character can take the place of underscore. But there must be one letter. To select the courses where the course name contains pattern .NET, enter:

select name,duration, fee from courses where name like '%.NET%'

NAME DURATION FEE

-------------------- --------- ---------

VB.NET 30 5500

ASP.NET 25 5000

The following example selects courses where second letter in the course code is “b” and column PREREQUISITE contains word “programming”.

select * from courses where ccode like '_b%' and prerequisite like '%programming%';

CCODE NAME DURATION FEE PREREQUISITE

----- -------------------- --------- --------- ------------------------

vbnet VB.NET 30 5500 Windows and programming

Remember LIKE operator is case sensitive. 

For example the following SELECT command will select all the courses where the column FEE is null.

select * from courses where fee is null;

Though Oracle provides NULL keyword, it cannot be used to check whether the value of a column is null. For example, the following condition will always be false as Oracle treats two null values as two different values.

select * from courses where fee = null;

The above command does NOT work as fee though contains null value will not be equal to NULL. SO, we must use IS NULL operator.

Selecting distinct values:

DISTINCT clause of SELECT command specifies only distinct values of the specified column must be selected. The following SELECT command will display only distinct course fee values from COURSES

table.

select distinct fee from courses;

FEE

---------

3500

4000

4500

5000

5500

Whereas the same query without DISTINCT clause will select the following. 

select fee from courses;

FEE

---------

4500

5500

3500

5000

4500

4000

Select Statement in Oracle

Select Statement:  SELECT command of SQL is used to retrieve data from one or more tables. It implements operators of relational algebra such as projection, and selection. The following is the syntax of SELECT command.

Syntax:

SELECT [DISTINCT | ALL] {* | table.* | expr } [alias ] [ {table}.*| expr } [alias ] ] ... FROM [schema.]object [, [schema.]object ] ... [WHERE condition] [ORDER BY {expr|position} [ASC |DESC]

[, {expr|position} [ASC | DESC]] ...]

schema is the name of the user whose table is being accessed. Schema prefix is not required if the table is in the current account. Schema prefix is required while we are accessing a table of some other account and not ours.

The following is an example of a basic SELECT command.

select * from courses;

CCODE    NAME     DURATION    FEE     PREREQUISITE

----- -------------------- --------- --------- -------------------------

ora             Oracledatabas      25          4500       Windows

vbnet          VB.NET             30           5500         Windows and programming

The simplest SELECT command contains the following:

Columns to be displayed. If * is given, all columns are selected.  The name of the table from where rows are to be retrieved.

Projection:

Projection is the operation where we select only a few columns out of the available columns.

The following is an example of projection.

select name,fee from courses;

NAME                FEE

-------------------- ---------

Oracle database   4500

VB.NET              5500

C programming     3500

Using expressions in SELECT command:

It is also possible to include expressions in the list of columns. For example, the following SELECT will display discount to be given for each course.

select name,fee, fee * 0.15 from courses;

NAME             FEE       FEE*0.15

-------------------- --------- ---------

Oracle database 4500      675

VB.NET            5500      825

C programming 3500       525

Column Alias:

The column heading of an expression will be the expression itself. However, as it may not be meaningful to have expression as the result of column heading, we can give an alias to the column so that alias is displayed as the column heading. The following example will use alias DISCOUNT for the expression FEE * 0.15.

select name, fee, fee * 0.15 DISCOUNT from courses

NAME               FEE     DISCOUNT

-------------------- --------- ---------

Oracle database   4500    675

VB.NET             5500     825

C programming   3500     525

ORDER BY clause:

It is possible to display the rows of a table in the required order using ORDER BY clause. It is used to sort rows on the given column(s) and in the given order at the time of retrieving rows. Remember, sorting takes place on the row that are retrieved and in no way affects the rows in the table. That means the order of the rows will remain unchanged.

Note: ORDER BY must always be the last of all clauses used in the SELECT command.

The following SELECT command displays the rows after sorting rows on course fee.

select name, fee from courses order by fee;

NAME               FEE

-------------------- ---------

Oracle database     4500

Java Language       4500

Note: Null values are placed at the end in ascending order and at the beginning in descending order.

The default order for sorting is ascending. Use option DESC to sort in the descending order. It is also possible to sort on more than one column. To sort rows of COURSES table in the ascending order of DURATION and descending order of FEE, enter:

select name, duration, fee from courses order by duration , fee desc;

NAME             DURATION    FEE

-------------------- --------- -         --------

Oracle database       25           4500

Java Language         25           4500

First, all rows are sorted in the ascending order of DURATION column. Then the rows that have same value in DURATION column will be further sorted in the descending order of FEE column.

Using column position:

Instead of giving the name of the column, you can also give the position of the column on which you want to sort rows. For example, the following SELECT sorts rows based on discount to be given to each course.

select name, fee, fee * 0.15 from courses order by 3;

NAME               FEE     FEE*0.15

-------------------- --------- ---------

Oracle database    4500    675

Java Language      4500    675

Note: Column position refers to position of the column in the selected columns and not the position of the column in the table. The above command uses column position in ORDER BY clause. Alternatively you can use column alias in ORDER BY clause as follows:

select name, fee, fee * 0.15 discount from courses order by discount;

NAME              FEE       DISCOUNT

-------------------- --------- ---------

Oracle database   4500    675

Java Language     4500    675

Selection:

It is possible to select only the required rows using WHERE clause of SELECT command. It implements selection operator of relational algebra. WHERE clause specifies the condition that rows must satisfy in order to be selected. The following example select rows where FEE is more than or equal to 5000.

select name, fee from courses where fee >= 5000

NAME                FEE

-------------------- ---------

VB.NET             5500

ASP.NET           5000

The following relational and logical operators are used to form condition of WHERE clause. Logical operators – AND, OR – are used to combine conditions. NOT operator reverses the result of the condition. If condition returns true, NOT will make the overall condition false. The following SELECT command displays the courses where duration is more than 15 days and course fee is less than 4000.

select * from courses where duration > 15 and fee < 4000;

CCODE NAME DURATION FEE PREREQUISITE

----- -------------------- --------- --------- -------------------

c C programming 20 3500 Computer Awareness

The following SELECT command retrieves the details of course with code ORA.

select * from courses where ccode = 'ora';

CCODE NAME DURATION FEE PREREQUISITE

----- -------------------- --------- --------- ----------------

ora Oracle database 25 4500 Windows

Note: When comparing strings, the case of the string must match. Lowercase letters are not equivalent to uppercase letters.