Primary Key , Foreign Key , and a few questions!

[Jumpy09]

Alright so I had originally thought that the database was going to be the easiest portion of my job until I watched "10 Ways to Destroy your Database" which was a real nice video.  I found out about Foreign Keys and the importance of not just using an Auto-Incrementing Primary Key to ensure Data Integrity.

 

Now this raised a concern which I thought I had solved by adding another column into the Primary Key, but I realized I didn't really know what multiple column primary keys did.  I found out!

 

So my Primary Key Question is!

Q: I want to have the Auto-Incrementing ID to be completely unique, it is how I manage relational data in other Tables.  I also want to ensure email addresses are unique to themselves.  I know adding 2 Primary Keys to an InnoDB isn't possible, but is there another method of making this happen?

 

On to Foreign Keys, before the video my entire Relational ordeal was done through PHP.  Which meant building elaborate if / else nests that would cascade on removal of one item to ensure removal of any orphans that would be left over.  Thank goodness I hadn't coded that portion of my project yet, and I had found the benefits of Foreign Keys.

 

I do have a few Questions related to Foreign Keys that I couldn't find the answer to going through Google ( Maybe my question was worded wrong and Google didn't understand )!

 

Question 1: I've read that you place the Foreign Key into the Child Table.  If you had a Species Table and a Sub Species Table, the key would go into Sub Species.  When you have CASCADE set on DELETE, when you delete the Parent Row would it automatically delete the Child Row?  I would think you would set the Foreign Key in the Parent Row to show what Tables are associated with it!  So that bit confused me!

 

Question 2: When you create a Foreign Key I have read it automatically creates an Index.  Is this an Index of the Columns in question or an Index of the relationship only?  If I wanted to find out how many Sub Species were in 1 Species, could I just Query the Sub Species Table looking for all the Species IDs related to one Species or would I have to set up an Index for that individual Column in addition to the Foreign Key?

 

Question 3:  When using the Foreign Key CASCADE ON DELETE, will the Connection remain open while the cascading deletes are happening or will that happen in the background and allow the connection to close so that another one can jump in?  Me and my friend had a hypothetical regarding a realistic scenario.

 

You have 1 Table for Posts , 1 Table for Comments , 1 Table for Likes / Dislikes on the Comments!  Likes would be bound to Comments which would be bound to Posts with Foreign Keys.  ( I think I just realized why the Foreign Key is added to the Child Table. )  So if 1 post had 2,000,000 comments with 7,000,000 Likes between all the comments!  Yeah that guy is really fricken popular, but he is a tool!  So you remove the 1 post, which removes the 2,000,000 comments, which remove the 7,000,000 likes!  Would the connection for that deletion remain open while those 9,000,001 rows are being deleted?  This Hypothetical is completely over exaggerated, but I'm sure there is someone in this world that could strike up enough interest to make it possible.

 

Thank you for any replies, they will be very helpful.  I have a base idea of what Foreign Keys do, but I'm not entirely sure so I would like confirmation.

[Jumpy09]

As far as the Primary Key question goes I still do not know the answer to that, although I have figure the answer to a couple of the Foreign Key questions.

 

1. I think I was right!  The Parent Table would look to see if it had any children, if so it would remove them first!

2. I still have no idea if the index set up via the Foreign Key actually helps when Querying the tables in question.

3. Thanks for #1, I would assume YES.  It must remove it's grandchildren before it's children and it's children before it!  So the connection, I am assuming, would stay open.  So this would be a nice area to mess around with a bit of Javascript and give a nice visual representation of what is happening.

 

So: How can I set it up so that the Auto-Incrementing Key is Unique to itself and the E-Mail Addresses are unique to itself?

Example: In ID I do not want any number to repeat itself, where as if I was to add a dual or triple column Primary Key.. this would actually occur.

 

So #2: Will the Indexes set up by a Foreign Key aid in Querying without having to set up additional Indexes to the tables?  If you Foreign Key SpeciesID in SubSpecies to the ID in Species.. it would I am assuming set up the index between the two, and allow for quicker queries when dealing with both the tables?

 

Thanks.

[fenway]

You don't need your PK to be "completely unique" -- just unique for each table.

 

If you want e-mail addresses to be unique (but you don't), you can add a UNIQUE index -- that has nothing to do with the PK.

 

Yes, defining a FK automatically creates an index, though personally, I prefer to do that explicitly, so that nothing "silent" happens.

 

I'm sure I missed a few questions.

[Jumpy09]

I really appreciate the answer, what I meant by completely unique was unique for the table.  At that time I wasn't sure about the UNIQUE Index which is exactly what I needed.