relationships between tables
-
i'm having a problem defining a one-to-many relationship between two tables. the problem seems to be with the primary keys.
in the "one" table i have a primary key that is comprised of two fields, an ID and a Date. of these two fields, only the ID field exists in the other, "many" table. i want to link the tables using the ID field; but there seems to be a problem because the ID field alone is not the primary key of the table on the "one" side, but it IS the only field from the primary key that i want to be part of the relationship.
adding the Date field to the "many" table would not be an effective solution, as that data would be redundant, not to mention irrelevant, in that table; so i feel like there must be a way around this problem without adding a redundant field to a table.
when i think about it logically, i can't imagine why this should be difficult. can anybody shed any light here for me?
-
A primary key uniquely identifies a row in a table and is used to establish relationship to other tables.
Now when your primary key is of ID and Date (btw, why when you have an identity property defined on an int field in your parent table this should be sufficient enough to uniquely identify a row), to establish a relation your child table must also contain both columms. Simple as that.
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
<...adding the Date field to the "many" table would not be an effective solution, as that data would be redundant, not to mention irrelevant, in that table...>
I can't see why this should be the case. If the parent table stores date sensitive data about the business object with the given id ,then the child table must also contain date-sensitive information. If the child table relates to the parent object (identified by ID) but is date insensitive, then the parent table is non-normalised and should be broken up into the date sensitive and date insensitive parts. The latter can now have a PK of just the ID field, with which your child table can have an FK relationship without needing to add the redundant date field.
Tony Bater
Edited by - Tony Bater on 11/13/2003 03:05:47 AM
Tony -
From what I can gather (which isn't much), why not just make your Date field in the Parent Table the Primary Key? I can't understand why you have a Composite Key on that table!?? Either stick with "ID" or "Date" for your Primary Key.
-
Would it help if you made the ID column in the "one" table your primary key, then create an index on ID + Date columns and specify Unique values?
-
quote:
Would it help if you made the ID column in the "one" table your primary key, then create an index on ID + Date columns and specify Unique values?If ID is the primary key, then a unique index including it adds nothing to any uniqueness.
I see no logical reason why SQL Server has the limitation that foreign keys must reference only unique values. I can understand why the primary key would be compounded from an non-unique ID and a date, but if one wants just the ID to be a foreign key then one must abandon DRI and instead write triggers or other code to enforce the relationship.
--Jonathan
--Jonathan -
Deek,
Are you trying to capture change history?
Isaiah
-Isaiah -
quote:
but if one wants just the ID to be a foreign key then one must abandon DRI and instead write triggers or other code to enforce the relationship.would you really want to let trigger handle RI?
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
quote:
quote:
but if one wants just the ID to be a foreign key then one must abandon DRI and instead write triggers or other code to enforce the relationship.would you really want to let trigger handle RI?
Frank
Well, no.
I avoid triggers if there is any other option. In this case, I would probably just handle the RI with stored procedures and other code (which is faster than DRI). If non-DBAs can access the tables directly , or if this was a canned product, then triggers might be the correct solution. Can you think of any logical reason why a referenced key would need to be unique? I haven't checked this in other RDBMSs or in the ANSI SQL spec...
--Jonathan
--Jonathan -
quote:
Can you think of any logical reason why a referenced key would need to be unique? I haven't checked this in other RDBMSs or in the ANSI SQL spec...Replying to my own messages now...
I checked Oracle and the ANSI SQL-92 spec, and they both require the referenced key to be unique. After thinking about this, I suppose the reason is to limit the logic required when altering or deleting the referenced key; if it were not required to be unique, the RI process would need to check not only the referencing tables but also the referenced table, which could get messy...
--Jonathan
--Jonathan -
quote:
Well, no. I avoid triggers if there is any other option. In this case, I would probably just handle the RI with stored procedures and other code (which is faster than DRI). If non-DBAs can access the tables directly , or if this was a canned product, then triggers might be the correct solution. I do have created two or three triggers until now. The only reason was that I wanted to know how they work.
quote:
Can you think of any logical reason why a referenced key would need to be unique? I haven't checked this in other RDBMSs or in the ANSI SQL spec...Well, yes, ANSI SQL 92, I think, although I only have here the 'Second Informal Review Draft'
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
I avoid triggers if there is any other option. In this case, I would probably just handle the RI with stored procedures and other code (which is faster than DRI). If non-DBAs can access the tables directly 
, or if this was a canned product, then triggers might be the correct solution. 
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply