Let’s understand the concept of keys and constraints in relational database model.
Keys – A key refers to an attribute/a set of attributes that help us identify a row (or tuple) uniquely in a table (or relation). A key is also used when we want to establish relationships between the different columns and tables of a relational database.it helps to identify a table’s record.
There are multiple keys which are used in RDBMS. These are –
- Super key
- Candidate key
- Composite key
- Primary key
- Foreign key
- Super key – these are key/keys which using we can uniquely identify any table’s record uniquely.
- Candidate key – candidate key is an alternative key of primary key. Using this key we can identify any table’s record uniquely. In table/relation candidate keys may be one or many attributes.
- Composite key – using this key we can identify any table’s record uniquely. In table/relation these are attributes which helps to do this. We need at-least two attributes for composite keys.
- Primary key – using this key we can identify any table’s record uniquely.
- Foreign key – if there are two relations/tables which have the same primary key relation, then the master table’s primary key foreign key will be another child table primary key.
Let’s understand the foreign key concept through an example..
|003||Nishant Kumar Jha||19|
According to the above tables(student and fees), ‘student’ table ‘student_id’ is primary key and ‘fees’ table primary key ‘stu_id’. Master table ‘student’ primary key ‘student_id’ its foreign key is child table ‘fees’ primary key ‘stu_id’.
Lets understood through another example
In a school a student can access the library but before he/she needs to get registered into the library. So in this case, there must be two tables – students and library. Let’s imagine ourselves two tables will –
Here from the above tables, Student_id is the primary key of the library table and ID is the primary key of students table.in that case both keys have a relation of foreign keys. Here child table(library) Student_id is the foreign key of Master Table(students) primary key ID.
Important notes –
- Primary key is always one/single and it can’t be a NULL state.
- Super key can be one or many in the table as attributes.
- Composite key will be at-least two attributes in a table/relation.
- Candidate key can be one or many.
Let’s briefly understand the example of all keys through the below table.
|5432||Shimanta Das||3||khardaha||9632458712||5 aug 2022|
|5433||Bidyut Sarkar||5||Bonga||8945120369||6 sep 2022|
|5436||Dipsayak Saha||2||Baghbazar||4587120396||9 feb 2023|
- Super key – ‘name’ field or ‘student_id’. Using any of these mentioned attributes we can uniquely identify record.
- Candidate key – ‘phone’ field will be different for all,so using these attribute we can uniquely identify record in a table/relation.
- Composite key – ‘name’,’roll’ both attribute together. A student can be the same but their roll no will not. So using these two attributes in a table/relation we can identify any record uniquely.
- Primary key – ‘student_id’ field. Obviously ‘student_id’ of every student will be different and unique also.
I hope you understand the concepts of keys. If you want to know more then plz, checkout our channel(youtube – microcodes) for more.
Now let’s understand the concept of Constraints in relational databases.
Constraints – these are some rules which apply on attributes of a relational table, to restrict user’s input data.
There is much type of constraints there –
- NOT NULL constraint – if we restrict any table’s field can’t be empty, then we can use this constraint.
- Unique constraint – if we restrict any table field need to maintain unique value each time, then we could use this constraint.
- Default constraint – if we want any field get default value each time.
- Check constraint – it checks some conditions depending upon user inputs.
Let’s take a simple example which will clear your above these –
|Admission No||Name||Age||Date||Body problems|
|100||Shimanta Das||21||8 jul 2023||No|
|101||Maya Sarkar||21||11 jul 2023||Backpain|
From the above registration table, we have several tuples and fields.
Admission no = NOT NULL and Unique Constraint
Name = NOT NULL constraint
Age = restricts above 18(age>18), check constraint
Date = default constraint (updates automatically during registration)
Body problems = NULL (it’s can be leave as empty)
** During registration of a student, admission no, filed is must for every case. Same as, Name attribute’s field can’t be null. For age field it must be above 18. We set date filed, will update automatically when a student gets registered. For body problems field, it’s not mandatory for all to fill-up details. Because every student does not have body problems.
You will understand it better when you practice SQL queries and make websites using PHP.