SQL Server BasicsWhat is SQL Server database?What is RDBMS?What is Normalization?Why we use Denormalization?What_is_SQL?What is PL/SQL?Difference between SQL and PL/SQLDatabase TableOne to One RelationshipOne to Many RelationshipMany to Many RelationshipMany to One RelationshipString Data TypesNumber Data TypesDate Data TypesOther Data TypesCreate DatabaseDrop DatabaseCreating and Managing Users in SQL ServerCreate TableAlter TableDrop TableConstraints in SQL serverPrimary KeyForeign KeyUnique KeyCandidate KeyComposite KeyDifference between primary key and candidate keyPrimary key and foreign key relationshipSurrogate KeyCascading Referential Integrity ConstraintsSelf Referential Integrity ConstraintsInsert into statementInsert multiple recordsUpdate statementDelete statementTruncate statementDifference between Delete and TruncateAlias in SQL ServerSelect statementSelect DistinctSelect TopSelect IntoNull Functions(ISNULL(),NULLIF(),COALESCE())Sub QueryIdentity ColumnSequence objectDifference between sequence and identity columnSQL Server ClausesWHERE ClauseOrder By ClauseTop N ClauseGroup By ClauseHaving ClauseDifference between Where and HavingSQL Server OperatorsArithmetic OperatorsComparison OperatorsLogical OperatorsBitwise OperatorsAny OperatorsAll OperatorsUnion OperatorsUnion All OperatorsDifference between Union and Union AllIntersect OperatorExcept OperatorDifference between Except and IntersectJoinsInner JoinLeft JoinRight JoinFull JoinSelf JoinCross JoinViewsWhat are views?Create views using SSMSIndexed ViewsComplex ViewsCheck Option in ViewCheck Encryption in ViewSchema Binding Option in ViewRead-only ViewsUpdatable ViewsAdvantages and disadvantages of viewsCreate multiple views on one tableCan we implement index on views?Can we Perform Insert, update, delete operation on views?Stored Procedure and FunctionsWhat are Stored Procedures?Why we use stored procedures?Passing parameters to Stored procedureUser-Defined FunctionsDifference between UDF and Stored procedurePre-Defined Functions@@Indentity and Scope_IndentityNULLIF, ISNULL and COALESCE
Previous
Next

What is the Relationship Between Primary Key and Foreign Key?

In relational databases, the relationship between a Primary Key and a Foreign Key is one of the most important concepts. It helps connect tables, maintain data integrity, and ensure that your database is well-organized and efficient. Let’s break it down in simple terms with examples.

Primary Key: The Unique Identifier

A Primary Key is a column (or a set of columns) in a table that uniquely identifies each row. Think of it as a unique ID for every record in the table. Here’s what makes a primary key special:

  • Uniqueness: No two rows can have the same primary key value.
  • Non-Nullability: A primary key column cannot have NULL values.
  • Single per Table: A table can have only one primary key.
  • Automatic Indexing: SQL Server automatically creates a clustered index on the primary key, making data retrieval faster.

Example of a Primary Key

CREATE TABLE Students (
StudentID INT PRIMARY KEY,
Name VARCHAR(50),
Age INT
);

In this example, StudentID is the primary key. Each student has a unique ID, and no two students can share the same StudentID.

Foreign Key: The Connector

A Foreign Key is a column (or a set of columns) in one table that refers to the primary key of another table. It creates a link between two tables, ensuring that the data in both tables stays consistent.

Here’s what makes a foreign key special:

  • References a Primary Key: The foreign key column(s) in one table point to the primary key column(s) in another table.
  • Maintains Referential Integrity: Ensures that the data in the foreign key column(s) matches the data in the referenced primary key column(s).
  • Allows NULL Values: A foreign key can have NULL values, meaning a row doesn’t have to be linked to another table.

Example of a Foreign Key

CREATE TABLE Enrollments (
EnrollmentID INT PRIMARY KEY,
StudentID INT FOREIGN KEY REFERENCES Students(StudentID),
CourseName VARCHAR(50)
);

In this example:

  • StudentID in the Enrollments table is a foreign key.
  • It references the StudentID primary key in the Students table.
  • This ensures that every enrollment is linked to a valid student.

How Primary Key and Foreign Key Work Together

The relationship between a primary key and a foreign key can be summarized as follows:

  • Primary Key as a Target: The primary key in one table becomes the target for the foreign key in another table.
  • Foreign Key as a Reference: The foreign key refers to the primary key to establish a relationship between the two tables.
  • Data Integrity: The foreign key ensures that the data in the referencing table (e.g., Enrollments) corresponds to the data in the referenced table (e.g., Students).
  • Logical Connections: This relationship allows tables to be connected, enabling meaningful associations between data.

Example of Primary Key and Foreign Key Relationship

Let’s take a real-world example to understand this better.

Tables:

Customers Table (Primary Key: CustomerID)

CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
Name VARCHAR(50),
Email VARCHAR(100)
);

Orders Table (Foreign Key: CustomerID)

CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
OrderDate DATE,
CustomerID INT FOREIGN KEY REFERENCES Customers(CustomerID)
);

Explanation:

  • The Customers table has a primary key (CustomerID) to uniquely identify each customer.
  • The Orders table has a foreign key (CustomerID) that references the CustomerID in the Customers table.
  • This ensures that every order is linked to a valid customer.

FAQs About Primary Key and Foreign Key

1. Can a foreign key reference a non-primary key column?

No, a foreign key must reference a primary key or a unique key in another table.

2. Can a foreign key have duplicate values?

Yes, a foreign key can have duplicate values, as long as they match the referenced primary key values.

3. What happens if I delete a primary key record referenced by a foreign key?

It depends on the database settings. You can configure the foreign key to:

  • Cascade Delete: Automatically delete related foreign key records.
  • Restrict Delete: Prevent deletion of the primary key record if it’s referenced by a foreign key.

4. Can a table have multiple foreign keys?

Yes, a table can have multiple foreign keys, each referencing a primary key in a different table.

5. What is the difference between a primary key and a foreign key?

A primary key uniquely identifies rows in its own table, while a foreign key references the primary key in another table.

Best Practices for Using Primary Key and Foreign Key

  1. Use Meaningful Primary Keys: Choose primary keys that are unique and stable (e.g., an auto-incremented ID or a UUID).
  2. Avoid Updating Primary Keys: Changing primary keys can disrupt relationships and data integrity.
  3. Use Indexes on Foreign Keys: Indexing foreign key columns improves query performance when joining tables.
  4. Enforce Referential Integrity: Always define foreign key constraints to ensure data consistency.
  5. Use Cascade Options Carefully: Use ON DELETE CASCADE or ON UPDATE CASCADE only when necessary, as they can lead to unintended data loss.
  6. Avoid Circular References: Ensure that foreign key relationships do not create loops between tables.
Points to Remember:
  1. A primary key uniquely identifies rows in a table, while a foreign key links rows between tables.
  2. Foreign keys ensure referential integrity by enforcing constraints on the data.
  3. A table can have only one primary key but multiple foreign keys.
  4. Always define foreign key constraints to maintain data consistency.
  5. Use indexing on foreign key columns to improve query performance.

Example of a Complex Relationship

Let’s look at a more complex example involving multiple tables:

Tables:

Departments Table (Primary Key: DepartmentID)

CREATE TABLE Departments (
DepartmentID INT PRIMARY KEY,
DepartmentName VARCHAR(50)
);

Employees Table (Primary Key: EmployeeID, Foreign Key: DepartmentID)

CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
Name VARCHAR(50),
DepartmentID INT FOREIGN KEY REFERENCES Departments(DepartmentID)
);

Projects Table (Primary Key: ProjectID, Foreign Key: EmployeeID)

CREATE TABLE Projects (
ProjectID INT PRIMARY KEY,
ProjectName VARCHAR(50),
EmployeeID INT FOREIGN KEY REFERENCES Employees(EmployeeID)
);

Explanation:

  • The Departments table has a primary key (DepartmentID).
  • The Employees table has a foreign key (DepartmentID) that references the Departments table.
  • The Projects table has a foreign key (EmployeeID) that references the Employees table.
  • This creates a chain of relationships, ensuring data consistency across all tables.

Conclusion

The relationship between a primary key and a foreign key is the backbone of relational databases. It allows you to connect tables, maintain data integrity, and retrieve meaningful information through joins. By understanding how primary keys and foreign keys work together, you can design efficient and reliable databases.

Remember to follow best practices, such as using meaningful primary keys, enforcing referential integrity, and indexing foreign key columns. With these principles in mind, you’ll be able to create robust database structures that are easy to manage and query. Happy coding!

Previous
Next