Primary keys vs disallowing null values

[Jade5]

Hello:
Can someone please explain the difference between setting multiple primary
keys and disallowing null values. For example, If I have Table1 with PK A and
Table2 with PK B and FK A. What's the diff between 1)making both PKB and FKA
the primary keys for Table 2 and 2) making PK B the primary key but
disallowing null values for FK A?
Thanks,
J

 

[Jerry Whittle]

1. That would be redundant as PKB is already unique. Adding FKA just adds to
the complexity UNLESS your business rules have changed and the combination of
those two fields now define the record. If you have a combination of fields
as the PK, you need to link all the fields to the multi-FK fields in say
Table3.

2. This is the better option. Also set up the relationships between the
tables with referiential integrity on. It stops orphan records from being
created in Table2 as there must be a FK entry that matches the PK in Table1.

Personally I use autonumbers as my primary keys and unique
indexes/constraints on other field to keep duplicates out of a table. If the
PK has no meaning, the meaning can't change. That can save a lot of grief.

 

[Tim Ferguson]

Hello:
Can someone please explain the difference between setting multiple
primary keys

Umm: you can have EXACTLY ONE primary key in a table; you can have a PK
made up of more than one field, or you can have more than one Unique
Index in a table (each of which may be made up of one or more fields).

and disallowing null values.

A Required field is just that: you have to have a value in every record,
although what constitutes a valid value is a different question. All
fields in a PK are Required by definition.

For example, If I have
Table1 with PK A and Table2 with PK B and FK A.

I'm assuming that Table2.FKA references Table1.PKA

What's the diff
between 1)making both PKB and FKA the primary keys for Table 2 and

If you mean a PK made up of (PKB,FKA) it means that you can have many
records that have the same PKB value, as long as they all have different
FKA values, and vice versa. This is a very common practice in so-called
"junction tables" where the same student can have several registrations
as long as they are all for different courses.

2) making PK B the primary key but disallowing null values for
FK A?

This means that every value of PKB must be unique, but you might have
records that do not reference a value in Table1. For example, a list of
EmploymentPosts will each have a unique PostCode, but some will not have
a CurrentEmployeeNumber if there is nobody employed at that post.

I don't really see how these questions are related to each other?

Hope it helps


Tim F

 

[Jade5]

Thank you.

1. That would be redundant as PKB is already unique. Adding FKA just adds to
the complexity UNLESS your business rules have changed and the combination of
those two fields now define the record. If you have a combination of fields
as the PK, you need to link all the fields to the multi-FK fields in say
Table3.

2. This is the better option. Also set up the relationships between the
tables with referiential integrity on. It stops orphan records from being
created in Table2 as there must be a FK entry that matches the PK in Table1.

Personally I use autonumbers as my primary keys and unique
indexes/constraints on other field to keep duplicates out of a table. If the
PK has no meaning, the meaning can't change. That can save a lot of grief.

 

[Jade5]

Thank you.

Umm: you can have EXACTLY ONE primary key in a table; you can have a PK
made up of more than one field, or you can have more than one Unique
Index in a table (each of which may be made up of one or more fields).


A Required field is just that: you have to have a value in every record,
although what constitutes a valid value is a different question. All
fields in a PK are Required by definition.


I'm assuming that Table2.FKA references Table1.PKA


If you mean a PK made up of (PKB,FKA) it means that you can have many
records that have the same PKB value, as long as they all have different
FKA values, and vice versa. This is a very common practice in so-called
"junction tables" where the same student can have several registrations
as long as they are all for different courses.


This means that every value of PKB must be unique, but you might have
records that do not reference a value in Table1. For example, a list of
EmploymentPosts will each have a unique PostCode, but some will not have
a CurrentEmployeeNumber if there is nobody employed at that post.

I don't really see how these questions are related to each other?

Hope it helps


Tim F