Table constraints

Kudose

I am using MySQL 5.3 and am running into an issue with constraints.

I have 4 tables

feature_types
feature_names
listings
listings_features

The feature_names.featuretypeId references feature_types.featuretypeId fine
listings_features.listingId references listings.listingId fine

I cannot seem to get listings_features.featureId to reference feature_names.featureId

I get error 150 ... this is usually caused by conflicting column definitions, but in my case, all columns in question are CHAR(36) with the same default collation and character set.

My goal is to have a delete from feature_types cacade to the feature_names and listings_features and a delete from the listings to cascade to the listings_features

Can I not have two foreign key references on a table like this? Am I doing something wrong?

TIA!

nemonoman

Let's have the table definitions.

Kudose

CREATE TABLE `mod_listings_features` (
 `listingId` char(36) NOT NULL,
 `featureId` char(36) NOT NULL,
 KEY `lf2l` (`listingId`),
 CONSTRAINT `mod_listings_features_ibfk_1` FOREIGN KEY (`listingId`) REFERENCES `mod_listings` (`listingId`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `mod_listings_feature_names` (
 `featureId` char(36) NOT NULL,
 `featuretypeId` char(36) NOT NULL,
 `featureName` varchar(64) NOT NULL,
 KEY `ft2fn` (`featuretypeId`),
 CONSTRAINT `mod_listings_feature_names_ibfk_1` FOREIGN KEY (`featuretypeId`) REFERENCES `mod_listings_feature_types` (`featuretypeId`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `mod_listings_feature_types` (
 `featuretypeId` char(36) NOT NULL,
 `featuretypeName` varchar(64) NOT NULL,
 PRIMARY KEY  (`featuretypeId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `mod_listings` (
 `listingId` char(36) NOT NULL,
 `userId` char(36) NOT NULL,
 `listingType` enum('base','automobile','marine') NOT NULL default 'base',
 `listingPrice` decimal(10,2) unsigned NOT NULL default '0.00',
 `listingSalePrice` decimal(10,2) unsigned NOT NULL default '0.00',
 `listingTitle` varchar(150) NOT NULL,
 `listingSubTitle` varchar(150) NOT NULL,
 `listingDescription` text NOT NULL,
 `listingQty` int(12) unsigned NOT NULL default '1',
 `listingStatus` enum('0','1','2') NOT NULL,
 `listingCurrency` char(3) NOT NULL default 'USD',
 `listingCreation` int(12) NOT NULL,
 PRIMARY KEY  (`listingId`),
 KEY `userId` (`userId`),
 KEY `listingType` (`listingType`),
 KEY `listingPrice` (`listingPrice`),
 KEY `listingSalePrice` (`listingSalePrice`),
 CONSTRAINT `mod_listings_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `users` (`userId`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Thanks

Kudose

Error

SQL query:

ALTER TABLE `mod_listings_features` ADD FOREIGN KEY `lf2lfn` ( `featureId` ) REFERENCES `mod_feature_names` ( `featureId` ) ON DELETE CASCADE

MySQL said: Documentation
#1005 - Can't create table './xxxxxx/#sql-1e5b_8bdf.frm' (errno: 150)

nemonoman

Your alter table command changes the nature of mod_feature_names from a simple CHILD of mod_listings_feature_types to a RESOLVING TABLE between mod_listings_feature_types and mod_listings_features

I am unable to find MySQL docs on how a 'cascade' delete works when applied to a resolving table...but the NON-automated delete of records from a resolving table typically requires some human decision-making to be applied, either manually or programatically. So it may be that the constraint you're attempting to add with "Alter Table" makes MySQL feel a little shaky around the knees.

From the MySQL manual

You can use SHOW ENGINE INNODB STATUS to display a detailed explanation of the most recent InnoDB foreign key error in the server.

I'd like to know the answer to this question.

Kudose

Thanks for that info!

 Error in foreign key constraint of table xxxxxx/#sql-1e5b                                             _8bdf:
foreign key `lf2lfn`(`featureId`) references `mod_feature_names`(`featureId`) on delete cascade:
Cannot resolve table name close to:
(`featureId`) on delete cascade

nemonoman

Google: Cannot resolve table name close to

The INNODB engine can't find table name mod_feature_names

Which suggests that mod_feature_names is a MyISAM table.

Are you ABSOLUTELY sure that mod_feature_names is in fact currently created with "ENGINE=InnoDB"

Can you drop and recreatemod_feature_names?

Kudose

It is InnoDB as it has another constraint on it, but you did make me catch a typo in the table name ... it should have been mod_listings_feature_names ... still get an error 150 though, SHOW ENGINE INNODB STATUS

Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
See http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html
for correct foreign key definition.

All of the table definitions are above and they look correct. :queasy:

MUST the key be the first column? The info above says "or".

nemonoman

Try a dump, drop, and reload. Do for all. Maybe one of these tables got made with pre-upgraded MySQL?

Kudose

I am starting to think that there may be something wrong with my build ... all of a sudden another table constraint started to fail. There is a listing table and an images table and the images table has a key tying it to the listings table, but it stopped allowing record creation on the images table for some reason. I turned to images table into an MyISAM table and my script worked. Setting it back to InnoDB and putting the constraint back on allows it to work again.

bleh.

Thanks for your help thus far!