Easy, I'm sure...

[oldman67]

I am brand new to the site and this question is something I knew at one time, I think, but have subsequently lost somewhere along the way:

I am creating a new DB from scratch and like the idea of using the Autonumber Data Type as the Primary Key in my tables, but don't know what to use as the Data Type for the Foreign Key. I don't know if I'll be able to use Autonumber, or if I'll have to change it. Any help and advice is greatly appreciated.

Thanks!

 

[plog]

Autonumber is really a Long Integer, just with a special property that automatically assigns a unique value when a new record is created. So, to use it as a foreign key in another table, make that field a Long Integer.

Now, there are a few other things that you need to know about an Autonumber: first and foremost is that it is guaranteed to be unique--no more no less. If the last one assigned was 17, do not expect or build logic into your system that expects 18 to be the next one. It probably will be, but that's not how to use them. Also, learn to live with gaps. If you delete the record with 14 and your autonumbers go from 13 then to 15, that's fine. You aren't to expect any specific number to exist. Their only purpose is to give you something unique to identify that record.

People always get themselves into trouble assuming things about autonumbers that are not in fact true. They are unique: nothing more, nothing less.

 

[oldman67]

Got it, thanks!...

 

[oldman67]

One more question, if I may:

I am assuming that if I link from a third table (which is the many-to-many relationship go-between) to the table in which the field is the Primary Key, everthing will work as advertised. Am I correct in this line of thinking, or am I still missing something? Again, any direction and/or advice is greatly appreciated.

Thanks again!

oldman

 

[jdraw]

You may wish toe research Junction Table.

Here's a youtube video regarding resolution of Many to Many

 

[plog]

You got it. The only issue you might have is dirty data.

In the real world, your table that assigns the primary key might get that record deleted. You would then have an orphan in the third table. That would cause the record not to be returned in the query even though the link between the 2nd and 3rd tables is still good.

 

[oldman67]

will do....thanks for the tip!

 

[Mihail]

Can you show us the involved tables structures ?
Only the fields that contain PKs and FKs .

 

[gemma-the-husky]

just to make sure you understand

generally the PK of a given table, is used in another table as a link to records in that table. so the value of the PK is stored in the second table, as an ordinary data field, and therefore becomes a foreign key, if referential integrity is stipulated between the two tables

an autonumber is simply a long number. but as an autonumber it has special properties. In the second table, it needs to be the same type, therefore a long number.

equally, both PK and FK could be strings - but then you wouldn't be able to autoincrement a PK string.