I have created a test table with a single column - Name using the SQL query given below.



A NOT NULL Constraint has been added to the field, yet when I run either of the queries, the operation does not fail.





Another example of a constraint not working under MySQL could be the FOREIGN KEY.



The last line of the query should result in an error, because the value 2 does not exist under the parent table. Am I missing out on something or is it just that MySQL has show-off constraints just for the sake of being SQL.

Hi turbopowerdmaxsteel,

A bit of a late reply, but I noticed no one made a response to your post. I do hope this is still relevant, but welcome to MySQL. Coming from an Oracle background, I know how you feel. I'm sure that (by now) you have already figured out why MySQL's behavior is as such, but for the benefit of others who may find this useful, here is an explanation.

MySQL NULLs and empty values are different. When inserting new values, MySQL will use the default value based on your column type. In this case for CHARs, it will be an empty value. Had you provided a different default (on the table schema), it will be used instead of an empty value in your "INSERT INTO test VALUES();" statement. Given this, we see that there really is no issue in the "not null" constraint. Try inserting a NULL instead.

As for Foreign Keys, it is available for all storage engines as of MySQL 5.2. For lower versions, only select engines such as InnoDB supports it. The storage engine can be chosen upon creating a table.

Cheers.

Thanks for the information, altimit and welcome to Astahost. Infact I had assumed that this was the desired behavior of MySQL (Constraints being only phony). I thougth it was similar to variables with no types as we have in Javascript, PHP, etc for performance reasons. I had entrusted the validation part to Javascripts and PHP. But, its always better to have them at the lowest level.

I have only done a rapid e-learning on MySQL from random pages on the Net. Good ol' google mostly helps out when I am stuck. All these engine stuff where like hocus-focus, so I skipped them altogether.

My localhost is running on MySQL 5.0.33, so I tried using the InnoDB engine and ran the following query:-



The query, however, executed successfully even though its still breaking the foreign key constraint. Am I still doing something wrong?

Thanks very much.

Yes this is quite a common scenario, but would boil down to the fact that MySQL treats NULLs as actual "values" (to put it very bluntly). I would suggest you add a "NOT NULL" clause to the Id's you are trying to associate (i.e., "id INT NOT NULL"); perhaps a good way of thinking on why the current scenario does not raise an exception is that the Child Id has successfully been matched against a NULL Parent Id.

Did it do the trick?

This post has been edited by altimit: Jan 1 2008, 11:53 AM

I tried that using the following code:-



But, it got executed nice and well too.

Thanks for the quick response;

It would seem that the Child ID is not currently being indexed, would that be correct? Kindly try the following set of statements:



My installation flags the final statement with an error "#1216 - Cannot add or update a child row: a foreign key constraint fails". How about yours?

Amazingly it runs without any error for me. What could the issue be? My MySQL Version is 5.0.33 and I tried running the query on phpMyAdmin - 2.9.2.

I must admit, that is quite strange.

Using phpMyAdmin, can you please verify that the engine being used is indeed InnoDB? Let us hope that there is no server configuration blocking the engine from being used, and automatically substituting MyISAM instead.

I have done my testing on a live server with MySQL version 4.1.22. phpMyAdmin's version is 2.11.0, but the does not matter as I used SSH and connected via MySQL's console.

Please also remember that InnoDB is transactional; you might want to create the tables first, before adding any data (in a separate phpMyAdmin command "instance").

This post has been edited by altimit: Jan 1 2008, 12:56 PM

I did try creating the Tables first, but it didn't work. You have hit the correct cause, though. The engine gets reset to MyISAM. When I tried to create a table with phpMyAdmin's interface, I don't see INNODB in the list. These are the storage engines available: MyISAM, Memory, Archive, Mrg_MyISAM.

I wonder why?

Hi,

I have a hunch that you are using a Windows Essentials MySQL installation, instead of the standard Windows one. Would this be correct? Also, kindly try issuing an "SHOW ENGINES;" statement. What is the output?

Thanks.