Member Avatar for teedoff

Hi I am using Navicat and have two tables that have a one to many relationship. I have a table called rlbbulbs and a table called rlbbrand. brandID is the primary key in rlbbrand and is supposed to be the foreign key in rlbbulbs. Navicat has a way of creating foreign keys, which I went through and did, I think, but there is no marker or way to let me know if that column IS a foreign key or not. I thought I had set a restraint on this column that when inserting new records in the rlbbulbs table, that it had to have a record for the brandID(foreign key) column. I doesnt restrain me from doing that. I can insert records and leave the brandID column blank. I dont want this. I also tried doing this in PHPmyAdmin, but it gave me an error.

ALTER TABLE rlbbulbs ADD FOREIGN KEY ( brandID ) REFERENCES rlbbulbs( brandID ) ;

The error said:

#1005 - Can't create table 'rlb.#sql-8f0_37' (errno: 150)


I have looked through PHPmyAdmin several times and havent found a way to select foreign key for this column. There is a little key icon for Primary keys, but not foerign keys. Thanks for any help!

  • 2 Contributors
  • 4 Replies
  • 841 Views
  • 12 Hours Discussion Span
  • Latest Post Latest Post by teedoff

Dani AI

Generated

(and thanks to for the pointers) — short diagnosis and a small how-to.

Most likely causes here are: the foreign key was pointed at the wrong table, the tables are not using InnoDB (MyISAM ignores/does not support FKs), or the child column allows NULL (foreign keys permit NULLs unless the column is declared NOT NULL). Also the parent and child columns must match exactly in type/sign and the parent column must be indexed (PRIMARY KEY or UNIQUE). To require every bulb to have a brand you need both a proper FK and a NOT NULL child column.

Checklist and commands to run (replace types with the exact definition from your rlbbrand table):

SHOW CREATE TABLE rlbbrand;
SHOW CREATE TABLE rlbbulbs;

If either table is not InnoDB convert it:

ALTER TABLE rlbbrand  ENGINE=InnoDB;
ALTER TABLE rlbbulbs  ENGINE=InnoDB;

Fix existing empty/null children first:

SELECT COUNT(*) FROM rlbbulbs WHERE brandID IS NULL;
-- update or delete those rows, or set a valid brandID

Make the child column match the parent and be NOT NULL (example, change INT UNSIGNED to match your parent):

ALTER TABLE rlbbulbs
  MODIFY brandID INT UNSIGNED NOT NULL;

Then add the correct FK (note: reference rlbbrand, not rlbbulbs):

ALTER TABLE rlbbulbs
  ADD CONSTRAINT fk_rlbbulbs_brandID
  FOREIGN KEY (brandID) REFERENCES rlbbrand(brandID)
  ON DELETE RESTRICT ON UPDATE RESTRICT;

Troubleshooting notes: if FK creation fails with the generic errno (150), check engine/type/signature mismatches and run SHOW ENGINE INNODB STATUS for a clearer InnoDB error. Navicat and phpMyAdmin will only show FK links after the FK exists and both tables use InnoDB; phpMyAdmin’s Relation view/designer requires its configuration storage to be enabled. Finally, validate the ColdFusion form so it never submits an empty string for brandID (in non‑strict SQL modes an empty string can be coerced to 0), and prefer server-side validation before relying on DB constraints.

Recommended Answers

All 4 Replies

Member Avatar for pritaeas

I tried to add one in Navicat, however I get a different error. It could be that you have no rights to create this, but it is hard to say. I'd contact Navicat support if I were you. Normally they are quite helpful.

Member Avatar for teedoff

Hi pritaeas, I guess I will have to contact navicat support. Their help system is not very helpful with this issue. I went in and thought I did what they said to do, but I get an error now when I try to add records using my CF insert page.

Error Executing Database Query.
Cannot add or update a child row: a foreign key constraint fails (`jmsexton/rlbbulbs`, CONSTRAINT `brandID_fk` FOREIGN KEY (`brandID`) REFERENCES `rlbbrand` (`brandID`))


Again, looks like PHPmyAdmin would have a simple way of specifying a foreign key much like how you cna specify the primary key. Thanks for the info.

Edited by teedoff because: n/a
Member Avatar for pritaeas

Once you have a foreign key, you can no longer insert/update an item if the dependency is invalid. If you foreign key field does not match a valid one, you get that message.

Member Avatar for teedoff

hmm well I am confused..lol I have an insert form that inserts new bulbs in the rlbbulbs table. I wanted make sure no new records couldf be added to the rlbbulbs table without having a brandID added as well, for which I have a formfield there as well. Again, it still allows me to leave brandID field blank even after I set that field as foreign key and set ON Update and On Delete to "Restrict". Not sure then why it would not restrict me from adding a record without a value for brandID.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.