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. 

Insert Command in Oracle

Insert command:

Inserting rows into a table

 SQL command INSERT is used to insert new row into the table. While inserting rows, you may enter value for each column of the table or selected columns. The following command inserts a row into COURSES table.

Syntax: insert into courses values('ora','Oracle database',25,4500,'Knowledge of Windows');


Note: After inserting the required row, issues COMMIT command to make sure the changes are made permanent.  Without COMMIT, rows that are inserted might be lost if there is any power failure. During insertion, character values are enclosed in single quotes. Unless otherwise specified we have to supply a value for each column of the table. If the value of any column is not known or available then you can give NULL as the value of the column. For example, the following insert will insert a new row with null value for PREREQUISITE column.

insert into courses values('c','C Programming',25,3000,null);

Note: INSERT command can insert only one row at a time. For multiple row, INSERT command must be issued for multiple times. DATE type values must be in the format DD-MON-YY or DD-MON-YYYY, where MON is the first three letters of the month (Jan, Feb). If only two digits are given for year then current century is used. For example, if you give 99 for year, Oracle will take it as 2099 as the current century is 2000. So it is important to remember this and give four digits if required. The following is the complete syntax for INSERT command.

INSERT INTO tablename [(columns list)] {VALUES (value-1,...) | subquery }


Inserting a row with selected columns:
It is possible to insert a new row by giving values only for a few columns instead of giving values for all the available columns. The following INSERT command will insert a new row only two values.

insert into courses(ccode,name) values ('odba','Oracle Database Administration');

The above command will create a new row in COURSES table with values for only two columns CCODE and NAME. The remaining columns will take NULL value or the default value, if the column is associated with default value.

DataTypes in Oracle

Datatypes:

Each column of the table contains the datatype and maximum length, if it is length is applicable. Datatype of the column specifies what type of data can be stored in the column. The datatype VARCHAR2 is to store strings that may have different number of characters, NUMBER is used to store numbers. The maximum length, which is given in parentheses after the datatype, specifies how many characters (or digits) the column can store at the most. For example, column VARCHAR2 (20) would mean it can store up to 20 characters.

Datatype Description:

VARCHAR2( len): Can store up to len number of characters. Each character would occupy one byte. Maximum width is 4000 characters.VARCHAR(len) Same as VARCHAR2. But use VARCHAR2 as Oracle might change the usage of VARCHAR in future releases. 

CHAR(len): Fixed length character data. If len is given then it can store up to len number of characters. Default width is 1. String is padded on the right with spaces until string is of len size. Maximum width is 2000.

NUMBER: Can store numbers up to 40 digits plus decimal point and sign. NUMBER (p ,s) P represents the maximum significant digits allowed. S is the number of digits on the right of the decimal point.

DATE: Can store dates in the range 1-1-4712 B.C to 31-12-4712AD.

LONG: Variable length character values up to 2 gigabytes. Only one LONG column is allowed per table. You cannot use LONG datatype in functions, WHERE clause of SELECT, in indexing and subqueries.

RAW and LONG RAW Equivalent to VARCHAR2 and LONG respectively, but used for storing byte-oriented or binary data such as digital sound or graphics images.

CLOB, BLOB, NCLOB Used to store large character and binary objects. Each can accommodate up to 4 gigabytes. 

BFILE Stores a pointer to an external file. The content of the file resides in the file system of the operation system. Only the name of the file is stored in the column.

ROWID Stores a unique number that is used by Oracle to uniquely identify each row of the table.

NCHAR (size) Same as CHAR, but supports national language.

NVARCHAR2 (size) Same as VARCHAR2, but supports national language.

Create Command IN DBMS

Creating a Table:

A Table is a collection of rows and columns. Data in relational model is stored in tables. Before a table is created the following factors of a table are to be finalized.

1. What data table is supposed to store.
2. The name of the table. It should depict the content of the table.
3. What are the columns that table should contains
4. The name, data type and maximum length of each column of the table.
5. What are the rules to be implemented to main data integrity of the table.

The following is an example of creation of COURSES table.  The following CREATE TABLE command is used to create COURSES table.

SQL> create table COURSES ( ccode varchar2(5),name varchar2(30),duration number(3),fee number(5), prerequisite varchar2(100));

Table Created

Rules to be followed for names:

The following are the rules to be followed while naming an Oracle Object. These rulesare applicable for name of the table and column.

1. The name must begin with a letter - A-Z or a-z.
2. Letters, digits and special characters – underscore (_), $ and # are allowed.
3. Maximum length of the name is 30 characters.
4. It must not be an SQL reserved word.
5. There should not be any other object with the same name in your account.

DESCRIBE:

You can display the structure of a table using SQL*PLUS command DESCRIBE. It displays then name, datatype and whether the column can store null value for each column of the table.

Syntax:  DESC[RIBE] objectname

Displays the column definitions for the specified object. The object may be a table, view,

synonym, function or procedure.

To display the structure of COURSES table, enter:

SQL> DESC COURSES

Name                           Null? Type

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

CCODE                    NOT NULL VARCHAR2(5)

