Basic Primary Key question..

[Bob]

In a database, can you have two entities that have the
same primary key and then link them together.

e.g

X = (r1,r2,r3 etc...)

Y = (r1,s2,s3,s4 etc..)

Where r1 = primary key

Can X be still linked to Y, what happens when referential
integrity is applied ?

Is there something seriously wrong with my database ?

 

[Allen Browne]

Although the AutoNumber is often used as the primary key, these are not the
same thing. A primary key is a field (or combination of fields) that
uniquely identifies a record. An Autonumber is a field that contains an
automatically assigned value. Natually the AutoNumber is a good candidate
for a primary key, but there are other valid choices for primary key as
well.

Your table X has field r1 as primary key. Perhaps r1 is an Autonumber.

Table Y can also have a field named f1. If you want this to be a foreign key
to X.r1, do not use an AutoNumber in table Y. Instead make Y.r1 a Number
field (size Long Integer), so you can create records and specify the
matching number.

Note that this will result in a one-to-one relationship between the 2
tables, since table Y cannot have more than 1 record that matches table X if
Y.r1 is primary key.

 

[Bob]

Thanks for the response.

R1 is not an auto number, but an attribute of the relation.

Does this change things then ?

 

[Allen Browne]

Bob, I'm not sure I'm clear on your terminology.

Are X and Y to be understood as an Access table?

If so, the relation must be between one or more fields
(columns/attributes/whatever) in the primary table, and matching foreign
keys in the related table. So if R1 is an attribute of the relation, it must
be represented by a field of the primary or related table?

 

[John Vinson]

In a database, can you have two entities that have the
same primary key and then link them together.

Yes. It wouldn't be a relational database if you couldn't.

e.g

X = (r1,r2,r3 etc...)

Y = (r1,s2,s3,s4 etc..)

Where r1 = primary key

Of two different tables X and Y?

Can X be still linked to Y, what happens when referential
integrity is applied ?

You will then be prohibited from adding a record to Y with a value of
r1 which does not occur in X. If there are such records already you
won't be able to apply RI until the discrepancies are fixed.

Is there something seriously wrong with my database ?

There's certainly no way for us to know whether there is or not on the
basis of this post.

 

[Tim Ferguson]

I do hope this isn't a troll...

X = (r1,r2,r3 etc...)

Y = (r1,s2,s3,s4 etc..)

Where r1 = primary key

Can X be still linked to Y, what happens when referential
integrity is applied ?

It depends what you mean by "linked". You can have X.r1 as a foreign key
referencing Y.r1, which means that every record in X has to have exactly
one sister in Y (that's why it's called a one-to-one relationship),
although you can have a record in Y that is not related to a record in X.

Alternatively, you could constrain Y.r1 to be a foreign key referencing
X.r1, so that all the rules above are the other way round.

You could constrain both fields, but it would be a really stupid thing to
do: the reason for that is left as an exercise for the reader...

Now, what was the real question?

Best wishes


Tim F