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.
Reply
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.
Reply
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?
Reply
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?
Reply
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.
Reply
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?
Reply
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.
Reply
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").
Reply
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?
Reply
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.
Reply
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.
Reply
Similar Topics
Keywords : constraints, work, mysql,
- How To Increment A Mysql Field At Regular Intervals?
(4)
MySQL For EasyPHP Users
Does anyone use this program? (4) I installed a program called EasyPHP because it was an easy install and I wanted to be able to learn
php and still be able to down the road learn MySQL. But I can't figure out how to use MySQL.
Does anyone out there use this program that could tell me alittle about it. Alot of the product
site is written in a different language, but everything on the program is english so that is good.
Any help would be appreciated.....
Random MySQL Entry
(1) I am tring to figure this out and I can't. When you set a primary key. Say something_id and it
is defined as a int (12) auto_inc. I go and fill it up with 10 entries, meaning it is now set to be
something_id entry 10, but say I go and delete entry 5. Why doesn't it take that 5 for the next
number, but instead it does a 11 and leaves 5 blank. How might I get it to where it fill's up
the 5 instead of going to the next number or is that just one of the bad thing's of having a sql
entry auto_inc ? Also, the random part. The reason I ask this is I take the....
Looking for constraints, work, mysql,
|
|
Searching Video's for constraints, work, mysql,
|
advertisement
|
|
