one-to-one relationship in SQL

In a one-to-one relationship in SQL, only one table typically contains a foreign key that references the other table's primary key. You don't need to add an ID to both tables to establish the relationship. Here's how it works:

Scenario:
Table A and Table B have a one-to-one relationship.
Each record in Table A corresponds to exactly one record in Table B, and vice versa.
How it's typically implemented:
One table contains a foreign key:
One of the tables (usually the "dependent" or "secondary" table) will contain a foreign key column that references the primary key of the other table.
The foreign key enforces the one-to-one relationship by ensuring that a record in one table corresponds to only one record in the other table.
Example:
Let's say you have two tables: users and user_profiles.

The users table contains basic information about users (e.g., id, name).
The user_profiles table contains additional profile details for each user (e.g., bio, avatar).

1. Table: users

sql

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL
);

1. Table: user_profiles In a one-to-one relationship, the user_profiles table will contain a foreign key (user_id) that references the id column in the users table.

sql

CREATE TABLE user_profiles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT UNIQUE,  -- Foreign key that ensures a one-to-one relationship
    bio TEXT,
    avatar VARCHAR(255),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

Key Points:
user_id in user_profiles is a foreign key that references the id in the users table.
The UNIQUE constraint on user_id ensures that each user can have only one associated profile, enforcing the one-to-one relationship.
No need to have an additional ID in the users table for user_profiles, as the foreign key in the user_profiles table already establishes the relationship.
Why only one foreign key:
In a one-to-one relationship, either table can reference the other, but only one foreign key is necessary to enforce the relationship.
The foreign key ensures that there is only one record in the related table, and the UNIQUE constraint ensures that each row in the related table corresponds to only one row in the primary table.
Optional Inverse Case:
Sometimes the foreign key could be placed in either table based on the use case:

If the users table contains the foreign key (profile_id), you would link it to the user_profiles table's primary key, but typically the foreign key is placed in the "secondary" or "dependent" table to keep the primary table simpler.
Conclusion:
In a one-to-one relationship, you do not need IDs on both tables to establish the relationship. A foreign key in one table, combined with a UNIQUE constraint, will enforce the one-to-one relationship.

In a one-to-one relationship, the table that contains the foreign key is typically considered the owner of the relationship.

In this example:
The users table is the primary table that holds basic user information.
The user_profiles table contains additional profile details and has the foreign key (user_id) that references the users table.

So, in this case:
user_profiles is the owner of the relationship because it contains the foreign key (user_id) that links to users.
users is the referenced or parent table, but it does not directly "own" the relationship since it doesn’t contain a foreign key pointing to user_profiles.

Why user_profiles is the owner:
The table with the foreign key manages the relationship by holding the reference to the other table.
In a one-to-one relationship, the owner typically controls how the relationship is formed and maintained (e.g., whether to allow nulls, cascade deletes, etc.).

To Summarize:
Owner = The table with the foreign key (in this case, user_profiles).
Referenced/Parent = The table with the primary key being referenced (in this case, users).