What is difference between foreign key and reference key?

Breaking down terminologies and best usage

Terminology: Foreign versus Reference keys

The term "reference key" isn't the superstar of SQL's dictionary. However, you might spot it interchangeably fancied as foreign key or even as a primary key, contextual convenience being its primary driver.

Deciding between column-level and table-level constraints

Your trusty allies in this battle are your database system and your need for legibility. MySQL stands by you only for table-level foreign key constraints. Yet, Postgres and SQL Server dual-wield, fighting for you on both column and table levels.

A column-level constraint joins the fray inline with the column:

-- I'm a column-level constraint!
-- Small, nimble, and single-minded, I focus on my column alone.
CREATE TABLE Orders (
    OrderID int PRIMARY KEY,
    CustomerID int REFERENCES Customers(CustomerID)
);


The table-level constraint jumps into the battle later, after the columns:

-- I'm a table-level constraint!
-- I've leveled up and handle a larger scale, watching over all columns.
CREATE TABLE Orders (
    OrderID int,
    CustomerID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);


Table-level constraints are your knights in shining armor for multi-column foreign keys and complex relationships. In comparison, column-level constraints are the unsung heroes, silently enhancing readability for straightforward, single-column foreign key references. Let combative requirements and your RDBMS form your strategic command.

Navigating the database systems

MySQL's support for table-level constraints

Although MySQL isn't the champion of column-level foreign key constraints, it still supports table-level foreign key constraints – ensuring consistent relationships and a smooth user experience for queries.

PostgreSQL and SQL Server's versatility

Databases like PostgreSQL and SQL Server are like a Swiss army knife, boasting both column-level and table-level declarations—catapulting your database schema designs to greater heights.

Readability with REFERENCES sans FOREIGN KEY

You'd sometimes stumble upon the REFERENCES keyword much like a solo artist, without the FOREIGN KEY backup vocalist. This shorthand improves readability when the foreign key relationship concerns a lone column. Yet, always guide your database designs towards clear communication.

Handling complex foreign keys

For composite foreign keys channelling their inner octopus by grabbing onto multi-column primary keys, you'd need the strength of table-level constraints:

-- With great power, comes great responsibility 🕷️
-- A table-level constraint handling complex relationships like a pro!
CREATE TABLE OrderDetails (
    OrderID int,
    ProductID int,
    FOREIGN KEY (OrderID, ProductID) REFERENCES Orders(OrderID, ProductID)
);


This doesn't just link OrderDetails to Orders, it handcuffs them together for data integrity!

Best practices and referential integrity

Ensure you study up on your RDBMS documentation like you're prepping for finals. This understanding will guarantee your foreign keys are wielded properly for data integrity and utmost reliability.