Integrity constraints: – Integrity constraints are the rules in real life which are too we imposed on the data. If the computer data is not satisfying these constraints then it is considered as inconsistent. The data which is not obeying integrity constraints is called inconsistent data. Every software must enforce each and every integrity consistence, otherwise inconsistent data will be generated. There are two types constraints used in SQL to integrate multiple existing table. They are called domain constraints and key constraints.
Domain constraints: – domain means a set of permitted values. Domain constraint are handle by defining proper data type, specifying NOT NULL constraints if necessary and by specifying CHECK constraints. It means every data should have proper data type, proper range and proper format. It also includes whether NULL values are acceptable for data. An example of domain constraints –
Create table student(rn number(3) not null,year number(1) not null,marks numer(5,2) not null,remarks varchar2(20),constraint student_rn check(rn between 1 and 250),constraint student_year check(year between 1 and 3),constraint student_marks check(marks between 0 and 100));
Key constraints: – key constraints are used in integrating constraints for creating table. There are three type of key constraints are used in SQL for integrity constraints. They are super key, candidate key and primary key.
Super key: – super key is the set of attributes or columns which unlikely identifies the identity. Entity means a think which is having physical or conceptual meaning. For example, student, employee, vehicle may be a super key because it contents number of attributes. Suppose, we create a table employee that means the employee is a super key because all the attributed such as employee name, employee code, salary, etc. are integrated with employee super key.
Candidate key: – A candidate key is the super key without any extraneous attribute or it is the super key with minimal attributes. For example, name may be a candidate key if this attribute is divided into two or more parts such as first name, middle name, last name and show own marks may also be a candidate key if it is divided into marks of subject1, subject2, subject3 and show own. The candidate key mainly used in object oriented data base instead of relational data base.
Primary key: – The primary key is the main key which is chosen by the designer. For recognizing entity. It is the choice among multiple super key. For example, if student is a super key then its roll no or registration number is its primary key because there is no meaning of super key such as student if primary key is not existed. Primary key can be constructed when table is created. A primary key further divided into two different keys such as unique key and foreign key.
Unique key: – because only one primary key created in a table. If it is necessary to create multiple primary keys then the remaining keys are created as unique key. A unique key can be defined to avoid duplicates in other data base column. The unique key (U.K.) can be defined according to the table definition. They have the following characteristics –
- Unique key column does not accept duplicate values.
- One table can have number of unique keys.
- Same combination of columns should not take part in both unique and primary keys.
- By default, unique key columns accept null values unless not null constraints are specified.
- Unique key is implemented by using keyword unique.
An example of creating table with primary and unique key –
Create table T(a number(2),b number(2),c number(2),d number(2),e number(2),constraint PK_T primary key (a,b),constraint UK_T1 unique (e),constraint UK_T2 unique (c,d));
Foreign key: – The foreign key is the constraint on data of one table due to data of another table. Suppose we have two tables namely student and student mark. Table student contents all information about student and student marks table contents only marks column of different subject then we integrate both using foreign key. Implementation of table using foreign key –
Create table student_marks(rn1 child column,year child column,marks child column,constraints fk_student_marks foreign key(rn1) reference student(rn) on delete cascade);