Foreign Key newbie questions

1) Must referenced columns always be primary or at least unique or at least a key? Can it just be any old column?

I guess I'm confused because I always thought of a foreign key as any column that you can join on. But that doesn't sound like the case. That column must be UNIQUE?

2) Do both ON DELETE and ON UPDATE always have to be set to something or can you opt for nothing to happen in either case? If you can opt for nothing is there any other reason you would bother explicitly declaring a foreign key?

3) Is the primary reason we want to explicitly declare foreign keys because of the ON DELETE / ON UPDATE functionality? Otherwise, what's the point?

3) the point is data integrity -- you cannot add a row to the child table if the parent key does not exist

Sorry, I edited my question a little, but I gather that the referenced column must be, at minimum, an explicitly declared unique key.

So, even if NO ACTION is selected, the point is that if I tried to add a row referencing a parent key value that doesn't exist, mysql will prevent me from adding it? And that ENFORCES referential integrity which is the goal.

Maybe this is different from the SQL standard or relational algebra, but it's the same with other mainstream database systems like PostgreSQL or Oracle DB.

Originally Posted by dano2

So, even if NO ACTION is selected, the point is that if I tried to add a row referencing a parent key value that doesn't exist, mysql will prevent me from adding it? And that ENFORCES referential integrity which is the goal.

That's true. In reality, foreign keys are mostly used to enforce valid references to the primary key of a parent table.

Let's say you have an online shop. Then you wanna make sure that your orders only contain actual products and not gargabe data caused by some bug in the application.

Why canít I use certain words like "drop" as part of my Security Question answers?
There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".

Not sure what the "yes" by r937 referred to, but the referenced columns do not have to be unique. They only have to be indexed in a certain way (a way which allows fast lookups).

Interesting. "InnoDB allows a foreign key constraint to reference a non-unique key. This is an InnoDB extension to standard SQL". I'm reading that as, it can be done but best practice is to keep the parent key unique.

Originally Posted by Jacques1

...bug in the application.

I guess I can appreciate the extra layer of reinforcement foreign keys provide, but it almost seems too redundant. It seems they only serve to compensate for a poorly written app. All of the foreign key actions (including the prevention of adding children without parents) have to be addressed at the app level anyway, whether a foreign key exists or not, even if only in the form of handling those foreign key violation exceptions.

So, since it doesn't take any burden off the app developer and has a cost in the form of creation/management/frustration time, I could see where the argument that they are more trouble than they're worth comes from.

Do you know a quick way in phpmyadmin to see if a key is foreign. The table's "Structure" pages, just indicates that it's a key, no mention of 'foreign'?