Primary key and foreign key in SQL are similar to essential tools for creating and maintaining databases. Every record in a table has a primary key, which serves as a unique identifier that facilitates easy identification. In the meantime, foreign keys—a vital component of both primary key and foreign keys in SQL—serve as links that carry data between database tables. This article discusses the significance of understanding primary and foreign keys in SQL and how they help to keep databases dependable and orderly.
Understanding primary key and foreign key in SQL is similar to having a collection of tools for developers to build robust, interconnected databases that process data efficiently and consistently.
What is Primary Key in SQL?
The primary key in SQL Server serves as a unique identifier for each row in a specific table, which is accomplished using a single column or a combination of columns. Additionally, this primary key generates a unique index for the data in the table. Notably, each table can only have one primary key, confirming its position as a unique and distinct identifier inside the table’s structure.
Syntax:
Create table <table name>(
Field <type> Primary key
.
.
Fields
)
What is Foreign Key in SQL?
A foreign key in SQL is similar to a connection between two tables. It is a field in one table that establishes a relationship with the main key in another table. By maintaining data consistency, this connection makes sure that the values in the foreign key correspond to those already present in the primary key. In a database, foreign keys aid in preserving the connections and accuracy of data by preserving the linkages between tables.
Syntax:
Create Table <Table name>(
Fields,
.
.
Field <type> References <Table name of primary key holder>(<Primary key field>)
)
Creating primary key in the new table:
Consider the creation of a new table named ‘Employee’ with the following columns: ‘EmployeeID,’ ‘FirstName,’ ‘Department,’ and ‘Salary.’
CREATE TABLE Employee (
EmployeeID int Not Null,
FirstName varchar(255) Not Null,
Department varchar(255),
Salary int,
Primary Key (EmployeeID)
);
In this scenario, the ‘EmployeeID’ column serves as the primary key for the ‘Employee’ table. It ensures that each employee record can be uniquely identified within the table.”
The provided SQL script is a table creation statement. When you execute this script, it will create an “Employee” table with the specified structure, and the “EmployeeID” column will be the primary key. However, the script itself doesn’t produce any output in the traditional sense. Instead, it defines the structure of the table.
If you want to view the newly created table or check its structure, you might use a SQL SELECT statement:
— Select all columns from the Employee table
SELECT * FROM Employee;
Keep in mind that this query assumes that the table has been successfully created. If there are any issues with the creation, error messages will be displayed to help identify and resolve the problem.
Creating primary key in the existing table:
Before establishing a foreign key relationship, let’s initiate the process by crafting a table called “Customer” with the following attributes: ‘CustomerID,’ ‘FirstName,’ ‘LastName,’ and ‘Email.’
CREATE TABLE Customer (
CustomerID int Not Null,
FirstName varchar(255) Not Null,
LastName varchar(255),
Email varchar(255),
);
Now that we’ve set up the “Customer” table, we will enhance it by introducing a primary key. The following SQL command achieves this: ALTER TABLE Customer ADD PRIMARY KEY (CustomerID);
Executing the above command confirms the successful creation of the primary key field “CustomerID” within the “Customer” table.
Now, let’s consider another table named “Order Details”:
CREATE TABLE OrderDetails (
OrderID varchar(10) Not Null,
CustomerID int,
Product varchar(255),
Quantity int,
TotalAmount decimal(10, 2)
);
To improve data integrity, a foreign key reference to the “Customer” table in the “Order Details” table can be added:ALTER TABLE OrderDetails ADD FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID);
In this example, the “Order Details” table’s “CustomerID” column is linked as a foreign key to the “CustomerID” primary key in the “Customer” table. In order to guarantee referential integrity and data consistency, this relationship creates a link between orders and customers.
Creating foreign key in the new table:
Let’s create a table named “Meeting Schedule” with the following structure:
CREATE TABLE “Meeting Schedule” (
MeetingID int Not Null Primary Key,
DayOfWeek varchar(255),
MeetingTime varchar(255),
EmployeeID int References Employee(EmployeeID)
);
In this scenario, the “EmployeeID” column within the “Meeting Schedule” table acts as a foreign key. It establishes a link to the “EmployeeID” column in the “Employee” table, creating a relationship between meeting schedules and employee information. This foreign key association ensures data integrity by guaranteeing that each record in the “Meeting Schedule” table corresponds to a valid employee ID in the associated “Employee” table.
Creating foreign key in the existing table:
Before proceeding to implement the foreign key constraint, let’s start by crafting a table named “Purchase Records” using the following SQL commands:
CREATE TABLE PurchaseRecords (
RecordID int Not Null,
TotalAmount varchar(255)
);
In this context, we have successfully created the “Purchase Records” table, which is intended to store information related to various purchase transactions. The “RecordID” column uniquely identifies each record in this table.
Now, let’s advance to the next step in database design by creating a foreign key. Specifically, we will establish a link with the primary key “TransactionID” from a parent table named “Transactions.” The objective is to ensure referential integrity and create a relationship between the “Purchase Records” and “Transactions” tables.
Here is the SQL command to declare the “Transaction_Purchase_Link” foreign key constraint within the “Purchase Records” table:
ALTER TABLE PurchaseRecords
ADD CONSTRAINT Transaction_Purchase_Link
FOREIGN KEY (RecordID) REFERENCES Transactions(TransactionID);
In this example, the “RecordID” column in the “Purchase Records” table is designated as a foreign key, referring to the primary key “TransactionID” in the “Transactions” table. This foreign key constraint enforces that each entry in the “Purchase Records” table corresponds to a valid transaction ID in the associated “Transactions” table, promoting data consistency and integrity.
Conclusion
To sum up, understanding primary key and foreign key in SQL is essential for managing and designing databases effectively. A table’s primary key, which serves as a unique identifier, protects the integrity of the data and makes retrieval easier. Foreign keys create associations between tables at the same time, fostering referential integrity by connecting entries between related entities.
In order to maintain structured and connected databases, this article highlights the importance of primary key and foreign key in SQL with examples. A strong grasp of primary key and foreign key in SQL allows database developers to build strong, networked structures that support efficient data management and guarantee data consistency. For those seeking expertise, consider Oracle SQL Training in Chennai to enhance your skills.
