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
  • 802 Views
  • 12 Hours Discussion Span
  • Latest Post Latest Post by teedoff
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.