Cannot set FK if one of the tables involved has data?

masterlayouts · May 4th, 2012, 02:21 PM

Why the odd behavior when a table has data, we cannot set foreign keys.
I tried
SET foreign_key_checks = 0;
to alter the table to make the foreign key.
It didn't worked. Does it mean I will have to delete all the date from the table?
Basically, it is not possible to set FK constraints at a later time unless drop the date?

On the yesterday's subject, is there any chance you can spread some light? I would really go with the fake "primary key" id, but I have the feeling that you will convince me once more that it is wrong. It is stuck in my mind and I put aside the work on that table until I'll have your expert answer. I would really appreciate your help.

Thank you once more.

Rod Stephens · May 5th, 2012, 10:37 AM

You should be able to add a constraint after creating the table as in:

Code:

ALTER TABLE Orders
ADD FOREIGN KEY (CustomerId)
REFERENCES Customers(CustomerId)

I would think the database should allow this even if the table contains data, although it's not clear what it would do if existing data violated the constraint.

But this may vary for different kinds of databases so I don't know if it will work for MySQL. At this URL:

ALTER TABLE Syntax

it says:

Quote:

The FOREIGN KEY and REFERENCES clauses are supported by the InnoDB storage engine, which implements ADD [CONSTRAINT [symbol]] FOREIGN KEY (...) REFERENCES ... (...). See Section 14.6.4.4, âFOREIGN KEY Constraintsâ. For other storage engines, the clauses are parsed but ignored.

So perhaps this won't work for you. You may be able to copy the data into a temporary table, recreate the original table, and then copy the data back into it.

Quote:

On the yesterday's subject, is there any chance you can spread some light?

I'm just really busy lately (and it's going to get worse) so I can't get back to everything right away.

And again you can make the artificial keys if you like but "it's easier" is not always a good reason. Sometimes it seems easier but adds extra work somewhere else (like you need to create additional uniqueness constraints anyway).

But if you really think it would make life simpler, go for it!

masterlayouts · May 5th, 2012, 11:59 AM

Sir, thank you very much for your time. I'll be patiently wait for your reply when you find spare time.

I had in mind something different. It is not my intention to create a primary key for the table, but only another row that it is defined to autoincrement (and unique without adding any index). This is something like the SKU for products.

UserID PK
Degree PK
Institution PK
Start
End

It is less likely a person to have the same degree from the same institution, but chances are somebody may have the same qualification from two different institutions (like when needed to practice the same job in two different states).

I guess this will be enough (and I agree that Europass does make very little sense besides printing all information related to an UserID).

Now what I had in mind is t add one extra field, let's call it RowIdentification (like SKU) to be passed as parameter for update and delete operations. This will not be part of the key and will not be indexed. The queries on page will still use the relevant and indexed information, but what I will pass between pages will be just this numeric value. It is easier and has the advantage of hiding what happens behind the hood.

UserID PK
Degree PK
Institution PK
Start
End
RowIdentification (autoincrement)

As I do not see how the database may be negatively affected as it does not affect any index, nor constraints; also I do not see any performance lost, as fetching one extra row it's nothing and that query should have been made anyway, my question is what are the arguments against this use of the field.

Thank you again for your time and I promise I'll think for myself for a while.

Rod Stephens · May 5th, 2012, 12:26 PM

Well if you're only using that field to break ties, then there's some other piece of information that is missing from the table that should be able to break the ties. At least usually. It's pretty rare that two records could actually really be perfectly identical in every meaningful way.

Quote:

It is less likely a person to have the same degree from the same institution, but chances are somebody may have the same qualification from two different institutions (like when needed to practice the same job in two different states).

The same qualification for two people isn't a problem because of the UserID.

Two records for the same person at the same institution would be unusual. If that's possible, you could use the dates to tell them apart.

Two records for the same person at the same institution on the same dates probably doesn't make any sense.

