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
|
|||
|
|||
1-1 relationship
Hi
I know that 1-1 relationships are uncommon but when you have them how do you know which is the primary table. Also if you have a 1-many relationship I presume that the primary table is the '1'. What about a many-many - could either be the primary table and how do you know which it is? Thanks |
#2
|
|||
|
|||
1-1 relationship
Hi Phippsy
If you double-click on a relationship line in the relationships window, the Edit Relationships window will pop up. On the left is the "Table/Query" and on the right is the "Related Table/Query". The "primary table" is the one on the left. There is no such thing as a many-to-many relationship. These must be implemented artificially using a "junction table", which contains foreign keys for both the tables you want to relate. You then set up a two one-to-many relationships, between each of the primary tables and the junction table. -- Good Luck! Graham Mandeno [Access MVP] Auckland, New Zealand "Phippsy" wrote in message ... Hi I know that 1-1 relationships are uncommon but when you have them how do you know which is the primary table. Also if you have a 1-many relationship I presume that the primary table is the '1'. What about a many-many - could either be the primary table and how do you know which it is? Thanks |
#3
|
|||
|
|||
1-1 relationship
What do YOU mean by "primary table"? It may not match my definition...
Regards Jeff Boyce Microsoft Office/Access MVP "Phippsy" wrote in message ... Hi I know that 1-1 relationships are uncommon but when you have them how do you know which is the primary table. Also if you have a 1-many relationship I presume that the primary table is the '1'. What about a many-many - could either be the primary table and how do you know which it is? Thanks |
#4
|
|||
|
|||
1-1 relationship
1-1 is less common - yes...but not uncommon.
general employee record...and then proprietary info i.e. salary , social security info, etc.... is a typical 1-1 lots of situations where a summary is all that is needed 95% of the time...and then additional info occasionally...this is very suitable to a 1-1 .....particularly where the total set of info is too big for a single record. Obviously they must share the same field ID...so neither/either is inherently primary per se...in ones mind the table that is used most frequently I tend to think of as the primary.... -- NTC "Phippsy" wrote: Hi I know that 1-1 relationships are uncommon but when you have them how do you know which is the primary table. Also if you have a 1-many relationship I presume that the primary table is the '1'. What about a many-many - could either be the primary table and how do you know which it is? Thanks |
#5
|
|||
|
|||
1-1 relationship
On Tue, 17 Oct 2006 17:25:02 -0700, NetworkTrade
wrote: Obviously they must share the same field ID...so neither/either is inherently primary per se...in ones mind the table that is used most frequently I tend to think of as the primary.... Well, as implemented in Access (and SQL/Server, Oracle, MySQL, and others), you're mistaken; even one to one relationships have directionality. If you are going to enforce referential integrity, there must be a record in the "primary" or "left hand" table, and there may or may not be a record in the child table. Strictly speaking, it's a "One to (Zero or one)" relationship. John W. Vinson[MVP] |
#6
|
|||
|
|||
1-1 relationship
Thank you all so much.
I suppose I am thinking of the primary table where referentioal integrity has been set. Am I correct in thinking that the cascade only works one way - ie from the 'Primary' (left hand) table. If you delete a record from the 'Primary' table it cascades through to the Child (Secondary) table but it doesn't work if you delete a record from the Secondary table. Debbie "John Vinson" wrote: On Tue, 17 Oct 2006 17:25:02 -0700, NetworkTrade wrote: Obviously they must share the same field ID...so neither/either is inherently primary per se...in ones mind the table that is used most frequently I tend to think of as the primary.... Well, as implemented in Access (and SQL/Server, Oracle, MySQL, and others), you're mistaken; even one to one relationships have directionality. If you are going to enforce referential integrity, there must be a record in the "primary" or "left hand" table, and there may or may not be a record in the child table. Strictly speaking, it's a "One to (Zero or one)" relationship. John W. Vinson[MVP] |
#7
|
|||
|
|||
1-1 relationship
You described "Cascading Delete". This is an OPTIONAL setting on the
referential integrity. Yes, it is directional. Regards Jeff Boyce Microsoft Office/Access MVP "Phippsy" wrote in message ... Thank you all so much. I suppose I am thinking of the primary table where referentioal integrity has been set. Am I correct in thinking that the cascade only works one way - ie from the 'Primary' (left hand) table. If you delete a record from the 'Primary' table it cascades through to the Child (Secondary) table but it doesn't work if you delete a record from the Secondary table. Debbie "John Vinson" wrote: On Tue, 17 Oct 2006 17:25:02 -0700, NetworkTrade wrote: Obviously they must share the same field ID...so neither/either is inherently primary per se...in ones mind the table that is used most frequently I tend to think of as the primary.... Well, as implemented in Access (and SQL/Server, Oracle, MySQL, and others), you're mistaken; even one to one relationships have directionality. If you are going to enforce referential integrity, there must be a record in the "primary" or "left hand" table, and there may or may not be a record in the child table. Strictly speaking, it's a "One to (Zero or one)" relationship. John W. Vinson[MVP] |
#8
|
|||
|
|||
1-1 relationship
Yes thanks I realise that - I was just giving a for example!
"Jeff Boyce" wrote: You described "Cascading Delete". This is an OPTIONAL setting on the referential integrity. Yes, it is directional. Regards Jeff Boyce Microsoft Office/Access MVP "Phippsy" wrote in message ... Thank you all so much. I suppose I am thinking of the primary table where referentioal integrity has been set. Am I correct in thinking that the cascade only works one way - ie from the 'Primary' (left hand) table. If you delete a record from the 'Primary' table it cascades through to the Child (Secondary) table but it doesn't work if you delete a record from the Secondary table. Debbie "John Vinson" wrote: On Tue, 17 Oct 2006 17:25:02 -0700, NetworkTrade wrote: Obviously they must share the same field ID...so neither/either is inherently primary per se...in ones mind the table that is used most frequently I tend to think of as the primary.... Well, as implemented in Access (and SQL/Server, Oracle, MySQL, and others), you're mistaken; even one to one relationships have directionality. If you are going to enforce referential integrity, there must be a record in the "primary" or "left hand" table, and there may or may not be a record in the child table. Strictly speaking, it's a "One to (Zero or one)" relationship. John W. Vinson[MVP] |
Thread Tools | |
Display Modes | |
|
|