ERD 1:1 Relationship

Question

I am trying to create a 1:1 relationship, but not primary key to primary key. In table 1 I have a uniqueidentifier as a primary key. In table 2 I have an int as the primary key and a column that takes the uniqueidentifier from table 1. Everytime I drag and drop the relationship line and link table 1 to table 2 it creates a 1:N relationship: ie. tbl1.primarykey links to tbl2.column2. So I'm not linking primary key to primary key however I still want a 1:1 relationship.

 

How do I do that?

Thanks in advance.

Answer 1

There will always be a parent and child in any relationship.  So you will simply have one table reference the other, and then apply a constraint of some sort to enforce the cardinality.  Since you want 1-1, just add a UNIQUE constraint to the foreign key value...

create table parent
(
    parentId    int primary key
)
create table child
(
    childId     int primary key,
    parentId    int not null references parent(parentId) unique
)

insert into parent
select 1
union all
select 2

insert into child 
select 1,1
union all
select 2,2

insert into child
select 3,2

--error

Answer 2

Thanks a million man, totally worked. I do understand that there always has to be a parent child relationship I just didn't know how to make it a 1:1 on non primary key to primary key.

 

Thanks again.