What are the primary and external keys in the 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.

Read also: Understanding databases and their functions and examples

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

The main key

Although the stipulation is that each entry in the table must be different, this is not always the case. Continuing the example of a car rental database, if the database contains two customers who each have the same name, namely “Johnny Cage”, Johnny Cage is obliged to return a Mercedes-Benz that he rented. Creating a primary key will reduce this risk.

What is the primary key

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

  • The primary key must contain a unique value. If the primary key consists of multiple columns, the combination of values ​​in those columns must be unique.
  • Primary key column cannot contain NULL values. This means that you must declare the primary key column with the NOT NULL attribute. Otherwise, MySQL will force the primary key column as the default NOT NULL.
  • A table has only one primary key

Using the primary key in the database

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

  • Car owner ID (which will store the master key)
  • Name
  • Surname
  • Phone number

Now each customer record entered in the database will have a unique identification number, as well as first name, last name and phone number. A phone number is not unique enough to be used as a primary key, because even if it is unique to one person at a time, one can easily change the number, which means it could belong to someone else.

Example of primary key record

/* membuat record baru di tabel pelanggan */
INSERT INTO Pelanggan VALUES
('0004',
'Johnny',
'Cage',
'081-999-888-777');

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

Example of primary key record

External key

You now have a primary key that uniquely distinguishes car tenants from the rest. The only problem is that there is no real connection in the database between any Johnny Cage and the cars he rents. Therefore, there is still the possibility of an error.

This is where foreign keys come into play. Using the primary key to resolve the property ambiguity issue can only be done if the primary key is duplicated as a foreign key.

What are external keys?

In the SQL database management system, a foreign key is a unique identifier or combination of unique identifiers that connects two or more tables in the 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 foreign keys, you must first identify which tables are topics and objects in their relationships.

Read also: The difference between relational and non-relational databases

Back to the car rental database, to connect each customer to the correct car, you need to understand that the customer (subject) is renting a car (object). Therefore, the foreign key must exist in the machine table. SQL code that generates a table with a foreign key is slightly different from the norm.

Creating a foreign key table Example

/* membuat tabel mobil baru di database rental mobil */
CREATE TABLE Mobil
(
NomorLisensi varchar(30) NOT NULL PRIMARY KEY,
JenisMobil varchar(30) NOT NULL,
IDPelanggan varchar(30), FOREIGN KEY (IDPelanggan) REFERENCES Pelanggan(IDPelanggan)
);
Create a table with a compound 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 that is linked to the new table. To add a record to a new table, you need to make sure that the value in the foreign key field matches the value in the primary key field in the original table.

Example of adding a foreign key record

/* membuat record baru di tabel mobil */
INSERT INTO Mobil VALUES
('100012',
'Mercedes-Benz',
'0004');

The above code creates a new record in the table Mobile again, it produces the following results.

Example of adding a foreign key record

From the table above, you can correctly identify Johnny Cage who rented a Mercedes-Benz by the foreign key in the file.

Foreign key in advance

There are two other ways to use foreign keys in databases. If you review the foreign key definition above, you’ll find that a foreign key can be a unique identifier or a combination of unique identifiers.

Going back to the example of the car rental database, you will see that creating a new record (of the same car) every time a customer rents that car cancels out the purpose of the table. Mobile. If a car is to be sold and once sold to a single customer, the existing database is perfect, but since the car is a rental, there are better ways to display this data.

Compound key

A composite key has two or more unique identifiers. In a relational database, there will be instances where the use of a single foreign key does not adequately represent the relationships that exist in that database. In the car rental example, the most practical approach is to create a new table that stores the rental details. In order for the information in the car rental table to be useful, it must be linked to the car and customer table.

Create a table with a compound foreign key

/* membuat tabel RentalMobil di database rental mobil */
CREATE TABLE RentalMobil
 (
 TanggalSewa DATE NOT NULL,
 NomorLisensi varchar(30) NOT NULL,FOREIGN KEY (NomorLisensi) REFERENCES mobil(NomorLisensi),
 IDPelanggan varchar(30) NOT NULL, FOREIGN KEY (IDPelanggan) REFERENCES pelanggan(IDPelanggan),
 PRIMARY KEY (TanggalSewa, NomorLisensi, IDPelanggan)
 );
Creating a foreign key table Example

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

All 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 Customer Registration number and Rental date not a good combination) multiple customers can rent the same car on the same day (so NomorLisense and Rental date not a good combination).

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

Foreign primary key

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

Suppose Toni (who works for a car rental company) is in the company’s database under the employees table. After a few years, he became a supervisor and was added to the supervisor’s table. Toni is still employed and still has the same ID number. So Toni’s employee ID is now in the supervisor 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 Toni ID number now that is a supervisor).

The difference between the primary key and the foreign key

The main key External key
A primary key constraint is a column or group of columns that uniquely identifies each row in a relational database management system table. A foreign key is a column that creates a relationship between two tables.
This helps you to uniquely identify the entries in the table. It is a field in one table that is the primary key of another table.
The primary key never accepts a null value. External keys can accept multiple null values.
The primary key is a grouped index, and the data in the DBMS table is physically organized in the order of the grouped index. External keys cannot be automatically indexed, grouped or ungrouped
You can have a primary key in a table. You can have multiple foreign keys in one table.
Primary key values ​​cannot be removed from the parent table. Foreign key values ​​can be removed from child tables.
You can specify the primary key in the temporary table by default. You cannot specify foreign keys in local or global temporary tables.
Adalah clustered index primary key. By default, it is not a grouped index.
No two rows can have identical values ​​for the primary key. External keys may contain duplicate values.
There are no restrictions on entering values ​​in the table columns. When entering any value in the foreign key table, make sure it is in the primary key column.

Conclusion

So what are primary 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 each other. While a foreign key is an attribute (or set of attributes) that completes a relationship pointing 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 a database. Understand the basic forms of primary and foreign keys and they are even more complex.


So many articles What is the primary key and the foreign key in the SQL database. Looking forward to more interesting articles and don’t forget to share this article with your friends. Thank you…

Leave a Comment

/* */