SQL Foreign Key

A foreign key in SQL is a field (or a collection of fields) in a table that uniquely identifies a row of another table. The table containing the foreign key is called the "child table," and the table containing the candidate key is called the "referenced" or "parent table". Foreign keys are used to enforce referential integrity in the database and create relationships between tables.

Creating a Foreign Key When the Table Already Exists

If a table is already created and you want to add a foreign key, you can use the ALTER TABLE statement:

ALTER TABLE child_table
ADD FOREIGN KEY (fk_column) REFERENCES parent_table(parent_key_column);

Creating a Foreign Key While Creating a New Table

You can also specify the foreign key when you are creating the table:

CREATE TABLE child_table(
    ... -- other columns
    fk_column data_type,
    FOREIGN KEY (fk_column) REFERENCES parent_table(parent_key_column)
);

In both these examples, fk_column is the column in the child table that you want to set up as a foreign key, and parent_key_column is the column in the parent table that the foreign key refers to.

Example

Consider two related tables, Orders and Customers:

Customers table:

ID	NAME
1	John
2	Jane

Orders table:

OrderID	Product	CustomerID
1	Apples	1
2	Bananas	2
3	Grapes	1

You can create a foreign key in the Orders table that references the ID of the Customers table, linking each order to a customer:

ALTER TABLE Orders
ADD FOREIGN KEY (CustomerID) REFERENCES Customers(ID);

After this operation, the DBMS will prevent any operation that would break the link between Orders and Customers.

Note: Syntax might slightly vary depending on the specific SQL dialect (MySQL, SQL Server, PostgreSQL, etc.) you are using. Always consult the specific documentation for more details.

Creating a Foreign Key in SQL:
- Description: Establishes a relationship between two tables by referencing the primary key of one table in another.
- Code Example:
```
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
```

Foreign Key Relationships in Relational Databases:

Description: Represents a connection between tables based on a shared column, enforcing referential integrity.

Code Example:

-- Customers table
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(255)
);

-- Orders table with Foreign Key
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

Referential Integrity with Foreign Key in SQL:
- Description: Ensures that relationships between tables remain consistent, preventing orphaned or dangling records.
- Code Example:
```
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
```

Foreign Key vs Primary Key in SQL:

Description: Primary Key uniquely identifies each record in a table, while Foreign Key establishes relationships between tables.

Code Example (Primary Key):

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(255)
);

Code Example (Foreign Key):

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

Cascading Actions with Foreign Key Constraints:
- Description: Specifies actions to be performed automatically on related records when a referenced record is modified or deleted.
- Code Example (Cascading Delete):
```
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) ON DELETE CASCADE
);
```
Modifying Foreign Key Constraints in SQL:
- Description: Allows alterations to Foreign Key constraints, such as changing the referenced table or columns.
- Code Example (Modify):
```
-- Modify Foreign Key reference
ALTER TABLE Orders
DROP FOREIGN KEY FK_Customer,
ADD FOREIGN KEY (NewCustomerID) REFERENCES NewCustomers(NewCustomerID);
```
Dropping a Foreign Key in SQL:
- Description: Removes a Foreign Key constraint from a table.
- Code Example:
```
ALTER TABLE Orders
DROP FOREIGN KEY FK_Customer;
```
Checking Foreign Key Constraints in SQL:
- Description: Verifies the existence and details of Foreign Key constraints on a table.
- Code Example:
```
-- Check Foreign Key constraints
SHOW CREATE TABLE Orders;
```