Keys concepts

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 –

1. Super key
2. Candidate key
3. Composite key
4. Primary key
5. Foreign key

1. Super key – these are key/keys which using we can uniquely identify any table’s record uniquely.
2. 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.
3. 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.
4. Primary key – using this key we can identify any table’s record uniquely.
5. 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..

Student_id	Name	roll
001	Shimanta Das	12
002	Rekha Paul	16
003	Nishant Kumar Jha	19

stu_id	Amount	Date
001	10000.00	12/03/21
003	12000.00	16/08/22

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 –

ID	Student Name	Roll	Sec
1000	akash	1	A
1001	Deep	2	B
1003	Prodip	3	B

Student_id	Name	Books
1003	prodip	History,Science
1000	Akash	Bengali,maths
1001	Deep	English,history

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 –

1. Primary key is always one/single and it can’t be a NULL state.
2. Super key can be one or many in the table as attributes.
3. Composite key will be at-least two attributes in a table/relation.
4. Candidate key can be one or many.

Let’s briefly understand the example of all keys through the below table.

Student_ID	Name	Roll	Address	Phone	Admission Date
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 –

1. NOT NULL constraint – if we restrict any table’s field can’t be empty, then we can use this constraint.
2. Unique constraint – if we restrict any table field need to maintain unique value each time, then we could use this constraint.
3. Default constraint – if we want any field get default value each time.
4. 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.