Constraints in SQL

Neil Bhanja
“Constraint is rule or restriction imposed and individual column to above data enter “
Oracle provide the following constraint to do so
1.     Primary key
2.     Not Null
3.     Unique
4.     Check
5.     Foreign Key

1.    PRIMARY KEY:
       This is constraint which in forces not null value and no report value in a column using this constraint the record is unique identify.
2.    NOT NULL
       This is constraint no not null value is column but it accepts duplicate value.
3.    UNIQUE
       Using this constraint no duplicate value is allow in column but accepts null
4.    CHECK
       It is used for check a condition.
NOTE:
       Multiple value null value accept in column the column                     
5.    FOREGIN KEY
       This is constraints which helpful up to make relationship between two or more than two table using a common attribute. It always refers to same table primary key or another table primary key to make a relationship. It is just opposite of primary key that means it can accept duplicate values and null.
It supports the concept of referential integrity.

NOTE :
       Refer integrity is a concept which no child record is allowed in the chid table unless and until the master record is available.
INCOMPOSEMENT OF CONSTRAINCY:
       Constraint can be impossible during table deign using two different levels
1.      Column level
2.      Table level

1.    COLUMN LEVEL
       Column level constraints are those constraints which are imposed in just after column rectification during table deign.
 NOTE :
       The basic different between column and table level constraint is, in column level maximum one constraint is imposed at one point of time whereas in table level multiple constraints can be imposed in table level
No Null -->  table level (incorrect)
Primary key--> column level (incorrect)

        Each and individual constraint is identify by a name either given by user or system. It is advisable to provide the constraint name by user.
User may follow following naming conversion for a constraint.
Tab_Col_Atype
ex
Myemp_sal_ck
NOTE:
NOT NULL always in column level.
FOREGIN KEY always in table level.

WORKING WITH ONDELETE CASCADE:
       On delete cascade is a keyword return with the foreign key which in force a rule that whenever a master record is deleted from master table all corresponding child records are automatically deleted from child table.
Ex
Create table sysemp
(
Empno number(4),
name vachar2(10),
dptno number(4) constraint sysemp_deptno_fk reference sysdept(deptno) on delete cascade);

Above ex is an ex in it’s FK in column level using on delete cascade.

WORKING WITH CONSTRAINT MANIPULATE :
       Constraint manipulation is constraint through which new constraint can be imposed or exiting constraint can be deleting after table deign. A constraint can be added or delete but a constraint can’t be modified.
       It is not mandatory to provide constraint during table deign rather than syntax to add a constraint after table deign.
SYNTAX TO ADD A CONSTRAINT AFTER TABLE DESIGN:
ALTER TABLE <T_NAME>
ADD CONSTRAINT<C_NAME><C_TYPE> (COL_NAME);
Above syntax is required to single column constraint in a single column.
ALTER TABLE<T_NAME>
ADD (CONSTRAINT <C_NAME><C_TYPE> (COL_NAME),
CONTRAINT <C_NAME><C_TYPE> (COL_NAME),
.....................................................N);
Above syntax is imposed multiple constraint using single alter.
SYNTAX TO DELETE:
ALTER TABLE <T_NAME>
DROP CONSTRAINT <C_NAME>

NOTE:
       Any constraint can be added after table design except not null for its syntactical restriction. User can add NOT NULL constraint forcibly during modification of a column but there should be no data in the table.
SYNTAX:
ALTER TABLE SYSEMP7
MODIFY (NAME VARCHAR2 (10) CONSTRAINT SYSEMP_NAME_NT NOT NULL);

Activating and deactivating a constraint using enable and disable command:
       Whenever a constraint is disable mode user can accept invalid data in it. So before enabling the constraint the user must delete all invalid data enter during disable time.

SYNTAX TO ENABLE/DISABLE A CONSTRAINT
ALTER TABLE <T_NAME>
ENABLE/DISABLE CONSTRAINT <C_NAME>;

WORKING WITH SYSTEM CATALOGUE OR DATA DICTIONARY OR METADATA:
       Whenever an object is created in ORACLE database, the information regarding that object will be kept in a system defined table called data dictionary.
       The advantage of data dictionary is it makes object searching faster. Inside a DD information are manipulate automatically by the system.
       ORACLE provides several diff. Types of objects for database management likewise it provides several diff. types of catalogue.
Such as
Table                     -->         user_tables
View                      -->        user_views
Synonym               -->         user_synonyms
Sequence              -->        user_sequences
Index                     -->        user_indexs
Type                      -->        user_types
Constraint            -->        user_constraints

SQL> DESC USER_TABLES;
SQL>SELECT CONSTRAINT_NAME, TABLE_NAMES, STATUS
FROM USER_CONSTRAINTS
WHERE USER_TABLES=T_NAME;
       It condition is omitted from the above statement it will display all constraints. STATUS is a column of user constraint responsible to display whether the constraints is enable or disabled.
SQL> SEARCH_CONDITION
FROM USER_CONSTRAINTS;
       SEARCH_CONDITION is a column of user constraint responsible to display check condition of a constraint.

IMPLICIT NAMING CONVENTION OF A CONSTRAINT:
       It no constraint name is given by a user by default system will provide the constraint name which looks like the following.
Sys_c005510
Sysc00511
Ex
Create table sysemp
(
Empnumber(4) primary key,
Name vachar2(95) not null,
Sal number (7, 2) check (sal between 2000 and 200000));

In the above ex constraint name is automatically given by the system.

NOTE:
       A constraint can be deleted in two diff. manner
-->  Explicitly using alter command
-->  Implicitly whenever a table is dropped

TABLE                   -->          USR_TABLE
VIEW                     -->          USER_VIEW
SYNOMYM            -->          USER_SYNOMYMS
SEQUENCE            -->          USER_SEQUENCE
INDEX                   -->          USER_INDEX
TYPE                     -->          USER_TYPE
CONSTRAINT       -->          USER_CONSTRAINT


1.      SELECT TABLE_NAME FROM USER_TABLE
In above ex table_name is a column where as user_abl is a table
2.      ESCUSER_TABLE
3.      SELECT CONSTRAINT_NAME, TABLE_NAME , STATUS
FROM USER_CONSTRAINS
WHERE TABLE_NAME=’SYSEMP’;
       If condition is omitted in the above statement it will display all constraint status is user_constraint is enable and disable.
4.      SELECT CONSTRAINT_NAME, SEARCH_CONDITION
FROM USER_CONSTRAINTS
WHERE TABLE_NAME=’SYSEMP’;

Search_condition is column of user constraint which is display check condition of constraint.