Relational Model | Key constraints | Types of key

KEY CONSTRAINTS

INTRODUCTION-A Key Constraint is a statement that a certain minimal subset of the fields of a relation is a unique identifier for a tuple.

TYPES OF KEY CONSTRAINTS- Various types of key constraints are-

1. Candidate key
2. Super key
3. Primary key
4. Foreign key

1. Candidate key- These are those attributes of a relation that contains the properties of uniqueness and irr-reducability. 

• Uniqueness- At any time no two tuples of a relation should have the same value means no duplicate values. 
• Irr-reducability-  Let "k" be the set of attributes in any relation "R" i.e.it contains composite key  then no proper subset of "k"  has the uniqueness property.

2. Super Key-  These are those attributes of a relation that contains the properties of uniqueness but not necessary that it satisfies  irr-reducability property.

3. Primary Key- Mixture of uniqueness and not null is known as the primary key.

Properties of Primary key-

1. Stable- It should be stable means value must not change or it should not be null throughout the life of an entity.

2. Minimal- It should be minimal means it should be composite of minimum no of fields that ensures that the occurrence are unique.

3. Definitive- It should be definitive means a value must exist for every record at the creation time.

4. Accessible-It should be accessible means any one who wants to create,read or delete a record mus be able to see the primary key values.

SYNTAX- create table <table name> (column name1 datatype (size), column name2 datatype(size) constraint (constraint name)  primary key);

EG- create  table  mydoc3 ( username varchar (20) ,password  number(10) constraint  rollno_pk  primary key);

4. Foreign key-Primary key of one table is the foreign key of some other table.

Features of foreign key:

1. Records cannot be inserted into a detail table if corresponding records in the master table do not exist.

2 .Records of the master table cannot be deleted  or updated if corresponding records in the detail table actually exist.

                        SYNTAX- create table <table name> (column name1 datatype (size), column name2 datatype(size) constraint (constraint name) primary key);

create table <table name> (column name1 datatype (size), column name2 datatype(size) references <tablename which contain primary key < primary key attribute>);

 EG- create  table  doc9    (bname  varchar(20),brollno number(20)    constraint vn_o   primary key);

create  table  doc10   (sname  varchar(20),srollno number(20)    references  doc9(brollno));