Constraints Don't Work In Mysql?

[turbopowerdmaxsteel 0]

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

 

CREATE TABLE Test(Name CHAR(10) NOT NULL)

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

 

INSERT INTO test VALUES();

INSERT INTO test VALUES("");

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

 

CREATE TABLE parent (id INT PRIMARY KEY);CREATE TABLE CHILD(id INT,FOREIGN KEY (id) REFERENCES parent(id));insert into parent values (1);insert into child values (2);

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.

[altimit 0]

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.

[turbopowerdmaxsteel 0]

Thanks for the information, altimit and welcome to Xisto. 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:-

CREATE TABLE parent (id INT PRIMARY KEY)Engine=InnoDB;CREATE TABLE CHILD(id INT,FOREIGN KEY (id) REFERENCES parent(id))Engine=InnoDB;INSERT INTO parent VALUES (1);INSERT INTO child VALUES (2);

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

[altimit 0]

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?

Edited January 1, 2008 by altimit (see edit history)

[turbopowerdmaxsteel 0]

I tried that using the following code:-

CREATE TABLE parent (id INT PRIMARY KEY NOT NULL)Engine=InnoDB;CREATE TABLE CHILD(id INT NOT NULL,FOREIGN KEY (id) REFERENCES parent(id))Engine=InnoDB;INSERT INTO parent VALUES (1);INSERT INTO child VALUES (2);

But, it got executed nice and well too.

[altimit 0]

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:

CREATE TABLE parent(	id INT NOT NULL,	PRIMARY KEY (id)) ENGINE = INNODB;CREATE TABLE child (	parent_id INT NOT NULL,	INDEX (parent_id),	FOREIGN KEY (parent_id) REFERENCES parent(id) ) ENGINE = INNODB;INSERT INTO parent VALUES (1);INSERT INTO child VALUES (2);

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?

[turbopowerdmaxsteel 0]

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.

[altimit 0]

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

Edited January 1, 2008 by altimit (see edit history)

[turbopowerdmaxsteel 0]

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?

[altimit 0]

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.

[turbopowerdmaxsteel 0]

Yes, I am using XAMPP. The "SHOW ENGINES;" statement shows InnoDB as disabled. Perhaps enabling it requires some INI file configuration?

Edit: Found the way to enable InnoDB in https://www.mydigitallife.info/2006/10/21/ep-installation/

It all works out nicely now. Thanx for your help.

Edited January 1, 2008 by turbopowerdmaxsteel (see edit history)

[altimit 0]

Edit: I'm truly glad that it works for you now. Have fun with InnoDB! If you do not need transactional engines though, do consider using MyISAM with constraints enabled. This feature is available in the more recent MySQL versions.

 

 

---------------

 

 

 

That would most likely be the issue. Please see your my.cnf file which contains MySQL's configuration. There would be a line there with the text "skip-innodb". This line should be commented-out.

 

After that, there are lines that are required to be UNcommented; you will find that the configuration file is quite well-documented and very understandable. It will point to you lines that must be modified to enable InnoDB.

 

For example, change:

 

- Comment the following line to unskip and use InnoDB

skip-innodb

 

- Uncomment the following options for InnoDB database if you are using InnoDB tables.

#innodb_data_home_dir = C:/xampp/xampp/mysql/data/

#innodb_data_file_path = ibdata1:10M:autoextend

#innodb_log_group_home_dir = C:/xampp/xampp/mysql/data/

#innodb_log_arch_dir = C:/xampp/xampp/mysql/data/

 

- Uncomment the lines and set innodb_buffer_pool_size up to 50% - 80% of RAM for optimization of InnoDB databases, try not to memory usage too high.

#set-variable = innodb_buffer_pool_size=16M

#set-variable = innodb_additional_mem_pool_size=2M

 

- Uncomment the lines and set innodb_log_file_size to 25% of InnoDB buffer pool size for optimisation.

#set-variable = innodb_log_file_size=5M

#set-variable = innodb_log_buffer_size=8M

#innodb_flush_log_at_trx_commit=1

#set-variable = innodb_lock_wait_timeout=50

To:

 

#skip-innodb

 

innodb_data_home_dir = C:/xampp/xampp/mysql/data/

innodb_data_file_path = ibdata1:10M:autoextend

innodb_log_group_home_dir = C:/xampp/xampp/mysql/data/

innodb_log_arch_dir = C:/xampp/xampp/mysql/data/

 

set-variable = innodb_buffer_pool_size=16M

set-variable = innodb_additional_mem_pool_size=2M

 

set-variable = innodb_log_file_size=5M

set-variable = innodb_log_buffer_size=8M

innodb_flush_log_at_trx_commit=1

set-variable = innodb_lock_wait_timeout=50

Do not copy-paste this though, check with your actual documentation. In case this has been done and InnoDB still is not active, you may need to use a non-reduced version of MySQL taken from the actual official site.
Edited January 1, 2008 by altimit (see edit history)