0

Let's say table B is dependent on table A.

Table B has a unique primary key B.B_ID and a foreign key B.A_ID

which references the parent table A's primary key.

Since B has a unique key, is B.A_ID, the foreign key, a non key (non-key)

in B?

Thank you.

Igor
  • 21
  • 1
  • 1
  • 2
    What do you mean by `non-key`? – Siyual Mar 06 '17 at 16:10
  • You can have tables reference other tables via a linking column and not have it be a `foreign_key` – Tim Lewis Mar 06 '17 at 16:11
  • I am trying to understand what non key (may be written non-key) is. Perhaps it means different things to different people. For example, here is one statement from my class in reference to second normal form: All non-key fields should be a function of the primary key. Elsewhere I seem to recall that a non primary key is a non key. – Igor Mar 06 '17 at 16:15
  • "Candidate key" is a term that means a column (or combination) is unique in this table but it is not defined/declared as the primary key. And a "foreign key" is a key in some other table. Defining/declaring a column(s) as a FK means that value must exist in the other table (see @lightningbear answer) and the database enforces that constraint. Otherwise a column that just happens to have values the same as in some other table is nothing special. – radarbob Mar 06 '17 at 16:28
  • @radarbob even the primary key is candidate key as far as I know. – Lajos Arpad Mar 06 '17 at 16:59
  • @LajosArpad, yes, the primary key is the smallest candidate key of the table. – Bill Karwin Mar 06 '17 at 17:07
  • @BillKarwin ideally, yes. In reality it can be a superkey containing a candidate key or a longer candidate key. – Lajos Arpad Mar 06 '17 at 17:25

3 Answers3

2

In the context of discussing the 2nd normal form, "non-key columns" are all the columns that are not part of a candidate key.

You may certainly have foreign key columns in a table that are not part of that table's primary key.

Suppose you have a table Person that has a primary key PersonName because that is the column you use to identify each person uniquely.

You can also have a column in that table such as CountryOfCitizenship that is a foreign key referencing another table Country. This foreign key column is not part of the primary key in the Person table; it is not the way we identify each row in that table.

Re your comment:

The second normal form requires that non-key columns have a functional dependency on the whole primary key. This is different from first normal form only if your primary key has multiple columns.

Functional dependency means that the attribute column unambiguously relates to the primary key, and therefore the value in the attribute column belongs on the same row with that primary key.

So if a column like CountryOfCitizenship always contains the country name that is the country of citizenship for the person who is named in that same row's primary key, then the attribute satisfies 1NF and since the table has a single-column primary key, it's automatically in 2NF as well.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Thank you @Bill. Then is Person.CountryOfCitizenship a non-key and does it not satisfy "All non-key fields should be a function of the primary key" so that Person fails the 2nd N.F. requirement? – Igor Mar 06 '17 at 16:49
  • Thank you again Bill. Clearly my understanding of normal forms is lacking. Please recommend a source to further my understanding. – Igor Mar 06 '17 at 17:26
1

In MySQL terminology, "key" usually refers to an explicit key which has an index on it. If you use this definition, then a primary key is a key. And a unique key is a key. And an index key is a key. But a foreign key is not necessarily a key.

When you declare a foreign key constraint, MySQL does not necessarily build an index on the referring table (it does in innodb). Of course, you can declare the foreign key to also be a key and guarantee that an index is built.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Since MySQL 4.1.2 (May 2004), InnoDB does automatically create an index when you declare a foreign key, unless there's already an index on the FK column(s). Before that version, the foreign key definition would fail unless you created the index explicitly. Either way, InnoDB requires an index for a foreign key. – Bill Karwin Mar 06 '17 at 16:29
  • Thank you. I have the following statement re the second normal form: All non-key fields should be a function of the primary key. My ERD software, Visual Paradigm, places foreign key in a dependent table. If this foreign key is not function of the primary key in the dependent table, does it mean that the second normal form is not satisfied? – Igor Mar 06 '17 at 16:30
0

A FOREIGN KEY constraint does not have to be linked only to a PRIMARY KEY constraint in another table; it can also be defined to reference the columns of a UNIQUE constraint in another table.

You can find more information here https://stackoverflow.com/a/18435114/7667467

Community
  • 1
  • 1
t16n
  • 223
  • 2
  • 11
  • I understand this. To keep it simple, let's say in this instance the foreign key in B refers to a primary key in another table A. If the foreign key is not part of a primary key in B, is it considered to be a non-key? – Igor Mar 06 '17 at 16:23
  • I am not sure what you mean by `non-key` – t16n Mar 06 '17 at 16:29
  • Sorry @Lightningbear. That's the crux of the matter, I am trying to figure out what a non-key attribute is, whether it can a foreign key referencing a primary key in its parent table – Igor Mar 06 '17 at 16:36