Dear All,
How to create 1 to 1 relationship by sql statements? i.e. if I have one employee table, and one spouse table. They should be 1 to 1, except you are living in a special country...
My strategy is:

1. in employee table
empID (pk)
spoID (fk)

2. in spouse table
spoID (pk)
empID (fk)

but... seems wrong... any suggestions?

thx,

neil

create table Employee
(
emp_id number(2),
spouse_id number(2) UNIQUE,
primary key (emp_id),
Constraint Employee_FK_SPOUSE_ID foreign key (spouse_id) references Employee(emp_id)
);

r123456, please show a couple of rows of sample data for your scheme

if A points to B as a spouse, does B point to A?

and if your answer is yes, then please explain how you add them to the table without violating the constraints!

100 103 tom thumb
101 null joe bachelor
102 null mary spinster
103 100 betty thumb

assuming tom thumb got added before betty, how do you specify 103 as the fk if betty doesn't exist in the table yet?



i just thought that it would be helpful to people reading this thread to fully understand what they have to do to get this relationship working properly

and i think that it must be a reflexive relationship, i.e. if tom has betty as spouse, then betty must have tom as spouse

otherwise the "who is a spouse of whom" queries get too complicated

I do not consider the point you raised to be a problem. If employee '100' is inserted into the table, however the spouse of employee '100' is not present in the table, then null is inserted. Null in this context means "Not applicable", which is correct. The only problem that needs to be addressed, I believe, is ensuring that the following situation does not occur.

EMP_ID SPOUSE_ID
---------- ----------
1 3
2 1
3

no, the point i raised is not a problem

i was merely suggesting that the relationship cannot be established with a simple insert

... unless your DBMS supports deferred constraints.

Of course, even with monogamy people get divorced or widowed and remarry, so a separate marriage table is more realistic.