InnoDB best practices for ensuring unique tuple where one column can be NULL
hello, assume the following table: CREATE TABLE t ( id INT UNSIGNED auto_increment PRIMARY KEY, c1 INT UNSIGNED NOT NULL, c2 INT UNSIGNED NOT NULL, c3 INT UNSIGNED, UNIQUE (c1, c2, c3) ) engine = InnoDB; Our first issue is that the UNIQUE constraint on (c1,c2,c3) does not work in the case that a NULL value for c3 is inserted: mysql insert into t (c1, c2, c3) VALUES (1,1,NULL); Query OK, 1 row affected (0.01 sec) mysql insert into t (c1, c2, c3) VALUES (1,1,NULL); Query OK, 1 row affected (0.01 sec) Given this behavior, we cannot rely on the UNIQUE constraint to enforce two sets of otherwise identical values. However, in addition to the UNIQUE requirement that we have above, we *only* want the UNIQUE constraint to be checked when the c3 column has a NULL value, e.g.: -- insert of (1,1,NULL) and (1,1,NULL): error insert of (1,1,1) and (1,1,1): ok -- Clearly the latter case would not be allowed with a UNIQUE(c1,c2,c3) constraint. Attempting to ensure these constraints via triggers is problematic, because within separate transactions two different clients can insert identical values, and once finished with the transaction, the triggers will already have done their validation finding no error. Is there a standard way to perform this sort of checking? thanks! -lev -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: view irregularities
Shawn, On Feb 13, 2008, at 9:12 AM, Shawn Green wrote: Lev Lvovsky wrote: I'm running into a difficult to reproduce problem with a view which is similar to the following: CREATE TABLE Common ( COMMON_ID INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, COMMON_NAME VARCHAR(50), UNIQUE(COMMON_NAME) ) ENGINE = InnoDB; CREATE TABLE Parent ( PARENT_ID VARCHAR(50) NOT NULL, PARENT_NAME VARCHAR(50) NOT NULL, PARENT_COMMON_ID INT UNSIGNED, PRIMARY KEY (PARENT_ID, PARENT_COMMON_ID), FOREIGN KEY (PARENT_COMMON_ID) REFERENCES Common(COMMON_ID) ) ENGINE = InnoDB; CREATE TABLE Child ( CHILD_IDBINARY(20) NOT NULL PRIMARY KEY, PARENT_ID VARCHAR(50) NOT NULL, CHILD_NAME VARCHAR(50), CHILD_COMMON_ID INT UNSIGNED, FOREIGN KEY (PARENT_ID) REFERENCES Parent(PARENT_ID), FOREIGN KEY (CHILD_COMMON_ID) REFERENCES Common(COMMON_ID) ) ENGINE = InnoDB; DROP VIEW IF EXISTS BrokenView; CREATE VIEW BrokenView AS SELECT Child.* FROM Child LEFT JOIN Parent USING(PARENT_ID) WHERE Child.CHILD_COMMON_ID = Parent.PARENT_COMMON_ID; DROP VIEW IF EXISTS WorkingView; CREATE VIEW WorkingView AS SELECT Child.*, Parent.PARENT_NAME, Parent.PARENT_COMMON_ID FROM Child LEFT JOIN Parent ON (Child.PARENT_ID = Parent.PARENT_ID AND Child.CHILD_COMMON_ID = Parent.PARENT_COMMON_ID); Though the example cited above does not cause the problems that I'm running into, the table structure is similar. Specifically the fact that I'm doing a WHERE ... in the BrokenView vs WorkingView is seemingly the difference between getting rows returned and not. And this is exactly the cause of your problems. When you build a query that optionally includes a table, you get your results back in stages. The one stage of the query takes your LEFT JOIN and builds a list of matching rows. A later stage evaluates the terms in your WHERE clause. In this case in order to evaluate the WHERE clause, you force the existence of the rows from the `Parent` table just as if you had written an INNER JOIN. If there were no rows (no values) from the `Parent` table then the WHERE clause will evaluate as FALSE and those rows will not be returned. By putting both terms into the ON clause of your LEFT JOIN, you make it possible to have non-matched rows in your final result. Below is SQL which demonstrates the bug. Apparently this is an issue with the optimizer, and the temporary fix is the ALGORITHM=TEMPTABLE as I'd mentioned in my previous email. If this is not a bug, I would think that the behavior would be consistent before and after a 'flush table ...' command. -lev DROP DATABASE TestDB; CREATE DATABASE TestDB; USE TestDB; CREATE TABLE Common ( COMMON_ID INT UNSIGNED NOT NULL PRIMARY KEY ) ENGINE=InnoDB; CREATE TABLE Parent1 ( PARENT_ID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, EXT_PARENT_ID INT UNSIGNED NOT NULL, PARENT_NAME INT UNSIGNED NOT NULL, COMMON_ID INT UNSIGNED NOT NULL, FOREIGN KEY (COMMON_ID) REFERENCES Common(COMMON_ID) ON DELETE RESTRICT ) ENGINE=InnoDB; CREATE TABLE Child1 ( CHILD_ID INT UNSIGNED NOT NULL PRIMARY KEY, PARENT_ID INT UNSIGNED NOT NULL, COMMON_ID INT UNSIGNED NOT NULL, FOREIGN KEY (PARENT_ID) REFERENCES Parent1(PARENT_ID) ON DELETE CASCADE, FOREIGN KEY (COMMON_ID) REFERENCES Common(COMMON_ID) ON DELETE RESTRICT ) ENGINE=InnoDB; CREATE TABLE Parent2 ( PARENT_ID INT UNSIGNED NOT NULL, PARENT_NAME INT UNSIGNED NOT NULL, COMMON_ID INT UNSIGNED NOT NULL, PRIMARY KEY (PARENT_ID, COMMON_ID), FOREIGN KEY (COMMON_ID) REFERENCES Common(COMMON_ID) ON DELETE RESTRICT ) ENGINE=InnoDB; CREATE TABLE Child2 ( CHILD_ID INT UNSIGNED NOT NULL PRIMARY KEY, PARENT_ID INT UNSIGNED NOT NULL, COMMON_ID INT UNSIGNED NOT NULL, FOREIGN KEY (PARENT_ID, COMMON_ID) REFERENCES Parent2(PARENT_ID, COMMON_ID) ON DELETE CASCADE, FOREIGN KEY (COMMON_ID) REFERENCES Common(COMMON_ID) ON DELETE RESTRICT ) ENGINE=InnoDB; CREATE VIEW ViewParent2 AS SELECT Parent2.* FROM Parent2; CREATE VIEW ViewChild2 AS SELECT Child2.* FROM Child2 LEFT JOIN ViewParent2 USING(PARENT_ID) WHERE Child2.COMMON_ID = ViewParent2.COMMON_ID; INSERT INTO Common SET COMMON_ID = 1; INSERT INTO Parent1 SET COMMON_ID = 1, EXT_PARENT_ID = 1, PARENT_NAME = 1; SELECT LAST_INSERT_ID() INTO @_parent_id; INSERT INTO Child1 SET COMMON_ID = 1, PARENT_ID = @_parent_id, CHILD_ID = 1; SELECT 1 FROM Child2 LEFT JOIN Child1 ON Child1.CHILD_ID = Child2.CHILD_ID AND Child1.COMMON_ID = Child2.COMMON_ID LEFT JOIN Parent2 ON Parent2.PARENT_ID = Child2.PARENT_ID AND Parent2.COMMON_ID = Child2.COMMON_ID WHERE ( Child1.CHILD_ID IS NOT NULL ) AND ( Child2.CHILD_ID = 1 ); INSERT INTO Parent2 SET PARENT_ID = 1, COMMON_ID = 1, PARENT_NAME = 1; INSERT INTO Child2 SET CHILD_ID = 1, PARENT_ID = 1, COMMON_ID = 1; -- Technically Child2, and ViewChild2 have the same data in them. However in selecting from ViewChild2 -- on the pkey returns 0 rows, whereas the base table returns the correct
view irregularities
I'm running into a difficult to reproduce problem with a view which is similar to the following: CREATE TABLE Common ( COMMON_ID INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, COMMON_NAME VARCHAR(50), UNIQUE(COMMON_NAME) ) ENGINE = InnoDB; CREATE TABLE Parent ( PARENT_ID VARCHAR(50) NOT NULL, PARENT_NAME VARCHAR(50) NOT NULL, PARENT_COMMON_ID INT UNSIGNED, PRIMARY KEY (PARENT_ID, PARENT_COMMON_ID), FOREIGN KEY (PARENT_COMMON_ID) REFERENCES Common(COMMON_ID) ) ENGINE = InnoDB; CREATE TABLE Child ( CHILD_IDBINARY(20) NOT NULL PRIMARY KEY, PARENT_ID VARCHAR(50) NOT NULL, CHILD_NAME VARCHAR(50), CHILD_COMMON_ID INT UNSIGNED, FOREIGN KEY (PARENT_ID) REFERENCES Parent(PARENT_ID), FOREIGN KEY (CHILD_COMMON_ID) REFERENCES Common(COMMON_ID) ) ENGINE = InnoDB; DROP VIEW IF EXISTS BrokenView; CREATE VIEW BrokenView AS SELECT Child.* FROM Child LEFT JOIN Parent USING(PARENT_ID) WHERE Child.CHILD_COMMON_ID = Parent.PARENT_COMMON_ID; DROP VIEW IF EXISTS WorkingView; CREATE VIEW WorkingView AS SELECT Child.*, Parent.PARENT_NAME, Parent.PARENT_COMMON_ID FROM Child LEFT JOIN Parent ON (Child.PARENT_ID = Parent.PARENT_ID AND Child.CHILD_COMMON_ID = Parent.PARENT_COMMON_ID); Though the example cited above does not cause the problems that I'm running into, the table structure is similar. Specifically the fact that I'm doing a WHERE ... in the BrokenView vs WorkingView is seemingly the difference between getting rows returned and not. After my program inserts data into all three tables, I can do a query on the base tables, however doing a query on the view with a WHERE on the primary key of the Child table returns no rows. Doing that same query on the base table returns rows as expected. Performing a 'flush table Parent' per the example above fixes the problem. Additionally, using the 'temptable' algorithm works, whereas explicitly using the 'merge' algorithm, or the 'undefined' default causes the problem to appear. All tables are InnoDB, 'select version()' returns '5.0.50-enterprise- gpl-log'. Having read the VIEW command documentation, it wouldn't seem that I'm doing anything against the rules in my query that would cause this. Any help would be greatly appreciated! thanks, -lev -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: which duplicate key was hit on last insert?
On Nov 13, 2007, at 1:25 AM, yaya sirima wrote:
Hi,
CREATE TABLE Test (
COL1INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
COL2VARCHAR(10) NOT NULL,
COL3VARCHAR(10) NOT NULL,
UNIQUE(COL2, COL3); --(not that)
FULLTEXT(col1,col2)
);
Try this property FULLTEXT
The columns here were used as an illustration of the two different
types of duplicate keys which might have been hit. We use UNIQUE
constraints to include binary, int's, etc...
thank you for the info however, it may be useful elsewhere.
-lev
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
which duplicate key was hit on last insert?
We have tables in our database that, in addition to primary key constraints also have unique() constraints of several columns in the table: CREATE TABLE Test ( COL1INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, COL2VARCHAR(10) NOT NULL, COL3VARCHAR(10) NOT NULL, UNIQUE(COL2, COL3); ); There are two insert scenarios which would cause a DUPLICATE KEY error - one which contained a pre-existing COL1 value, and another which contained a pre-existing COL2,COL3 value. Is there any way to differentiate between which KEY, 'PRIMARY', or 'UNIQUE' (as listed by the 'show create table Test' in the mysql client) was actually violated in the last insert? This is specifically for use with the ON DUPLICATE KEY UPDATE clause. Normally we use behavior this to produce a no-op upon adds of identical records, however this can cause problems in the case that the KEY that was DUPLICATE was in fact the UNIQUE() key, and not the PRIMARY key. Knowing which of these triggered the DUPLICATE key error would be helpful in determining what to do next - is this information stored anyplace? thanks, -lev -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
case sensitivity in stored procedure formal arguments
Could someone explain the logic of how case sensivity works on stored procedure formal argument names? Example: CREATE PROCEDURE sp_Test ( IN col1 INT, IN col2 INT ) BEGIN INSERT INTO Table SET COL1 = col1, COL2 = col2; END We've found that this is problematic in some cases because of the case insensitivity of column names, so I'm looking to see if there's a general logic for how to name formal arguments that have a similar name to a table column name (we ended up putting a '_' in the beginning of the argument name). thanks! -lev -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
on duplicate key question
This is somewhat related to my last question RE conflicting procedure argument names, but in regards to multi-row inserts... suppose the following procedure: DROP PROCEDURE IF EXISTS sp_ImportedUpdate | CREATE PROCEDURE sp_ImportedUpdate () DETERMINISTIC CONTAINS SQL MODIFIES SQL DATA BEGIN INSERT INTO Destination SELECT ID, CONTACT_NAME, CONTACT_EMAIL, CONTACT_PHONE, ADDRESS_1, ADDRESS_2, CITY, PROVINCE, POSTAL_CODE, COUNTRY, CIRCUIT_ID, TIME_ZONE FROM ToBeUpdated ON DUPLICATE KEY UPDATE ID = VALUES(ID), CONTACT_NAME = VALUES(CONTACT_NAME), CONTACT_EMAIL = VALUES(CONTACT_EMAIL), CONTACT_PHONE = VALUES(CONTACT_PHONE), ADDRESS_1 = VALUES(ADDRESS_1), ADDRESS_2 = VALUES(ADDRESS_2), CITY = VALUES(CITY), PROVINCE = VALUES(PROVINCE), POSTAL_CODE = VALUES(POSTAL_CODE), COUNTRY = VALUES(COUNTRY), CIRCUIT_ID = VALUES(CIRCUIT_ID), TIME_ZONE = VALUES(TIME_ZONE); END | - Here, 'ToBeUpdated' is a view which highlights changes between to tables with the same primary keys, but possibly different values in other columns. The naming scheme here can be tricky, and on first glance given previous problems, it would look to fail, because the column name in VALUES() is the same as the one on the left. However, this actually works as intended, where the changed values are the ones being returned by VALUE. Can someone explain why this is? I'd like to have a solid understanding of the scoping of procedure variables which happen to be the same as column names. thank you! -lev -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
external updates/additions of database entries
Hello, I'm looking for help in merging the contents of two database tables with identical columns between them. A base set of data will be stored in the permanent table, and incremental additions and/or changes will be introduced in the staging table the ID's of the data in the tables is the only thing which won't be in sync. Initially looking through the documentation, a 'natural join' seemed to be the best way to highlight changes. My ideal result would have been all of the entries from the staging table not in the permanent table, however I'm unclear on how to form a query without explicitly naming each row name and using it with a comparison operation. Any help on this subject would be appreciated! thanks! -lev -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
