Zero-or-one to zero-or-one relationship

Question

Is there a way to create a 0 or 1 to 0 or 1 relationship in a database?

For example, let's say I have a table of managers and a table of artists. A manager can only manage one artist, and an artist can only have one manager. However, a manager can be without an artist (i.e. looking for an artist to manage) and an artist might not have a manager.

Is there any way to do this? Creating a foreign key in either table would just create a 1 to many relationship.

Answer 1

This is fairly simple.

You have a 1:1 relationship which may not exist.

So you have a unique foreign key which is nullable and therefore can be specified to point at nothing.

Something like:

CREATE TABLE manager (
    id int primary key,
    ....
);

CREATE TABLE artist (
     id int primary key,
     manager_id int unique references manager(id)
     ...
);

Now the artist cannot have more than one manager, and the manager cannot have more than one artist. And the artist may not have a manager.