If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Setting up relational integrity with only one of two key fields
I'm trying to set up referential integrity between two
tables. Here's what I have: Table 1: Primary key is made up of two fields: Field 1: Month * Field 2: Day * * Not actual field name Table 2 also has a Month field but it doesn't have the Day field. All it cares about is Month. In Table 1 any particular Month and Day can be listed only once, but as you can guess Month can be listed many times. And in Table 2 there can be multiple records with the same Month. I'd like to set up integrity between just the Month fields but keep getting this error "No unique index found for the referenced field of the primary table." Can anyone tell me what I need to do? Thanks! Linda |
#2
|
|||
|
|||
Can't do this in the table (relationship window) itself. You'll need to have
your database's programming maintain this for you. -- Ken Snell MS ACCESS MVP "Linda V" wrote in message ... I'm trying to set up referential integrity between two tables. Here's what I have: Table 1: Primary key is made up of two fields: Field 1: Month * Field 2: Day * * Not actual field name Table 2 also has a Month field but it doesn't have the Day field. All it cares about is Month. In Table 1 any particular Month and Day can be listed only once, but as you can guess Month can be listed many times. And in Table 2 there can be multiple records with the same Month. I'd like to set up integrity between just the Month fields but keep getting this error "No unique index found for the referenced field of the primary table." Can anyone tell me what I need to do? Thanks! Linda |
#3
|
|||
|
|||
"Linda V" wrote in
: I'd like to set up integrity between just the Month fields but keep getting this error "No unique index found for the referenced field of the primary table." Can anyone tell me what I need to do? Yes: you need a Unique Index (usually the Primary Key) for the foreign key to point to. Therefore, Table2.Month must be at least uniquely indexed, and there is usually no good reason not to use the existing PK. Whether the best PK is indeed Month or some other field or fields we cannot tell, since names like "Table2" do not really impart much about the meaning of the thing. Remember that db design is above all about semantics, trying to abstract it really doesn't help -- once you take away the meaning from the objects you are trying to model, you do not leave enough behind to learn anything. What are you actually trying to model here? Tim F |
#4
|
|||
|
|||
What am I actually trying to model? I'm modeling
consumers on a given piece of conductor (the electric wires you see on telephone poles, and those you don't see underground). In the real world, a length of conductor can span multiple poles so it can be hundreds of feet long. It can have many consumers tied to it. However, in my world, we're creating digital maps and whenever a conductor crosses a map edge we have to, in essence, 'break' the conductor into multiple pieces. If it crosses one map edge you get two pieces. If it crosses two map edges you get 3 pieces, etc. So in my database I could have conductor '100' that might consist of three different pieces. Hence the two field primary key: conductor number ('100') and piece (1, 2, or 3). (Please don't suggest that we change the way we're creating the digitial maps. That is out of the question right now.) Given what we have, I'd like to set up referential integrity between my consumers and my conductors. As I said in my initial email, the consumers know their conductor but they don't know, or care, about the piece. I envision referential integrity between the two working as such: a consumer can't be assigned to a conductor unless that conductor exists. It doesn't matter if I have all the pieces there. If there's one piece there then it exists. Likewise, a conductor cannot be totally deleted unless there are no consumers tied to it. By "totally" I mean all pieces. So if I have three pieces, two of the pieces can be deleted without problem, but if I try to delete the last piece and there are consumers tied to it I should get an error. My two tables are Conductor and Consumer. Conductor table - Primary key is Conductor number + Piece Consumer table - Primary key is Consumer number. This table also contains a Conductor number field. According to Access Help I have to create a junction table for this relationship. I did that. It contains three fields; Conductor number, Piece and Consumer number. I then created a primary key using all three fields and set up referential integrity using the junction table. Using the junction table I can set up referential integrity without getting the error I talked about in my intial email. So all looked well. However, to test it I went into my Consumer table and tried to add a consumer with a conductor number not specified in my Conductor table, and it let me. No error. So it still isn't enforcing referential integrity. I saw Ken's response that this has to be handled programatically. I was hoping that wasn't true, but it's looking like it is...unless you have some tricks up your sleeve we don't know about. Any tricks? Thanks, Tim. Linda -----Original Message----- "Linda V" wrote in : I'd like to set up integrity between just the Month fields but keep getting this error "No unique index found for the referenced field of the primary table." Can anyone tell me what I need to do? Yes: you need a Unique Index (usually the Primary Key) for the foreign key to point to. Therefore, Table2.Month must be at least uniquely indexed, and there is usually no good reason not to use the existing PK. Whether the best PK is indeed Month or some other field or fields we cannot tell, since names like "Table2" do not really impart much about the meaning of the thing. Remember that db design is above all about semantics, trying to abstract it really doesn't help -- once you take away the meaning from the objects you are trying to model, you do not leave enough behind to learn anything. What are you actually trying to model here? Tim F . |
#5
|
|||
|
|||
Hello Linda
"Linda V" wrote in : My two tables are Conductor and Consumer. I detect that you are actually describing three different entities, though. Pieces and Conductors are not the same thing, and each should be modelled by a table. Conductor table - Primary key is Conductor number + Piece This is what I mean: a single Conductor has lots of Pieces, so there should be two tables -- Conductors(*ConductorNumber, Wattage, StartsFrom, ... etc) Pieces(*ConductorNumber(FK), *PieceCode, StartMap, EndMap, ... etc) These two are clearly in a one-to-many relationship. You said above As I said in my initial email, the consumers know their conductor but they don't know, or care, about the piece. (actually the initial mail talked about Months and Years, but never mind!) If _you_ care about which Piece the Consumer is connected to, then the relationship should be between Consumers(ConductorID, PieceCode) and Pieces. Otherwise, enforcing the relationship between Consumers and Conductors is fine. Consumer table - Primary key is Consumer number. This table also contains a Conductor number field. Yes, that is fine. Likewise, a conductor cannot be totally deleted unless there are no consumers tied to it. By "totally" I mean all pieces. So if I have three pieces, two of the pieces can be deleted without problem, but if I try to delete the last piece and there are consumers tied to it I should get an error. Unfortunately, Access cannot do this for you, with the model you described above. A "real" RDBMS has triggers that can check this kind of thing, but not this one, I'm afraid. You will have to control this behind a form, and make sure that nobody had access to table datasheets, or making their own queries, etc. If you do relate Consumers to Pieces to Conductors, though, this rule will be enforced by normal FK integrity. Hope that helps Tim F |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
UNION Query truncating Memo fields | Matthew DeAngelis | Running & Setting Up Queries | 4 | June 10th, 2004 02:17 PM |
Sorting a query is bringing up a parameter request for some fields | Donna | Running & Setting Up Queries | 3 | May 31st, 2004 07:51 AM |
Document properties fields in a template for author/typist | Stephen Glynn | New Users | 6 | May 27th, 2004 03:51 PM |
Can't export calculated fields to Excel | Sharon Caspers | Setting Up & Running Reports | 0 | May 26th, 2004 08:31 PM |
Setting axis limits by reference to fields | Geoff | Charts and Charting | 2 | November 5th, 2003 05:57 PM |