Referential integrity. AutoNumber…

[Peter]

Hi all..I want to create a one-to-many relation between:

Table: A. Field : A (AutoNumber) (Primary Key)

One to many relation

Table: B. Field: A (Number)

Am I thinking wrong here? If I cant Enforce Referential Integrity in this
case should I still create a relation, but without RI?

Thanks!

 

[Arvin Meyer [MVP]]

Yes in:

Table: B. Field: A (Number)

the number is a long integer and you should have Referential Integrity. If
you can't force RI, you probably have some bad data in Table B.

 

[Stuart McCall]

Hi all..I want to create a one-to-many relation between:

Table: A. Field : A (AutoNumber) (Primary Key)

One to many relation

Table: B. Field: A (Number)

Am I thinking wrong here? If I cant Enforce Referential Integrity in this
case should I still create a relation, but without RI?

Thanks!

Yes, that looks correct, so long as Table B has its own primary key field as
well as Field A (which is a Foreign key). Also Field A should be of type
Long to match the Autonumber in Table A.

Given the above, I can't see a reason why you won't be able to establish the
relation you want, and be able to enforce RI.

 

[Roger Carlson]

Others have answered, but I just want to mention that if you don't enforce
Referential Integrity, you don't HAVE a relationship. That's what a
relationship means. Without RI, you just have a line between two tables,
which is basically useless.

 

[David W. Fenton]

As Arvin pointed out, and which I should have considered before my
initial response, be sure the field in Table B is not only number,
but that it is Long Integer. Long Integer is one of the choices
for Field Size, at least in pre-2007 versions of Access.

And the reason you choose Long Integer is because that's the data
type of an Autonumber field, which is nothing more than a Long
Integer field with a special kind of default value.

 

[John W. Vinson]

Hi all..I want to create a one-to-many relation between:

Table: A. Field : A (AutoNumber) (Primary Key)

One to many relation

Table: B. Field: A (Number)

Am I thinking wrong here? If I cant Enforce Referential Integrity in this
case should I still create a relation, but without RI?

Are you having a problem doing so? What you're suggesting is perfectly
routine. Just be sure that TableB Field A is a Long Integer.