Constraints

Constraints: These are the restriction imposed on the database in order to ensure that changes made by an authorized user do not result in a loss of data consistency.         e.g.-> account balance cannot be null etc.

INTEGRITY CONSTRAINTS

It is to make sure invalid values should not be inserted or updated. DB should always be in a consistent stage. Restrictions should be possessed in the form of rules and regulations are termed constraint.

A KEY is a relational means of specifying uniqueness. 

TYPES OF KEYS:

• PRIMARY KEY: It is an attribute or set of attribute of a relation which possess the property of uniqueness and irreducibility. Here, no subset should be unique, no similar values can be placed and no null value allowed. 

(note - It’s a constraint which sets the values which could not have similar values in one column of a table. A primary key uniquely identifies every record in a table based upon its values.)

• CANDIDATE KEY: Any key/attribute / combination of attribute that can uniquely identify records in a table i.e. candidate for the primary key referred to as candidate key.
• ALTERNATE KEY: Those candidate key chosen will be called a primary key and the one retained will be referred to as alternate key.
• FOREIGN KEY (Referential Integrity): It is an attribute of a table, which refers to the primary key of some another table. It may be null. Records of weak entity in a table must be depended on the records of strong entity of another table which is declared as primary key.
•   ARTIFICIAL KEY: An attribute added to a relation to identify the records in that relation uniquely and has nothing to do with the database schema is called as artificial key.
• UNIQUE: It allows single values for the records and applied in case of null attribute.

WHAT HAPPEN IF PRIMARY KEY RECORD IS DELETED?

•  Cascaded delete: If we delete record of the primary key table, the record from foreign key table may be allowed to be deleted.
•   Not allowing master record to be deleted before: do not allow a primary key record to be deleted until all the dependent files are not deleted from foreign key.
• Set foreign key to null: foreign key can be set to null (as we are looking for only a valid value in primary key so, a foreign key is free from primary key’s constraints.) 

Allow primary key’s value to be deleted by setting the foreign key to null making sure that when we update this record the value of foreign key attribute is set to one of the valid values.