Understanding Foreign Keys, Primary Key Alterations, and Conditional Inserts in SQL

Relational databases thrive on well-defined relationships. In this article, we’ll explore three key aspects of modern database design:

1. How to map foreign keys—including composite keys
2. How to add a new primary column to an existing table
3. How to insert data only when foreign key values are not unique

Whether you’re using PostgreSQL, MySQL, or MSSQL, these strategies apply across the board and are essential for any serious database developer.

🔗 1. Foreign Key Mapping: Single and Composite Keys

Foreign keys (FKs) enforce referential integrity between tables. Here’s how to correctly define them in SQL.

✅ Single Foreign Key Example

CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(100)
);

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(100),
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

Diagram:

departments
+---------+------------+
| dept_id | dept_name  |
+---------+------------+
      ▲
      |
employees
+--------+----------+---------+
| emp_id | emp_name | dept_id |
+--------+----------+---------+

🔁 Composite Foreign Key Example

Sometimes, a primary key is made of multiple columns. To reference it, the foreign key must also use both columns in the same order.

CREATE TABLE accounts (
    acc_num INT,
    acc_type INT,
    acc_descr VARCHAR(100),
    PRIMARY KEY (acc_num, acc_type)
);

CREATE TABLE sub_accounts (
    sub_acc INT PRIMARY KEY,
    ref_num INT NOT NULL,
    ref_type INT NOT NULL,
    sub_descr VARCHAR(100),
    FOREIGN KEY (ref_num, ref_type) REFERENCES accounts(acc_num, acc_type)
);

🧱 2. Alter Table to Add a Primary Key Column

Suppose you’re dealing with a legacy table that has no PRIMARY KEY, or the existing primary key is no longer sufficient. You can safely add one.

✅ Step-by-Step: Adding a New Primary Key Column

ALTER TABLE employees
ADD emp_uid INT IDENTITY(1,1); -- Use AUTO_INCREMENT in MySQL

ALTER TABLE employees
ADD CONSTRAINT pk_emp_uid PRIMARY KEY (emp_uid);

🔎 Note: In PostgreSQL, use SERIAL instead of IDENTITY, and in MySQL use AUTO_INCREMENT.

Before and After Diagram:

Before:
+--------+----------+---------+
| emp_id | emp_name | dept_id |
+--------+----------+---------+

After:
+--------+----------+---------+---------+
| emp_id | emp_name | dept_id | emp_uid |
+--------+----------+---------+---------+
                                ▲ PK

✅ 3. Insert Only if Foreign Key Value is Not Unique

This is a special case where you want to insert a new row only if the FK value is already duplicated in the table (i.e., not unique).

✅ SQL Example (PostgreSQL / MySQL Compatible)

Suppose child_table references parent_table(fk_column). Insert into child_table only if fk_column has multiple existing rows.

INSERT INTO child_table (fk_column, value)
SELECT 42, 'Sample value'
WHERE EXISTS (
    SELECT fk_column
    FROM child_table
    GROUP BY fk_column
    HAVING COUNT(*) > 1 AND fk_column = 42
);

Explanation:

• This checks if fk_column = 42 exists more than once in child_table.
• If yes, it inserts the new row.

✅ Bonus: How to Check Foreign Keys Programmatically

-- MSSQL
SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS;

-- PostgreSQL
SELECT
    conname AS constraint_name,
    conrelid::regclass AS table_name,
    confrelid::regclass AS referenced_table
FROM pg_constraint
WHERE contype = 'f';

🔚 Conclusion

• Foreign keys help maintain data consistency.
• Composite keys require careful matching in foreign key definitions.
• Adding a PRIMARY KEY via ALTER TABLE is straightforward but must be carefully planned.
• Conditional inserts based on FK duplication can enforce custom business logic.