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.
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');
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.
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.
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.
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.|
create table( column1 datatype1 primary key…)
create table( column1 datatype1 references tablename…)
|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.