What Can be a Foreign Key in Another Table?

Answer: A foreign key in another table can be any field or combination of fields that references the primary key in a related table.

Identify Tables and Columns

Identify the referencing or child and referenced or parent tables along with their respective columns involved in the relationship.

Create Tables

Ensure both tables are created with appropriate columns. Use the CREATE TABLE statement to define tables, specifying primary keys. Example:

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(100),
    -- Other columns
);
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    -- Other columns
);

Assign Foreign Key

Use ALTER TABLE to add a foreign key constraint to the referencing table (Orders), referencing the CustomerID column to the Customers table’s CustomerID. Example:

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

Enforce Referential Integrity

Optionally, enforce referential integrity for consistency. For instance, ON DELETE CASCADE ensures associated orders are deleted if a customer is deleted. Confirm the foreign key relationship through database queries.

Conclusion

Assigning foreign keys in a relational database ensures data integrity by linking tables through constraints, guaranteeing each value in one table corresponds to an existing value in another table.

Article Tags :

DBMS

DBMS-MAQ