DEV Community

Cover image for Integrity Constraints in DBMS
DHRUV TRIVEDI
DHRUV TRIVEDI

Posted on • Updated on

Integrity Constraints in DBMS

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);
Enter fullscreen mode Exit fullscreen mode
  • 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);
Enter fullscreen mode Exit fullscreen mode
  • 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);
Enter fullscreen mode Exit fullscreen mode
  • 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);

Enter fullscreen mode Exit fullscreen mode

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.

If you have any feedback, then you can DM me on Twitter or Linkedin.

Top comments (1)

Collapse
 
codingmaster97 profile image
codingmaster97

Intresting post

Some comments may only be visible to logged-in visitors. Sign in to view all comments.