Working With Keys

[geosite]

I have a table that beings with a numerical field (1-273) named "ID," which I designated my primary key, which I understand is standard practice. But I want to use abbreviations for my primary key instead, so I'm trying to switch the primary key to a different field, "CCode," but I can't figure out how to do it with either phpMyAdmin or MySQL Front.

I figured out how to drop the primary key, but I can't apply it to the other field - CCode. I made two screenshots to illustrate the steps I followed and the error messages I received with both phpMyAdmin and MySQL Front:

http://www.geoworld.org/primarykey.gif
http://www.geoworld.org/primarykey2.gif

Any tips?

Also, how do you create a foreign key? I haven't yet found the instructions on either phpMyAdmin or MySQL Front.

P.S. When phpMyAdmin uses the word "Index," is that another word for "Key"?

[Toly]

I did a quick test and didn't have any problems assigning a primary key to a different field.

The other thing is that you could use alias to temporary rename your column to something else.

SELECT
    ID as CCode
FROM
    nations

[Subjective Effec]

I don't think mySQL actually allows foreign key definitions. You have to enforce integrity yourself. Not hard really.

[geosite]

I did a quick test and didn't have any problems assigning a primary key to a different field.[/PHP]

A quick test? Did you recreate my table, or a portion of it? If so, thanks for the effort!

"The other thing is that you could use alias to temporary rename your column to something else."

SELECT
    ID as CCode
FROM
    nations

Is that something you can do with phpMyAdmin or MySQL Front, or do you have to do it from a command line?

At any rate, I tried something similar. I copied another table that already had a similar column designated the primary key, then tweaked it a bit. I still got an error message when I tried to import data, but I just clicked through the messages until all the data was imported.

Then, I suddenly realized what the real problem probably is.

My table features five columns filled with data for each of the world's nations, incuding the abbreviations (e.g. us for United States) that I'm using for my primary key.

However, I also inserted some hearders - like North America at the top and South America at the top of the South American nations. On those rows, the corresponding abbreviation cells are empty.

So when I designated my primary key NOT NULL - when some cells were in fact empty - that probably threw things out of wack, right?

Now that I've imported the data, will that be a continuing problem? Do I have to stick something in those emty cells - like - or an em dash - or is it OK to leave a few empty cells on a primary key?

[geosite]

I don't think mySQL actually allows foreign key definitions. You have to enforce integrity yourself. Not hard really.

So how do you do that?

[geosite]

So how do you do that?

Nevermind... I found MySQL's foreign key page...

[Toly]

Is that something you can do with phpMyAdmin or MySQL Front, or do you have to do it from a command line?

Yes, you do it from the command line or when you are calling a query within a script.

Now that I've imported the data, will that be a continuing problem? Do I have to stick something in those emty cells - like - or an em dash - or is it OK to leave a few empty cells on a primary key?

I think there might be some issues with empty rows on a not null primary key field, but I can't be sure since I'm no sql expert. I'm sure someone else will be able to answer that question.

[geosite]

Holy cow, I took another look at the MySQL Manual, and I'm more confused than ever!

From square one, please refer to the first two tables in the screenshot at
http://www.geoworld.org/worlddatabase.gif

The first table features rows for every continent and ocean, ending in a field named "CCode," for Continent Code. It's simply a list of abbreviations that I want to designate my primary key.

The second table features a row for every nation, ending with the CCode field, which will be designated the foreign key (as soon as I figure out how to make foreign keys!).

There are two things that confuse me. 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.

Second problem: I included rows with subheadings in this table for each of the continents and oceans.

If you look at the very top row in the Nations table, you'll see it features data in just one cell - the words "NORTH AMERICA." That row's foreign key cell is marked NULL.

So here's my question: Does every cell in a foreign key have to have a value? If so, can I just insert something like a dash (-) if I don't want to link to that row to a primary key on another table?

Or should I go ahead and link the North America row to North America on the Continents table?

If I can resolve this, then I can repeat the process, establishing NCode as the primary key on my states table, and using its values to make a foreign key on the Counties table.

Also, I don't understand how to create foreign keys. From the MySQL Manual:

http://dev.mysql.com/doc/mysql/en/In...nstraints.html

"Both tables must be InnoDB type. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order."

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"?

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."

Are they saying the foreign key has to be the first field? And why do they speak of foreign key columnS? I thought there could only be one foreign key per table.

"In the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order."

Again, this is confusing. Which is the referenced table? What kind of index are they talking about - a primary key? Foreign key? It has to be the first column/field? And what do they mean by "the same order"? Are they saying that if NA is listed first in the Continent table's primary key, it also has to be first in the Nation table's corresponding foreign key?

"InnoDB does not automatically create indexes on foreign keys or referenced keys: You must create them explicitly."

So how do you create indexes?

There's more. This is very confusing.

Are you aware of any tutorials that feature pictures illustrating this concept and clearly spell out what steps you have to take?

Thanks.

[r937]

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.

it's OK, and you are absolutely right, foreign keys can repeat

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)

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"?

that would be the Nations 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)
);

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."

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)

Are you aware of any tutorials that feature pictures illustrating this concept and clearly spell out what steps you have to take?

yes, Data Modeling by the university of texas at austin