Datatype "AutoNumber" in different tables in one database 1

[Beng79]

Good day to all,

I have a number tables in a database and primary keys are needed for the tables. If I assign Datatype "AutoNumber" for the primary keys for each table, will they be unique?

The primary keys are also link up as foreign keys for other tables.

I have posted this question before in the ASP forum as I am also using ASP scripts. Since the problem is regarding MS Access tables, thus I am posting it here again.

Thanks

 

[lynchg]

Yes, the autonumber fields will be unique. You can't enter values into these fields, the values are inserted automatically with each new record. If you delete records the autonumber values will not be reused.

Say that you insert 10,000 records into your table and the autonumber values are 1 - 10000. You then delete the records 9001 - 10000. On your next insert the autonumber value of the next record will be 10001.

 

[Beng79]

How will it be unique across multiple tables?

 

[lynchg]

When you use an autonumber field as a foreign key in another table, if you need it to be unique in that table you will have to enforce that yourself.

You can make it an index and force uniqueness, and restrict the insertion of records using code, it all depends on what your requirements are.

 

[lespaul]

say you have:

Customers
CustomerID (PK)
CustomerName


Orders
OrderID (PK)
CustomerID (FK)

OrderDetail
DetailID (PK)
OrderID (FK)
LineNumber
ProductID (FK)

Each time you enter a new customer, they get a unique ID number in the Customer table. If they then place an order you add a record in the order table and it doesn't matter if you have orderID = 2 AND CustomerID = 2 does it? Say there are 5 items ordered, you now have DetailID = 1, 2, 3, 4, 5 that refer to orderID = 2.

So, why does it matter to you if these numbers are unique across multiple tables? The whole idea of an autonumber field is to provide a unique identifier within the table for each record. There should be no other meaning attached to the number.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design