What are Primary Keys and Foreign Keys in SQL Database?

 For programmers, the terms primary key and foreign key may be familiar. This term usually appears in SQL or query languages. Let's learn what foreign and primary keys are and how to use them in SQL.

SQL performs mathematical operations on data in a database management system. This database contains different tables that each store data on a specific entity. If you have a car rental database, the entity (or table) in that database will be the customer (which will store all the personal data on each customer). This database table contains rows and columns, where each row becomes a hosts record and each column holds attribute-specific data. In a database management system, each record (or row) must be unique.

Primary Key

Although the stipulation is that each record in a table must be distinct, this is not always the case. Continuing with the car rental database example, if the database contains two customers who each have the same name, namely “Johnny Cage”, Johnny Cage is required to return a Mercedes-Benz that he has rented. Creating a primary key reduces this risk.

What are Primary Keys

In the SQL database management system, the primary key is the unique identifier that distinguishes one record from another. Therefore, every record in the SQL database management system must have a primary key. But there are some rules you should follow when specifying the primary key for a table:

* Primary key must contain a unique value. If the primary key consists of several columns, the combination of values in these columns must be unique.

* The primary key column cannot contain NULL values. This means that you must declare a primary key column with a NOT NULL attribute. Otherwise, MySQL will implicitly coerce the primary key column as NOT NULL.

* A table has only one primary key

Using Primary Key In Database

To include the primary key in a database management system using SQL, you can simply add it as a normal attribute when creating a new table. So the customer table will contain four attributes (or columns):

* Car Owner ID (which will store the primary key)

* First name

* Last name

* Phone number

Now every customer record that enters the database will have a unique identification number, as well as a first name, last name, and telephone number. Phone numbers are not unique enough to serve as primary keys, because even if they are unique to one person at a time, someone can easily change the number, meaning it could belong to someone else.

Record With Primary Key Example

/* create a new record in the customer table */

INSERT INTO Customer VALUES

('0004',

'Johnny',

'Cage',

'081-999-888-777');

The SQL code above will add a new record to the existing Customers table. The table below shows the new customer table with two Johnny Cage records.

Record With Primary Key Example

Foreign Keys

Now you have the primary key that uniquely differentiates car renters from the crowd. The only problem is, in the database, there's no real connection between any Johnny Cage and the cars he rents. Therefore, the possibility of errors still exists.

This is where foreign keys come into play. Using the primary key to solve the ownership ambiguity problem can only be achieved if the primary key doubles as a foreign key.

What are Foreign Keys?

In the SQL database management system, a foreign key is a unique identifier or combination of unique identifiers that links two or more tables in a database. Of the four existing SQL database management systems, the relational database management system is the most popular.

When deciding which tables in a relational database should have a foreign key, you must first identify which table is the subject and object in the relationship.

Going back to the car rental database, to link each customer to the correct car, you have to understand that the customer (subject) rents a car (object). Therefore, the foreign key must exist in the cars table. The SQL code that generates tables with foreign keys is a little different from the norm.

Creating Tables With Foreign Key Examples

/* create a new car table in the car rental database */

CREATE TABLE Car

(

License Number varchar(30) NOT NULL PRIMARY KEY,

CarType varchar(30) NOT NULL,

CustomerID varchar(30), FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)

);

Create a Table With a Composite Foreign Key

As you can see in the code above, the foreign key must be explicitly identified, along with a reference to the primary key linked to the new table. To add records to a new table, you must ensure that the value in the foreign key field matches the value in the primary key field of the original table.

Adding a Record With a Foreign Key Example

/* create a new record in the cars table */

INSERT INTO CAR VALUES

('100012',

'Mercedes-Benz',

'0004');

The code above creates a new record in the new Cars table, producing the following results.

Adding a Record With a Foreign Key Example

From the table above, you can identify the correct Johnny Cage who rented a Mercedes-Benz with a foreign key in the record.

Advance Foreign Key

