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.
- Difference between Strong and Weak Entity
- Difference between Entity and Object
- Weak Entity Set in ER diagrams
- Semantic Heterogeneity in DBMS
- SQL | Constraints
- SQL | Checking Existing Constraints on a Table using Data Dictionaries
- Violation of constraints in relational database
- Constraints on Relational database model
- Structural Constraints of Relationships in ER Model
- Difference and Similarities between PHP and C
- Difference between Stop and Wait protocol and Sliding Window protocol
- Similarities and Difference between Java and C++
- Difference between Yaacomo and and XAP
- Difference between VoIP and and POTS
- Difference between ++*p, *p++ and *++p
- Difference Between DOS and Windows
- Difference between User Level thread and Kernel Level thread
- What’s difference between The Internet and The Web ?
- Difference between Priority Inversion and Priority Inheritance
- What’s difference between Linux and Android ?
If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to firstname.lastname@example.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.