1. CONSTRAINT CONCEPTS
1.1. Data Constraints
Business Rules, which are enforced on data being stored in a table, are called Constraints. It super control the data being entered into a table for permanent storage.If constrains fails during insertion or updation of records, then entire record will be rejected and Insert or Update operation failed.
There are 2 types of data constraints which can be further classified as described in below sections.
- Input/Output Constraints
- The Primary Key Constraint
- The Foreign Key Constraint
- The Unique Key Constraint
- Business Rule Constraints
- CHECK Constraints
1.2. Input/Output Data Constraints
1.2.1. Primary Key Constraint
Primary Key Constraint defined at Column LevelNormally used, to define a single primary key in a table.
SYNTAX:
[COLUMN NAME] [DATATYPE] ([SIZE]) PRIMARY KEY
E.g.
CREATE TABLE employee (employee_no varchar2 (10) PRIMARY KEY, employee_name varchar2 (25));
Primary Key Constraint defined at Table Level
Normally used, to define a composite primary key in a table.
SYNTAX:
PRIMARY KEY ([COLUMNNAME], [COLUMNNAME])
E.g.
CREATE TABLE employee (employee_no varchar2 (10), employee_ssn varchar2(20), employee_name varchar2 (25), PRIMARY KEY (employee_no, employee_ssn));
Features of a Primary Key
- Uniquely identifies a Row
- Does not allow duplicate values and null values
- Helps in establishing relationship among tables
- Cannot be LONG or LONG RAW data type
- Only 1 Primary Key is allowed per table
- Unique Index created automatically
1.2.2. Foreign Key Constraint
This constraint establishes a relationship between records across a Master Table and Details table.Foreign Key Constraint defined at Column Level
SYNTAX:
[COLUMNNAME] [DATATYPE]([SIZE]) REFERENCES [TABLENAME] [([COLUMNNAME]) ]
[ON DELETE CASCADE]
Note: ON DELETE CASCADE delete all the related records from the Master Table & Details table.
ON DELETE SET NULL option instead of deleting the record set the column value to NULL in the entire related table.
For e.g.
CREATE TABLE employee (employee_no varchar2 (10), employee_ssn varchar2(20), employee_name varchar2 (25), deptt_code varchar2(10) REFERENCES DEPTT_MASTER);
Here REFERENCES point to the DEPTT_MASTER table and create deptt_code as foreign key in employee table.
deptt_code is a primary key of DEPTT_MASTER table.
Foreign Key Constraint defined at Table Level
SYNTAX:
FOREIGN KET ([COLUMNNAME], .. [COLUMNNAME]) REFERENCES [TABLENAME] ([COLUMNNAME],…. [COLUMNNAME])
For e.g.
CREATE TABLE employee (employee_no varchar2 (10), employee_ssn varchar2 (20), employee_name varchar2 (25), deptt_code varchar2 (10), FOREIGN KEY (deptt_code) REFERENCES DEPTT_MASTER (deptt_code));
Features of Foreign Keys
- Is a column(s) referencing a column(s) of a table (may be same table)
- Parent references should be unique or primary key
- Child may have duplicates or null but unless it is specified.
- Foreign Key Constraint can be specified on child but not on parent.
- Parent Record can be deleted provided no child record exists.
- Records cannot be inserted into a detail table if corresponding records in master table do not exist.
- Records cannot be deleted from the Master Table if corresponding records in the detail table actually exist.
1.2.3. Unique Key Constraint
Unique Key Constraint defined at Column LevelNormally used, to define a single unique key in a table.
SYNTAX:
[COLUMNNAME] [DATATYPE]([SIZE]) UNIQUE
For e.g.
CREATE TABLE employee (employee_no varchar2 (10) UNIQUE, employee_ssn varchar2 (20), employee_name varchar2 (25));
Unique Key Constraint defined at Table Level
Normally used, to define a composite unique key in a table.
SYNTAX:
UNIQUE ([COLUMNNAME],…. [COLUMNNAME])
For e.g.
CREATE TABLE employee (employee_no varchar2 (10), employee_ssn varchar2 (20), employee_name varchar2 (25), UNIQUE(employee_no));
Note: Unique Key Column allow NULL values while Primary Key Column does not allow NULL values
Features of Unique Key Constraint
- Does not allow duplicate values
- Unique Index created automatically
- A Table can have more than 1 unique key which is not possible in Primary Key
- Cannot be LONG or LONG RAW data type
1.3. Business Rule data constraint
Business Rule validations can be applied to a table column using CHECK constraint. CHECK constraints must be specified as a logical expression that evaluates either to TRUE or FALSE.1.3.1. Check Constraint
Check Key Constraint defined at Column Level
SYNTAX:
[COLUMN NAME] [DATATYPE] ([SIZE]) CHECK ([LOGICAL EXPRESSION])
E.g.
CREATE TABLE employee (employee_no varchar2 (10) CHECK (employee_no like ‘E%’), employee_name varchar2 (25) CHECK (employee_name=UPPER(employee_name));
Unique Key Constraint defined at Table Level
SYNTAX:
CHECK ([LOGICAL EXPRESSION])
E.g.
CREATE TABLE employee (employee_no varchar2 (10), employee_name varchar2 (25), CHECK (employee_no like ‘E%’), CHECK (employee_name=UPPER(employee_name));
Restriction on Check Constraints
- The condition must be a Boolean expression
- Check constrains require a condition to be true or unknown for the row to be processed
- The condition cannot contain subqueries or sequences
- The condition cannot include SYSDATE, UID, USER or USERENV SQL functions.
Note: CHECK constraint takes substantially longer to execute as compared to NOT NULL, PRIMARY KEY, FOREIGN KEY or UNIQUE.
1.4. NULL Value Concepts
Principles of NULL values- Setting NULL value is appropriate when actual value is unknown.
- A NULL is not same as 0 if data type is number and not same as spaces if the data type is character.
- A NULL value will evaluate to NULL in any expression.
- NULL value can be inserted into columns of any data type
- Usual operands such as =,<,> and so on cannot be used on a NULL value. Instead, the IS NULL and IS NOT NULL conditions have to be used.
1.4.1. NOT NULL Constraint
SYNTAX:[COLUMNNAME] [DATATYPE]([SIZE]) NOT NULL
E.g.
CREATE TABLE employee (employee_no varchar2 (10) PRIMARY, employee_name varchar2 (25) NOT NULL);
Note: The NOT NULL constraint can only be applied at column level.
1.5. Default Value Concepts
The DEFAULT clause can be used to specify a default value for a column.SYNTAX:
[COLUMNNAME] [DATATYPE]([SIZE]) DEFAULT [VALUE];
E.g.
CREATE TABLE employee (employee_no varchar2 (10) PRIMARY, employee_name varchar2 (25) NOT NULL, sex varchar2 (2) default ‘M’);
Rules for Default Value
- The data type of the default value should match the data type of the column
- Character and date values will be specified in single quotes
- If a column level constraint is defined on the column with a default value, the default value clause must precede the constraint definition
[COLUMNNAME] [DATATYPE]([SIZE]) DEFAULT [VALUE] [CONSTRAINT DEFINITION]
2. REFERENCES
- SQL, PL/SQL Third Edition by Ivan Bayross.
Comments