Help with a one to one relation

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with a one to one relation

Author

Topic

AurasD
Starting Member

23 Posts

Posted - 2009-01-13 : 14:57:45

Hello!I have a problem with a one to one relation.I have 2 tables: Humans which has the following fields:
HumanID - PK,identity,int
HumanFN - nvarchar(20),
HumanLN - nvarchar(20).

and Adresses which has the following fields:
AdressID - PK,identity,int
HumanID - int
HumanCity nvarchar(20).

I established a relation between the two tables through the HumanID field.But before I set the relation,I've created an index for the Adresses.HumanID field with the type Unique Key.The thing is that when I click on the Show Diagram Pane toolbar button and I add my tables,Sql Server 2005 shows me that I have a one to many relation between Humans and Adresses.But I want a one to one relation between them.How can I do it?Thank you

AurasD
Starting Member

23 Posts

Posted - 2009-01-13 : 15:25:23

In Access 2003 I've created the same database and it correctly shows a 1 to 1 relation.In Sql Server 2005 I've created the index in SSMS so it was not done in code.

AurasD
Starting Member

23 Posts

Posted - 2009-01-13 : 16:16:11

Guys there is really something fishy with SSMS.In the case of my table the Show Diagram Pane shows a different relation than Database Diagrams.I mean Database Diagram shows me the correct relation (1 to 1)
but what's with Show Diagram Pane?It's not modifying the relation,all though I delete and recreate the unique index for the Adresses.HumanID field.It keeps showing one to many relation but the Database Diagrams updates and show the correct relation.Does Show Diagram Pane is bugged or something like?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-01-14 : 10:59:35

I think you are are confusing an index with a foreign key?

Your Addresses table should be


AddressId PK,identiy,int
HumanId int, FOREIGN KEY -> humans.humanId
...
...
...
...

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-01-14 : 11:07:08

This should give you sample syntax:


IF OBJECT_ID('dbo._address') IS NOT NULL DROP TABLE _address
IF OBJECT_ID('dbo._humans') IS NOT NULL DROP TABLE _humans

CREATE TABLE _humans (
	[humanId] INT IDENTITY(1,1) PRIMARY KEY
	, [name] NVARCHAR(255)
	)

CREATE TABLE _address (
	[addressId] INT IDENTITY(1,1) PRIMARY KEY
	, [humanID] INT FOREIGN KEY REFERENCES _humans([humanId])
	, [line1] NVARCHAR(255)
	)

Note you have to drop _address before _humans because of the foreign key constraint.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

AurasD
Starting Member

23 Posts

Posted - 2009-01-14 : 12:11:25

But man I want to make that foreign key (Adresses.HumanID) unique so that I have a 1 to 1 relation between Humans and Adresses.So what confusion are you talking about?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-01-15 : 06:07:55

Sorry -- obviously didn't get what you meant there!

You can add a constraint to make address.humanId unique. Then there is only a 1 to 1 relation possible.

Does that help you?


IF OBJECT_ID('dbo._address') IS NOT NULL DROP TABLE _address
IF OBJECT_ID('dbo._humans') IS NOT NULL DROP TABLE _humans

CREATE TABLE _humans (
	[humanId] INT IDENTITY(1,1) PRIMARY KEY
	, [name] NVARCHAR(255)
	)

CREATE TABLE _address (
	[addressId] INT IDENTITY(1,1) PRIMARY KEY
	, [humanID] INT UNIQUE FOREIGN KEY REFERENCES _humans([humanId])
	, [line1] NVARCHAR(255)
	)

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-15 : 06:28:04

Also, If its a one to one mapping, I'd store it all in one table.

AurasD
Starting Member

23 Posts

Posted - 2009-01-15 : 13:21:46

Thx guys,I've done it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources