Constraints in sql-: An integrity constraint can be thought of as a way to define a business rule for a column or a table. Integrity constraints are defined with a table and are stored as part of the table’s definition in the data dictionary. This is advantageous as if a rule changes then it has to be changed only once in the data dictionary.
Data Integrity Constraints:-
Constraint Description
NOT NULL Specifies that this column may not contain a null value
UNIQUE Specifies a column or combination of columns whose values must be unique for all rows in the table.
PRIMARY KEY Uniquely identifies each row of the table
FOREIGN KEY Establishes and enforces a foreign key relationship between the column and a column of the referenced table.
CHECK Specifies a condition that must be true.
Defining Constraints:-The integrity constraints can be defined using the CONSTRAINT clause. The CONSTRAINT clause can appear in CREATE TABLE and ALTER TABLE commands. This clause allows the user to define UNIQUE, PRIMARY KEY, NOT NULL, FOREIGN KEY and CHECK integrity constraints.
The CREATE TABLE command is used when the constraints are to be defined at the time of table creation. The ALTER TABLE command is used when the table already exists and constraints are to be defined.
Constraint Guidelines
Constraints are easy to reference if we give them a meaningful name. Constraint names must follow the standard object-naming rules. If we do not name our constraint, the Server generates a name with the format SYS_Cn, where n is an integer to create a unique constraint name.
The NOT NULL Constraint:-
When insertions are done for only a few columns of a table, the rest of the columns get a null value assigned to them. A rule defined on a single column which allows or disallows insert or update of rows containing a null for the column is called NOT NULL constraint. The NOT NULL constraint ensures that null values are not allowed in the column. Columns without the NOT NULL constraint can contain null values by default.
The NOT NULL constraint can be specified only at the column level, not at the table level.
The Unique Key Constraint:-
A UNIQUE key integrity constraint requires that every value in a column or set of columns be unique – i.e., no two rows of a table have duplicate values in a specified column or set of columns. The column (or set of columns) included in the definition of the UNIQUE key comprises more than one column, that group of columns is said to be a composite unique key.
Create table dept( deptno Number(2),dname varchar(20) not null,loc varchar(13),constraints unq_dept_dname UNIQUE(dname);
Rules -:
- More then one constraints can be define on a column .
- Not null value constraints define using the column constraint syntax.
The Primary key constraint -: This constraints key make a primary key for a table. Only one primary key can be created for one table as like this way ..
Create table dept( deptno Number(2) Constraint dept_deptno_pk Primary key,dname varchar(20) ,loc varchar(13);
Foreign Key Constraints-: A foreign key value must match an existing value in the parent table or be NULL. A foreign key are based on data values and are purely logical not physical pointers. As like
Create table dept( deptno Number(2),dname varchar(20) not null,loc varchar(13),sal number(8), constraint dept_sal_fk foreign Key (sal) reference emp(Sal);
Check constraint-: It define a condition that each row must satisfy. It is violated only if the condition evaluated to False TRUE and Unknown value do not violated a check condition.
Create table dept( deptno Number(2),dname varchar(20),loc varchar(13),constraints emp_deptno_ck check (deptno between 10 and 99));
Dropping a constraint -: In this constraint use Alter statement with drop clause.
Alter table tablename Drop {Primary Key | Unique (column)| CONSTRAINT constraint } [cascade];
Modify Integrity constraint-: There are not any direct method to modify integrity constraint. It need to dropped and then redefined.
Alter table dept (drop constraint dept_deptno_nn Add(constraint dept_dname_unq UNIQUE(dname));
Disabling constraint -: we can disable a constraint without dropping it. It may be using alter table statement as like
Alter table dept disable CONSTRAINT constraint [cascade];