Constraints Don't Work In Mysql?

turbopowerdmaxsteel

Aug 31 2007, 04:23 AM

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

CODE

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.

CODE

INSERT INTO test VALUES();

CODE

INSERT INTO test VALUES("");

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

CODE

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.

Comment/Reply (w/o sign-up)

altimit

Dec 31 2007, 07:30 PM

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.

Comment/Reply (w/o sign-up)

turbopowerdmaxsteel

Dec 31 2007, 08:04 PM

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

CODE

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?

Comment/Reply (w/o sign-up)

altimit

Jan 1 2008, 11:51 AM

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?

Comment/Reply (w/o sign-up)

turbopowerdmaxsteel

Jan 1 2008, 12:02 PM

I tried that using the following code:-

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.

Comment/Reply (w/o sign-up)

altimit

Jan 1 2008, 12:20 PM

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:

CODE

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?

Comment/Reply (w/o sign-up)

turbopowerdmaxsteel

Jan 1 2008, 12:44 PM

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.

Comment/Reply (w/o sign-up)

altimit

Jan 1 2008, 12:51 PM

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

Comment/Reply (w/o sign-up)

turbopowerdmaxsteel

Jan 1 2008, 01:08 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?

Comment/Reply (w/o sign-up)

altimit

Jan 1 2008, 01:15 PM

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.

Comment/Reply (w/o sign-up)

Latest Entries

altimit

Jan 1 2008, 01:34 PM

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:

QUOTE

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

QUOTE

#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.

Comment/Reply (w/o sign-up)