NAME                       VARCHAR2(30)

DURATION                 NUMBER(3)

FEE                              NUMBER(5)

PREREQUISITE           VARCHAR2(100)

DESCRIBE is an SQL*Plus command and can be abbreviated to DESC.

What is SQL and SQL*plus?

Structured Query Language (SQL):

Almost all relational database management systems use SQL (Structured QueryLanguage) for data manipulation and retrieval. SQL is the standard language forrelational database systems. SQL is a non-procedural language. 

SQL Commands are divided into four categories, depending upon what they do.

DDL (Data Definition Language)

DML (Data Manipulation Language)

DCL (Data Control Language)

Query (Retrieving data)

DDL commands are used to define the data. For example, CREATE TABLE.

DML commands such as, INSERT and DELETE are used to manipulate data.

DCL commands are used to control access to data. For example, GRANT.

Query is used to retrieve data using SELECT.

DML and Query are also collectively called as DML. And DDL and DCL are called as

DDL.



 SQL commands include commands of ANSI/ISO SQL and extra commands added to ANSI SQL by oracle.The following are the rules to be followed while entering SQL commands.
1. An SQL statement may be entered in multiple lines.
2. It is not possible to break a word across lines.
3. SQL statement must be terminated by semicolon (;).
The following is an example of SQL command. What this command does is not important at this moment.
SQL> select ccode,name
2 from courses
3 where fee > 5000;
In the above command, we entered the command in three lines. When you enter semicolon and press enter key then SQL*PLUS will take it as the end of the command.



SQL*Plus statements are available only in SQL*PLUS. They are not part ofstandard SQL. SQL*Plus commands are mainly used for two purposes – editing SQL commands and formatting result of query.
The following rules are to be followed while entering these commands.
1. The entire command must be entered on a single line.
2. No need to terminate command with semicolon (;).
3. Commands can be abbreviated. However, the amount of abbreviation isnot fixed. Some commands are abbreviated to one letter some are abbreviated to 2 and so on.
The following example show how to use CLEAR SCREEN command of SQL*PLUS.

SQL>clear screen
Or it can be abbreviated to
SQL>cl scr

Data Integrity in DBMS

Integrity Rules:

Data integrity is to be maintained at any cost. If data loses integrity it becomes garbage. So every effort is to be made to ensure data integrity is maintained. The following are the main integrity rules that are to be followed.

Domain integrity:

Data is said to contain domain integrity when the value of a column is derived from the domain. Domain is the collection of potential values. For example, column date of joining must be a valid date. All valid dates form one domain. If the value of date of joining is an invalid date, then it is said to violate domain integrity.

Entity integrity:

This specifies that all values in primary key must be not null and unique. Each entity that is stored in the table must be uniquely identified. Every table must contain a primary key and primary key must be not null and unique.

Referential Integrity:

This specifies that a foreign key must be either null or must have a value that is derived from corresponding parent key. For example, if we have a table called BATCHES, then ROLLNO column of the table will be referencing ROLLNO column of STUDENTS table. All the values of ROLLNO column of BATCHES table must be derived from ROLLNO column of STUDENTS table. This is because of the fact that no student who is not part of STUDENTS table can join a batch.

Primary,composite and Foreign key in DBMS

Primary Key:

A table contains the data related entities. If you take STUDETNS table, it contains data related to students. For each student there will be one row in the table. Each student’s data in the table must be uniquely identified. In order to identify each entity uniquely in the table, we use a column in the table. That column, which is used to uniquely identify entities (students) in the table is called as primary key. In case of STUDENTS table we can use ROLL NO as the primary key as it in not duplicated. So a primary key can be defined as a set of columns used to uniquely identify rows of a table. Some other examples for primary keys are account number in bank, product code of products, employee number of an employee.

Composite Primary Key:

In some tables a single column cannot be used to uniquely identify entities (rows). In that case we have to use two or more columns to uniquely identify rows of the table. When a primary key contains two or more columns it is called as composite primarykey.  we have PAYMENTS table, which contains the details of payments made by the students. Each row in the table contains roll number of the student, payment date and amount paid. Neither of the columns can uniquely identify rows. So we have to combine ROLLNO and DP to uniquely identify rows in the table. As primary key is consisting of two columns it is called as composite primary key.

Foreign Key:

In relational model, we often store data in different tables and put them together to get complete information. For example, in PAYMENTS table we have only ROLLNO of the student. To get remaining information about the student we have to use STUDETNS table. Roll number in PAYMENTS table can be used to obtain remaining information about the student. The relationship between entities student and payment is one-to-many. One student may make payment for many times. As we already have ROLLNO column in PAYMENTS table, it is possible to join with STUDENTS table and get information about parent entity (student). Roll number column of PAYMENTS table is called as foreign key as it is used to join PAYMENTS table with STUDENTS table. So foreign key is the key on the many side of the relationship. ROLLNO column of PAYMENTS table must derive its values from ROLLNO column of STUDENTS table. When a child table contains a row that doesn’t refer to a corresponding parent key, it is called as orphan record. We must not have orphan records, as they are result of lack of data integrity.