1:1 relationships - SQL Anywhere

[indigodream]

Hi All,

How are 1:1 relationships represented in relational models? Do both sides get a foreign key?

Many thanks,
Barry

[skhanal]

No, only one has foreign key (the referencing table). It also needs to have to unique key defined to enforce uniqueness.

[indigodream]

Ok. So what about implementation - is there still only one side that 'knows' about the relationship?

[rmiao]

Put pkey on parent table and fkey on child table, they both know.

[indigodream]

Moving back to the realational model, what if the 1:1 was mandatory on both sides. Does this make any difference?

[rmiao]

Then put unique constraint on fkey column on child table.

[skhanal]

No make it mandatory you have to make it NOT NULL in child table, and primary key on parent table will prevent NULL value anyway.

[indigodream]

i've got to be honest, you're using more advanced terminology than I'm used to. To me, parent and child means supertype and subtype, yet I haven't mentioned this arrangement at all.

[skhanal]

Supertype and subtype are not exactly same as parent and child, but related. Supertype/subtype is logical concept and parent/child is physical implementation concept. Let me give you an example.

If you have a table called Person with information such as names, etc. and Employee table as subtype with employee specific information. Then Parent is supertype and employee is subtype. To enforce this relation, you create a foreign key in Employee table to reference corresponding record in person table. Now Person is parent table and Employee is child table.