it's OK, and you are absolutely right, foreign keys can repeat
First, there are several nations in most of the continents. Thus, the foreign key has a value of "na" (North America) for Canada, U.S. and Mexico. Several dozen nations will have a foreign key value of "eur" (Eurasia) and "afr" (Africa).
Is this OK? Something I read led me to suspect that no element can be repeated in a foreign key, but that doesn't make sense. I can understand why every element in a PRIMARY key should be unique, but not a foreign key.
otherwise nobody would be able to implement a 1-to-many relationship
[quote]So here's my question: Does every cell in a foreign key have to have a value? [quote]absolutely not
see Optional foreign key can be NULL (site registration may be required, but it's free)
that would be the Nations table
First, suppose we just have two tables. The Continents table has just one key - a primary key. The Nations table has just one key - a foreign key that matches the Continent table's primary key.
Which table is the "referencing table"?
the foreign key is always on the "many" side of the 1-to-many relationship
the foreign key "references" the primary key
seeing the syntax should make it clear --
create table Nations
, constraint validcontinent foreign key (CCode) references Continents (CCode)
it means that if the primary key is A1,B1,C1, then the foreign key will be A2,B2,C2, and you need to declare an index on A2,B2,C2 in that order, with nothing else at the beginning (you can add other columns after the FK columns)
And what does this mean?: "There must be an index where the foreign key columns are listed as the first columns in the same order."
yes, Data Modeling by the university of texas at austin
Are you aware of any tutorials that feature pictures illustrating this concept and clearly spell out what steps you have to take?