Hey there 👋
Hope you are doing well 🙂
In the last blog we have discussed about ALTER command and its use cases, Order By and NULL Values. In this blog we are going to discuss about Keys and Joins in SQL.
So let's get started 🔥
DESC command in SQL
To describe the structure of table in SQL we use DESCRIBE or DESC(both are the same) commands.
Let's suppose we have a users table that contains the id, name, email and age of any user. Here's how we have made it -:
So you can see that I have applied a primary key constraint on Id field. We will see what primary key is in a short time.
Now to describe the structure of table we will use following command -:
desc users
So this is the output generated. Here we have information about every field in table. We have information about datatype, constraint, initial values, Null values and extra information.
This is important when we are working with multiple tables and we want to look into structure of each table.
Keys in SQL
In SQL, keys are essential constraints used to uniquely identify records in a table and establish relationships between tables. They ensure data integrity and prevent duplicate or invalid entries.
Primary Key
The PRIMARY KEY constraint uniquely identifies each record in a table. Primary keys must contain UNIQUE values, and cannot contain NULL values.
Suppose we have a Student table that contains id, name and age of students in a class. Now here each student must have a unique id and each student must have a valid id. So we will apply a Primary Key constraint to id field. This is how we can do it -:
After adding some data to this table, this is how our table will look like -:
Now let's try to add another student with id 1.
We will get an error.
Each table can have atmost 1 primary key.
So let's try to add an column to our table with primary key constraint.
We can composite multiple columns to create one Primary Key.
You know that Sql doesn't allow to make multiple primary keys but what if we are required to apply primary key constraint on more than one column. In this case we can combine the columns and apply primary key constraint on this composition.
Let's create students table again and this time we will apply primary key on both id and name column.
So here we have defined a composition of (id,name) as student and applied primary key on that.
This is how our table looks like initially, now let's try to add (1,"John",7) to this table.
But let's try to add (5,"John",8) to this table-:
So this data is added to table. Why?
Because here the primary key constraint is applied to combination of (id,name) and we are adding distinct value of this composition.
Note that this composition of keys is called Composite Key.
Drop a Primary Key
So to drop a primary key this is how we can do it-:
The syntax depends on the type of DBMS you are working with, above is used for MySql.
Unique Key
Unique key ensures all values in a column (or a set of columns) are unique. Unlike the primary key, it allows one NULL value.
So recreating students and setting constraint of id field as unique key.
This is our table. Now let's add data to only name and age column.
A PRIMARY KEY constraint automatically has a UNIQUE constraint.
However, you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.
Candidate Key
A column (or a set of columns) that can uniquely identify each record in a table. A table can have multiple candidate keys, but one is chosen as the Primary Key.
Here both Email and EmployeeId can be candidate keys because both have to be unique for every employee.
Alternate Key
Any candidate key that is not chosen as the primary key is alternate key. In above example Email is alternate key.
Super Key
A superset of candidate keys that can uniquely identify each record in a table. It may include extra columns in addition to the candidate key. In the Employees table, both EmployeeID and EmployeeID, Name are super keys.
Foreign Key
A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY KEY in another table. The table with the foreign key is called the child table, and the table with the primary key is called the referenced or parent table. This is basically used to create a link between two tables.
Here, DepartmentID in the Employees table is a foreign key referencing the Departments table.
The FOREIGN KEY constraint prevents invalid data from being inserted into the foreign key column, because it has to be one of the values contained in the parent table.
So these were Keys in SQL.
In the next blog we will see joins.
I hope you liked my blog. Please leave some ❤ and don't forget to follow me.
Also have you checked my Instagram page where I upload necessary resources for software engineers.
If not check it out here -:
👉 Instagram: https://www.instagram.com/fluxx_96/
👉 YouTube: https://www.youtube.com/@Fluxx-n4q/shorts
Thankyou 💙
Top comments (0)