Replying to Constraints Don't Work In Mysql?

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.

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 http://www.mydigital...p-installation/

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

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.

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?

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

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.

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?

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.

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?

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:-

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?