Difference between Entity constraints, Referential constraints and Semantic constraints

The data in a relational database is stored in form of a table. A table makes the data look organized. Yet in some cases we might face issues while working with the data like repetition. We might want enforce rules on the data to avoid such technical problems. Theses rules are called constraints. A constraint can be defined as a rule that has to enforced on the data to avoid faults. There are three kinds of constraints: entity, referential and semantic constraints. Listed below are the differences between these three constraints :

1. Entity constraints :
These constraints are given within one table. The entity constraints are primary key, foreign key, unique.

Example :

create table student (rollnumber int primary key, name varchar2(30), course varchar2(10));
Insert into student values(111, 'ABC', 'Chemical');
Insert into student values(112, 'JJP', 'Mech');
Rollnumber Name Course
111 ABC Chemical
112 JJP Mech

These values are inserted in the table. Suppose a value given below is inserted :

Insert into student values(111, 'MAB', 'EEE');

It gives an error as the roll-number is enforced a primary key constraint that refrains from duplication. These constraints ensures to maintain uniqueness in the tables to avoid duplication’s.



2. Referential constraints :
These constraints are used for referring other tables to enforce conditions on the data. The widely used referential constraint is foreign key.

Example :

create table marks (rollnumber int, name varchar2(30), course varchar2(30)
references student, marks int);

A table is created with a constraint that the marks should be rewarded to those students that are pursuing courses stated in the student table only. If a user tries to enter a value that doesn’t exist, it returns an error.

3. Semantic constraints :
Datatypes are the semantic constraints enforced in a table. Datatypes help the data segregate according to its type.

Example :
A name is a combination of different letters. We can place the name column in the char datatype yet char doesn’t satisfies the condition thereby varchar is preferably used for name.

name varchar2(30);



Difference between Entity constraints, Referential constraints and Semantic constraints :

Characteristics Entity constrints Referential constraints Semantic constraints
Definition Entity constraints are posed within a table. Referential constraints are enforced with more than one table. Semantic constraints are enforced on the values of a specific attribute .
Kinds The entity constraints are: unique, primary key, NULL. The referential constraints are foreign key. The semantic constraints are the datatypes.
Description These constraints are used to enforce uniqueness in a table( while NULL is used to define no value) These constraints are used for referring to another table for analysis of the data. These constraints are used to divide a set of particular value based on a category.
Functions These constraints ensure non duplicate’s in a database. These constraints ensure that consistency of a database. These constraints ensure that values are categorized accordingly to avoid confusions.
Syntax Primary key:
create table( column1 datatype1 primary key…)
Foreign key:
create table( column1 datatype1 references tablename…)
column1 varchar2(30)
Examples No two students can be designated the same rollnumber. Rollnumber being referred to the marks table. Name is assigned to varchar2 with a precision of 50.

Attention reader! Don’t stop learning now. Get hold of all the important CS Theory concepts for SDE interviews with the CS Theory Course at a student-friendly price and become industry ready.

My Personal Notes arrow_drop_up


If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :
Practice Tags :


1


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.