How to create 1 to 1(or zero) relationship in SQL Server 2008

Question

I know how to create one to one relationship only problem is that it demands rows in both tables e.g. i have a table

emp_table => key1, key2, name, description
emp_address_table => key1, key2, address

system do not put mandatory constraints on address so an emp may exist without having his/her address, OR emp may have maximum one address not more that that so it seems

1:1(0) relationship

please let me know how can i do that in sql server 2008

my bad i was creating 1 to 1 wrongly, it worked any way thanks for lot of answers — Mubashar, May 14 '12 at 11:26

score 2 · Accepted Answer · edited Feb 20 '14 at 00:50

2

You can't have a constraint that enforces the existence of a child row because you can't insert the two rows at the same time. I.e. whether you insert your employee, or employee address row first, you would have an invalid constraint at this point.

So all constraints in SQL are '0 or more' and never '1 or more' or 'exactly 1'.

You can enforce a '0 or 1' constraint by adding a UNIQUE constraint to the foreign key column in the emp_address table. This will ensure there can be at most one emp_address row that refers to a given employee.

edited Feb 20 '14 at 00:50

chopper

6,649
7
36
53

answered May 14 '12 at 11:04

Tim Rogers

21,297
6
52
68

That was the most dump question I ever asked, i was certainly not thinking right – Mubashar Mar 10 '14 at 03:35

score 1 · Answer 2 · answered May 14 '12 at 11:11

Use an association table. It's better practice anyway and you can use it too implement 0 to N relationships easily, where:

Absence of rows in the association table represents 0:0
Presence of a single row in the association table per entity represents 1 to 1
Presence of multiples rows in the association table (per entity) represents 1 to many

*emp_table*

key1
key2
name
description

*address_table*

addr_key
address

*emp_address_table*

key1
key2
addr_key

In your association table, make key1 and key2 a foreign key reference back to your employee table, and addr_key a foreign key reference back to the address table. If you want to enforce a 1 to 1 relationship, make key1, key2 a unique constraint on the association table. Otherwise, leave the unique constraint to represent 0 to many relationship.

score 0 · Answer 3 · answered May 14 '12 at 11:05

0

Try

SELECT * 
FROM emp_table et 
LEFT OUTER JOIN emp_address_table edt ON et.key1 = edt.key1 AND et.key2 = edt.key2

answered May 14 '12 at 11:05

bitoshi.n

2,278
1
16
16

score 0 · Answer 4 · answered May 14 '12 at 11:08

You can not enforce having row in child table by using constraints. (because you first insert row in parent table and only then in child table- its not one operation but 2, even though it most possibly one transaction)

But you can use stored procedure to insert data and you can do validation inside a procedure.

How to create 1 to 1(or zero) relationship in SQL Server 2008

4 Answers4