There are two other ways to use foreign keys in the database. If you look back at the definition of foreign key above, you will find that a foreign key can be a unique identifier or a combination of unique identifiers.

Going back to our example car rental database, you'll see that creating a new record (of the same car) every time a customer rents that car defeats the purpose of the Cars table. If the car is going to be sold and sold once to a single customer, the database is perfect, but considering the car is a rental there is a better way to display this data.

Composite Keys

Composite keys have two or more unique identifiers. In a relational database, there will be instances when the use of a single foreign key does not adequately represent the relationships in that database. In the car rental example, the most practical approach is to create a new table that holds the rental details. For the information in the car rental table to be useful, it must be linked to the car and customer tables.

Create a Table With a Composite Foreign Key

/* create a RentalCar table in the car rental database */

CREATE TABLE Rent a Car

(

LeaseDate DATE NOT NULL,

License Number varchar(30) NOT NULL,FOREIGN KEY (License Number) car REFERENCES(License Number),

CustomerID varchar(30) NOT NULL, FOREIGN KEY (CustomerID) REFERENCES customer(CustomerID),

PRIMARY KEY (Lease Date, License Number, Customer ID)

);

Creating Tables With Foreign Key Examples

The code above illustrates an important point. Although tables in an SQL database can have more than one foreign key, they can only have one primary key. This is because there is only one unique way to identify a record.

The three attributes in the table must be combined to have a unique key. A customer can rent more than one car on the same day (so CustomerID and RentalDate is not a good combination) more than one customer can also rent the same car on the same day (so License Number and RentalDate is not a good combination).

However, creating a composite key that tells which customer, what car, and on what day of day makes for an excellent unique key. This unique key represents a composite foreign key and a composite primary key.

Foreign Primary Key

Oh yes, foreign primary keys also exist. Although there is no official name, a foreign key can also be the primary key in the same table. This happens when you create a new table that contains special data about an existing entity (or records in another table).

Let's say Toni (who works for a car rental company) is in the company database under the employees table. After a few years, he becomes a supervisor and is added to the supervisors table. Toni is still an employee and still has the same id number. So Toni's employee id is now in the supervisors table as a foreign key which will also be the primary key in that table (because it doesn't make sense to create a new id number for Toni now that she is the supervisor).

Difference between Primary Key and Foreign Key

Primary Key Foreign Key

A primary key constraint is a column or group of columns that uniquely identifies each row in a relational database management system table. Foreign key is a column that creates a relationship between two tables.

This helps you uniquely identify records in a table. This is a field in a table that is the primary key of another table.

The primary key never accepts a null value. Foreign keys can accept any number of null values.

The primary key is the clustered index, and the data in the DBMS tables are physically arranged in clustered index order. Foreign keys cannot automatically create indexes, clustered, or non-clustered

You can have one primary key in a table. You can have multiple foreign keys in one table.

The primary key value cannot be deleted from the parent table. Foreign key values can be removed from child tables.

You can specify the primary key implicitly in the temporary table. You cannot define foreign keys in local or global temporary tables.

The primary key is a clustered index. By default, it is not a clustered index.

No two rows can have identical values for the primary key. Foreign keys can contain duplicate values.

There are no restrictions on entering values into table columns. When entering any value in the foreign key table, make sure that it is in the primary key column.

Conclusion

So what are primary keys and foreign keys? Primary keys are useful rules so that each row of data or value is unique, so that each row is different from one another. Meanwhile, a foreign key is an attribute (or a set of attributes) that completes a relationship that points to its parent.

From this article, you already know what foreign and primary keys are, how they work, and why it's important to have them in your database. You have also understood the basic forms of primary and foreign keys and are even more complex.

________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________ ________

So many articles What are Primary Keys and Foreign Keys in SQL Database. Look forward to other interesting articles and don't forget to share this article with your friends. Thank you…

Resa Risyan

Just an ordinary person who wants to share a little knowledge, hopefully the knowledge I provide can be useful for all of us. Keep in mind! Useful knowledge is an investment in the afterlife.