Is it possible to implement a TRUE one-to-one relation?

Question

Consider the following model where a Customer should have one and only one Address and an Address should belong to one and only one Customer:

To implement it, as almost everybody in DB field says, Shared PK is the solution:

But I think it is a fake one-to-one relationship. Because nothing in terms of database relationship actually prevents deleting any row in table Address. So truely, it is 1..[0..1] not 1..1

Am I right? Is there any other way to implement a true 1..1 relation?

Update:

Why cascade delete is not a solution:

If we consider cascade delete as a solution we should put this on either of the tables. Let's say if a row is deleted from table Address, it causes corresponding row in table Customer to be deleted. it's okay but half of the solution. If a row in Customer is deleted, the corresponding row in Address should be deleted as well. This is the second half of the solution, and it obviously makes a cycle.

Answer 1

Beside my comment

• You could implement DELETE CASCADE See HOW

I realize there is also the problem of insert.

• You have to insert Customer first and then Address

So I think the best way if you really want a 1:1 is create a single table instead.

Customer

CustomerID
Name
Address
City

Answer 2

Sorry, is this meant to be a real-world database relationship? In all of the many databases I have ever built with customer data, there has always been real cases of either customers with multiple addresses, or more than one organisation at the same address.

I wouldn't want to lead you into a database modelling fallacy by suggesting anything different.

Answer 3

It's possible in principle to implement a true 1-1 data structure in some DBMSs. It's very difficult to add data or modify data in such a structure using standard SQL however. Standard SQL only permits one table to be updated at a time and therefore as soon as you insert a row into one or other table the intended constraint is broken.

Here are two examples. First using Tutorial D. Note that the comma between the two INSERT statements ensures that the 1-1 constraint is never broken:

VAR CUSTOMER REAL RELATION {
    id INTEGER} KEY{id};

VAR ADDRESS REAL RELATION {
    id INTEGER} KEY{id};

CONSTRAINT one_to_one (CUSTOMER{id} = ADDRESS{id});

INSERT CUSTOMER RELATION {
    TUPLE {id 1234}
    },
INSERT ADDRESS RELATION {
    TUPLE {id 1234}
    };

Now the same thing in SQL.

CREATE TABLE CUSTOMER (
    id INTEGER NOT NULL PRIMARY KEY);

CREATE TABLE ADDRESS (
    id INTEGER NOT NULL PRIMARY KEY);

INSERT INTO CUSTOMER (id)
    VALUES (1234);

INSERT INTO ADDRESS (id)
    VALUES (1234);

ALTER TABLE CUSTOMER ADD CONSTRAINT one_to_one_1
    FOREIGN KEY (id) REFERENCES ADDRESS (id);

ALTER TABLE ADDRESS ADD CONSTRAINT one_to_one_2
    FOREIGN KEY (id) REFERENCES CUSTOMER (id);

The SQL version uses two foreign key constraints, which is the only kind of multi-table constraint supported by most SQL DBMSs. It requires two INSERT statements which means I could only insert a row before adding the constraints, not after.

A strict one-to-one constraint probably isn't very useful in practice but it's actually just a special case of something more important and interesting: join dependency. A join dependency is effectively an "at least one" constraint between tables rather than "exactly one". In the world outside databases it is common to encounter examples of business rules that ought to be implemented as join dependencies ("each customer must have AT LEAST ONE addresss", "each order must have AT LEAST ONE item in it"). In SQL DBMSs it's hard or impossible to implement join dependencies. The usual solution is simply to ignore such business rules thus weakening the data integrity value of the database.

Answer 4

Yes, the "shared PK" idiom you show is for 1-to-0-or-1.

The straightforward way to have a true 1-to-1 correspondence is to have one table with Customer and Address as CKs (candidate keys). (Via UNIQUE NOT NULL and/or PRIMARY KEY.) You could offer the separate tables as views. Unfortunately typical DBMSs have restrictions on what you can do via the views, in particular re updating.

The relational way to have separate CUSTOMER and ADDRESS tables and a third table/association/relationship with Customer and Address columns as CKs plus FKs on Customer to and from CUSTOMER and on Address to and from ADDRESS (or equivalent constraint(s)). Unfortunately most DBMSs needlessly won't let you declare cycles in FKs and you cannot impose the constraints without triggers/complexity. (Ultimately, if you want to have proper integrity in a typical SQL database you need to use triggers and complex idioms.)

Entity-oriented design methods unfortunately artificially distinguish between entities, associations and properties. Here is an example where if you consider the simplest design to simply be the one table with PKs then you don't want to always have to have distinct tables for each entity. Or if you consider the simplest design to be the three tables (or even two) with the PKs and FKs (or some other constraint(s) for 1-to-1) then unfortunately typical DBMSs just don't declaratively/ergonomically support that particular design situation.

(Straightforward relational design is to have values (that are sometimes used as ids) 1-to-1 with application things but then just have whatever relevant application relationships/associations/relations and corresponding/representing tables/relations as needed to describe your application situations.)

Answer 5

Yes, what you say is true, the dependent side of a 1:1 relationship may not exist -- if only for the time it takes to create the dependent entity after creating the independent entity. In fact, all relationships may have a zero on one side or the other. You can even turn the relationship into a 1:m by placing the FK of the address in the Customer row and making the field not null. You can still have addresses that aren't referenced by any customer.

At first glance, a m:n may look like an exception. The intersection entry is generally defined so that neither FK can be null. But there can be customers and addresses both that have no entry referring to them. So this is really a 0..m:0..n relationship.

What of it? Everyone I've ever worked with has understood that "one" (as in 1:1) or "many" (as in 1:m or m:n) means "no more than this." There is no "exactly this, no more or less." For example, we can design a 1:3 relationship on paper. We cannot strictly enforce it in any database. We have to use triggers, stored procedures and/or scheduled tasks to seek out and call our attention to deviations. Execute a stored procedure weekly, for instance, that will seek and and flag or delete any such orphaned addresses.

Think of it like a "frictionless surface." It exists only on paper.

Answer 6

I see this question as a conceptual misunderstanding. Relations are between different things. Things with a "true 1-to-1 relation" are by definition aspects or attributes of the same thing, and belong in the same table. No, of course a person and and address are not the same, but if they are inseparable, and must always be inserted, deleted, or otherwise acted upon as a unit, then as data they are "the same thing". This is exactly what is described here.

Answer 7

Yes, and it's actually quite easy: just put both entities in the same table!

OTOH, if you need to keep them in separate tables for some reason, then you need a key in one table referencing¹ a key in another, and vice-versa. This, of course, represents a "chicken and egg" problem² which can be resolved by deferring the enforcement of FKs to the end of the transaction³. This works only on DBMSes that support deferred constraints (such as Oracle and PostgreSQL).

---

¹ Via a foreign key.

² Inserting a row in the first table is impossible because that would violate the referential integrity towards the second table, but inserting a row in the second table is impossible because that would violate the referential integrity towards the first table, etc... Ditto for deletion.

³ So you simply insert both rows, and then check both FKs.