Implementing 1 to 1 relationships

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

SQL Server 2000 Forums

SQL Server Development (2000)

Implementing 1 to 1 relationships

Author

Topic

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2006-09-29 : 06:34:46

Hi,

I have table called server:
Server(ServerID, ServerName, ServerDescription)

and a table called Note:

Note(NoteID,NoteContent, ServerID)

I would like to implement a one to one relationship where a Server may have 0 or 1 note, but a note must have only 1 server.

My understanding was that the attribute ServerID in Note should be a Unique constraint, but that does not explain how SQL Server knows that Server with ServerID of say, 23, in relation Server is the same Server with ServerID of 23 in Note.

How do I implement this one to one relationship.

I've spent a whole year studying database theory, but am having difficulty bridging the theory to practise.

Thanks

Drew

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2006-09-29 : 06:51:47

Should it be:

Server(ServerID, ServerName, ServerDescription)

Note(NoteID, ServerID, NoteContent)

Thanks

Drew

nr
SQLTeam MVY

12543 Posts

Posted - 2006-09-29 : 06:52:17

Have a look at foreign keys - it's the same keyword you should have come across in the theory.

severid will be unique and a foreign key.
By making serverid an attribute of the note you are ensuring a note only has one server.
By making serverid unique you make sure that a server only has one note.
By making the serverid a foreign key you ensure that the server exists.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-29 : 06:54:30

It doesn't with only a Unique Constraint. But, If you also put a foreign key to Server table, the system knows.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-29 : 06:57:59

The order of columns doesn't matter, if that is what you mean.
Strange that you put one year of effort to study database design, and doesn't get the hints we give.

Peter Larsson
Helsingborg, Sweden

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2006-09-29 : 06:59:03

quote:
Originally posted by Peso

Strange that you put one year of effort to study database design, and doesn't get the hints we give.

Peter Larsson
Helsingborg, Sweden

Gees, thanks. You make me feel a lot better.

Thanks

Drew

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-29 : 07:06:28

Sorry.
Have you managed to embrace the suggestions and did they work out for you?

Peter Larsson
Helsingborg, Sweden

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2006-09-29 : 07:46:27

Yes, thank you Peter.

So just to be completely comfortable (bare with me)...

What you've said there, makes ServerID and NoteID together a Candidate key, because it it has the properties of uniqueness
and minimality. By minimality, I mean that, as ServerID and NoteID is a combination of attributes, no attribute may be discarded from the
combined attributes without destroying the uniqueness.

So why not just make NoteID and ServerID a composite Primary Key of the relation Note?

Thanks

Drew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources