Constraints are of 5 Types, these set rules for data integrity, reliability and compliance to standard requirements. 1. Domain Constraints - Valid value, data type or data in a range 2. Key Constraints - Applied to one or more columns 3. Entity Integrity Constraint - Enforces PK 4. Referential Integrity Constraint - Enforces PK and FK integrity
1. Domain Constraints
Define permissible values for a given attribute, by data type, format, allowed range or set of values.
- Data Type constraints - INT, VARCHAR, DATE, etc
- Length constrains - VARCHAR(10)
- Range Constraint - CHECK, used in DBMS to apply a check on a value, like CHECK AGE>=18, DECIMAL(5,2) - 5 digits and 2 decimal
- Nullability - NOT NULL
- Default Constraint
2. Key Constraints
Define rules on one or more columns
- Primary Key Constraint - Uniquely identifies a row in a table, constraint on unique and not null, and only one primary key in a table
- Unique Constraint - Unique in table, but allows NULL
- Foreign key constraint - A key to refer the primary key in another table, it establishes the relationship. Enforces the referential integrity like, insert in foreign key should exist in the primary key, if foreign key exists PK can’t be deleted, and cannot modify the FK to a value for which PK doesn’t exists
3. Entity Integrity Constraint
It specifically emphasizes to enforce the Primary Key constraints - Uniqueness and Not null on Inserts and updates.
4. Referential Integrity Constraint
It specifically emphasized to enforce foreign key and primary key relationship and reference integrity while insert, update and delete operations of data.
5. Tuple(row) Uniqueness Constraint
Tuple or Row uniqueness is constraint over the complete row. It can be enforced using unique, check or primary key, but the conditions should enforce complete row uniqueness.