How to best implement a 1:1 relationship in a RDBMS?

To solve this, I would start with the standard many-to-many relationship layout TableA AId AInfo TableB BId BInfo TableA2B AId BId I would then use triggers, unique indexes, constraints to enforce the 1:1 relationship in the table. The exact method would depend on the system needs The reason I like this format is that many relationships have attributes as part of the relationship which are not part of the entities. This pattern allows for that now and in the future For example: a person works for a company.

The relationship has a hire date which does not fit with the person entity or the company entity.

To solve this, I would start with the standard many-to-many relationship layout. TableA AId AInfo TableB BId BInfo TableA2B AId BId I would then use triggers, unique indexes, constraints to enforce the 1:1 relationship in the table. The exact method would depend on the system needs.

The reason I like this format is that many relationships have attributes as part of the relationship which are not part of the entities. This pattern allows for that now and in the future. For example: a person works for a company.

The relationship has a hire date which does not fit with the person entity or the company entity.

So far the best one! – Vilx- Nov 13 '09 at 13:19.

CREATE TABLE A (id INT NOT NULL PRIMARY KEY, field1, …) CREATE TABLE B (id INT NOT NULL PRIMARY KEY, field1, …) CREATE TABLE AB (aid INT NOT NULL, bid INT NOT NULL, CONSTRAINT pk_ab PRIMARY KEY (aid, bid), CONSTRAINT ux_a UNIQUE (aid), CONSTRAINT ux_b UNIQUE (bid), CONSTRAINT fk_aid_a FOREIGN KEY (aid) REFERENCES A, CONSTRAINT fk_bid_b FOREIGN KEY (bid) REFERENCES B ).

I think the schema would look like this: create table A ( A_id integer primary key, ... ); create table B ( B_id integer primary key, A_id integer references A (A_id), ... ); alter table B add constraint c1 unique(A_id); B can only reference one row in A, and since the field is unique, A can only be referenced by one row in B. B. A_id is nullable, so rows can exist in A and B that don't reference each other.

The unique constraint doesn't preclude multiple NULL records existing. A unique constraint ensures that the values are all either unique, or NULL.

But since it is unique, you can't have two rows with NULLs in them! – Vilx- Nov 13 '09 at 13:16 @spoonmeiser : my answer was not holding that added requirement, I guess I read over it, I deleted it since it was not usefull for this question, tx for your remakr – Peter Nov 13 '09 at 13:17 @Vilx: yes you can. A unique constraint implies that all values are unique, but you can have multiple NULLs because you can't compare NULL to NULL.

– SpoonMeiser Nov 14 '09 at 14:56.

Nullable foreign key with a unique constraint at one or both ends depending (at boths ends is interesting! ).

Yeah, that's what we have now. A foreign key at both ends. It's also awkward when you try to establish/break a link.

Has to resort to triggers 'n stuff. I was hoping for a more elegant weapon... – Vilx- Nov 13 '09 at 13:05 Why awkward? Two updates in a single transaction should allow you to break and / or reconfigure a link.

– Larry Lustig Nov 13 '09 at 13:39 Exactly. Two updates. Awkward.

:) – Vilx- Nov 13 '09 at 14:00 And error-prone, because you can forget to do one somwhere. – Vilx- Nov 13 '09 at 14:01 Exactly - one end is probably more elegant but then you're making an assertion that one end is notionally a parent of the other which may not, in fact, be the case. – Murph Nov 13 '09 at 15:13.

Separate join table of foreign key referencing A against foreign key referencing B, both columns with UNIQUE constraint. So either a link between two entities exists and is the only link for either of them, or no link exists so there's no row in the table.

– Vilx- Nov 13 '09 at 13:31 Umm. .. @Vilx? This is the same solution as the one by Darryl you just praised!

– Larry Lustig Nov 13 '09 at 13:37 That's what I mean. You were too slow at posting this and someone else already submitted the exactly same solution...20 minutes earlier. – Vilx- Nov 13 '09 at 13:38 Yes, looks like Darryl's is the same idea; I didn't spot it at the time.

You wouldn't need any complicated triggers to enforce it though, the UNIQUE foreign keys do that by themselves. – bobince Nov 13 '09 at 16:38 @Vilx: Oops, I get you. I thought you were suggesting that @bobince's solutino was too slow.

– Larry Lustig Nov 13 '097 at 15:09.

I would use the solution proposed by Darryl: TableA AId AInfo TableB BId BInfo TableA2B AId BId and then just add unique constrain on AId in tableA2B and BId on tableA2B alter TableA2B add constraint ucAId unique(AId) alter TableA2B add constraint ucBId unique(BId) I think that would solve your problem The tableA entries that are not linked to any tableB entries would simply not be present in the TableA2B similarly tableB entries not linked to tableA. The constrains would enforce maximum one link from tableA to tableB or tableB to tableA.

There are IMO two different cases to consider. Case one is best pictured with a monogamous marriage: the two objects are created independendly, and at some point in time, they are joined; later, they might be detached, and possible joined with other objects. For such a relationship, I'd propose the A2B table approach used by many others here.

Case two is pictured with twins: if the two objects are connected, they are so since birth and they are so till one of them dies. For that case, you could choose to simply give both of them the same primary key during creation (e.g. In Oracle by selecting one value from a sequence and using it as ID for both tables).

A foreign key at both ends. It's also awkward when you try to establish/break a link. Has to resort to triggers 'n stuff.

I was hoping for a more elegant weapon. " You will not find such an elegant weapon in the universe of SQL-based systems. Darryl Peterson's response shows the solution that is logically correct.

But some cases of "changing a link" can become a nightmare in SQL due to its lack of support for the TTM concept of "multiple assignment".

There is a great blog post by Tony Rogerson on this: How to Create a One-to-One Relationship in SQL Server using DRI, Triggers and Views.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.