There are four main types of integrity constraints in DBMS:
- Domain Constraints:
They define the domain or set of values that an attribute can hold (e.g., integers, characters, dates, times, strings, etc.).
ALTER TABLE employees
ADD CONSTRAINT CHECK_age (age >= 18 AND age <= 65);
- Entity Integrity Constraints:
It ensures that the primary key column(s) contain unique and non-null values.
ALTER TABLE customers
ADD CONSTRAINT PRIMARY KEY (customer_id);
- Referential Integrity Constraints:
These constraints maintain relationships between tables by ensuring that foreign key values reference valid primary key values. This prevents orphaned records and ensures data consistency.
ALTER TABLE orders
ADD CONSTRAINT FK_customer_id
FOREIGN KEY (customer_id) REFERENCES customers(customer_id);
- User-Defined Integrity Constraints:
These constraints are custom constraints defined by the database administrator or application developer to enforce specific business rules or data requirements.
ALTER TABLE products
ADD CONSTRAINT CHECK_stock_quantity (stock_quantity >= 0);
what is candidate key?
In a relational database, a candidate key is a minimal superkey. A superkey is a set of one or more attributes that uniquely identify every row in a table. A candidate key is a superkey that does not contain any redundant attributes. In other words, no attribute in the candidate key can be removed without losing the uniqueness of the row.
primary key:
In a relational database, a primary key is a special constraint that uniquely identifies each row in a table. It is a column or combination of columns that cannot contain null values and must have unique values. The primary key is used to enforce referential integrity, which ensures that relationships between tables are maintained.
