Foreign Key
in [Tables]
|
Prev: Access Employee Training Database
Next: Normalization
From: Amsterdammy on 1 Sep 2005 06:45 Good day, I have Access 2000 How do I make a field have a Fireign Key attribute. I have looked in adding new buttons to my toolbar, but cannot find a Foreign Key button (help suggests this) Any advice would be appreciated Thanks
From: BruceM on 1 Sep 2005 07:31 A foreign key is created by creating a relationship with a primary key field in another table. You specify a field as primary key in table design view, but you do not do the same with the foreign key field. Make the foreign key field the same data type (in table design view) as the primary key field, except that if the primary key is Autonumber the foreign key needs to be Number. You cannot create a relationship between two primary key fields. "Amsterdammy" <Amsterdammy(a)discussions.microsoft.com> wrote in message news:A44E72E9-F58F-4C94-8C99-D0FF1B555E11(a)microsoft.com... > Good day, I have Access 2000 How do I make a field have a Fireign Key > attribute. I have looked in adding new buttons to my toolbar, but cannot > find a Foreign Key button (help suggests this) > > Any advice would be appreciated > Thanks
From: Jeff Boyce on 1 Sep 2005 08:58 I might be remembering wrong, but I believe I have created a relationship between two tables' primary key fields when there's a one-to-one relationship. I do know that I can't create a (meaningful) relationship between two Autonumber fields. Regards Jeff Boyce <Access MVP> "BruceM" <bamoob(a)yawwhodawtcalm.not> wrote in message news:eZLrAjurFHA.4040(a)TK2MSFTNGP14.phx.gbl... > A foreign key is created by creating a relationship with a primary key field > in another table. You specify a field as primary key in table design view, > but you do not do the same with the foreign key field. Make the foreign key > field the same data type (in table design view) as the primary key field, > except that if the primary key is Autonumber the foreign key needs to be > Number. You cannot create a relationship between two primary key fields. > > "Amsterdammy" <Amsterdammy(a)discussions.microsoft.com> wrote in message > news:A44E72E9-F58F-4C94-8C99-D0FF1B555E11(a)microsoft.com... > > Good day, I have Access 2000 How do I make a field have a Fireign Key > > attribute. I have looked in adding new buttons to my toolbar, but cannot > > find a Foreign Key button (help suggests this) > > > > Any advice would be appreciated > > Thanks > >
From: BruceM on 1 Sep 2005 10:18 I wondered about that. I should have just stopped before I got to that last sentence. I believe I was thinking of both fields being autonumber when I posted my quickly-written reply. Now that I think about it, a relationship between a PK field and another in a one-to-one will result in the other field also being unique, so probably no reason why it couldn't be a PK also. Hmm. Thanks for making the point. "Jeff Boyce" <JeffBoyce_IF(a)msn.com-DISCARD_HYPHEN_TO_END> wrote in message news:OG8WDRvrFHA.3216(a)TK2MSFTNGP12.phx.gbl... >I might be remembering wrong, but I believe I have created a relationship > between two tables' primary key fields when there's a one-to-one > relationship. > > I do know that I can't create a (meaningful) relationship between two > Autonumber fields. > > Regards > > Jeff Boyce > <Access MVP> > > "BruceM" <bamoob(a)yawwhodawtcalm.not> wrote in message > news:eZLrAjurFHA.4040(a)TK2MSFTNGP14.phx.gbl... >> A foreign key is created by creating a relationship with a primary key > field >> in another table. You specify a field as primary key in table design > view, >> but you do not do the same with the foreign key field. Make the foreign > key >> field the same data type (in table design view) as the primary key field, >> except that if the primary key is Autonumber the foreign key needs to be >> Number. You cannot create a relationship between two primary key fields. >> >> "Amsterdammy" <Amsterdammy(a)discussions.microsoft.com> wrote in message >> news:A44E72E9-F58F-4C94-8C99-D0FF1B555E11(a)microsoft.com... >> > Good day, I have Access 2000 How do I make a field have a Fireign Key >> > attribute. I have looked in adding new buttons to my toolbar, but > cannot >> > find a Foreign Key button (help suggests this) >> > >> > Any advice would be appreciated >> > Thanks >> >> >
From: peregenem on 2 Sep 2005 10:12
BruceM wrote: > A foreign key is created by creating a relationship with a primary key field > in another table. You specify a field as primary key in table design view, > but you do not do the same with the foreign key field. Neither is required to be PRIMARY KEY. For a FOREIGN KEY, the column(s) in the referenced table must comprise a UNIQUE constraint. PRIMARY KEY has special meaning for Jet (physical ordering on disk) and is not related to declarative referential integrity (DRI) actions. Do not be fooled by that bogus message in Access about a PRIMARY KEY being required to create a 'Relationship'. > Make the foreign key > field the same data type (in table design view) as the primary key field, > except that if the primary key is Autonumber the foreign key needs to be > Number. Minor point: autonumber is not a data type; more like an attribute (again, don't be mislead by the caption in Access). And you probably meant INTEGER. Remember an autonumber can be a GUID too ;-) |