But all of this depends on what you're interested in. For example, if you don't care about the dates, you might like to omit them from the table. (In this program you probably would want the dates but suppose you don't.) In cases like that people often add a SequenceNumber field that is just a numeric field to make the records unique. These are numbered 1, 2, 3, etc. for each person.

They are also often used to impose an order on data that doesn't have a natural order. For example, people often add it to the items in a purchase order so the items appear in the same order whenever you look at the record.

But back to your example. The auto-number field would act sort of like a sequence number to make the records unique.

One disadvantage I see is that, if you'll be using the RowId to find records then you'll probably want to index it so finding records with it will be faster

.

That's not the end of the world, however, and indexing numbers is pretty fast and takes little memory so the penalty won't be too high.

(Note also that some databases have a record number or record ID value that is automatically available. Usually it's used internally by the database but you can use it if you really want to, although most database purists would frown on that because it's an artificial key!

A quick google makes me think that MySQL doesn't have a row ID.)

masterlayouts · May 5th, 2012, 01:24 PM

Sir, at least I am glad that I do not waste your time with bad questions. I had no idea about the ROWID feature. I knew about the php function mysql_insert_id(), but this returns just the last row.

I begin to have some remorse about using MySQL and when I think back to the time when their InnoDB was not working with FULL INDEX on MySQL and the choice were MyISAM (default at that time)... How this db become the most popular with all these elementary flaws? What DB would you recommend for a large project?

So CHECK does not work as intended, CONSTRAINTS cannot be set on/off so we have to take the db out to add a constraint, now this thing with ROWID that in MySQL it means I have to create it myself, quite in the way I was thinking... What other hell is ahead?

Indexing this ROWID field will bring a little penalty (to use your words), but if I think about it, it will not be without some benefits. The alternative would have been to pass 2 parameters from free input fields where chances are for the same institution I will have many different values (names), even for califications/degrees (and no way to constrain it). Isn't a small chance that as far as I do not look for the degrees in searches and all I need is to print that part of CV (like Europass is doing), than what it come as a penalty is in fact a huge advantage (like a bad huge ugly and unknown player kicking Wayne Gretzky? I wouldn't say this is a bad penalty...)

At the end of the day I am not a purist in neither sense of the word, if it is faster this way (not to mention easier and safer from my point of view or at least my set of skills) I'll go for it. I know for sure that I can optimize the query so it will disregard the compound key and still maintain the integrity of the database as the constraints are in place. To me seems like a smart move...

I see there is the option to put such rows in memory, I guess others can be put as well and it will probably work faster. Do you know how may be calculated the memory per number of records? What it will be a good practice about memory rows (maybe the best practice is not to use them!).

Sir, it is always a pleasure hearing from you and I have to say that I learned a lot from this forum (basically only from you). I am not sure how can I ever repay your kindness. In terms of reviews I did my best. When we will launch the web site (kind of a monster.com) I am tempted to thank you publicly on our site as it is true that many things weren't possible without your help. With your approval, of course, as I do not want to drag your name into an obscure project... after all I want to "thank you"...

Rod Stephens · May 7th, 2012, 04:53 PM

Quote:

I begin to have some remorse about using MySQL

I think MySQL is a good choice and has the big advantage that it's free. It;s easy enough to work around the RowId issue by adding an auto-number field.

Quote:

CONSTRAINTS cannot be set on/off so we have to take the db out to add a constraint

Hopefully you won't have to add them very often.

Quote:

I see there is the option to put such rows in memory, I guess others can be put as well and it will probably work faster. Do you know how may be calculated the memory per number of records? What it will be a good practice about memory rows (maybe the best practice is not to use them!).

Do you mean cache rows in memory? It's going to depend on a lot of things. It sounds like your application will be running from a lot of users' browsers. In that case, you may not be able to cache too much without the possibility of the users interfering with each other.

Quote:

In terms of reviews I did my best.

Thanks! That's very important for book sales.

Post a note here when it's available so I (and others) can take a look.

masterlayouts · May 8th, 2012, 03:47 PM

sure, the review it's here.

http://www.amazon.com/review/RZGGKGJ...cm_cr_rdp_perm

At the time i wrote it i didn't knew (nor expected) so much help on this forum. This is a treasure.

Thank you.