foreign keys

[teedoff]

Ok I am still unable to create a foreign key restraint in my database. I know I posted this in another post, but after 3 or 4 days with no responses, I wanted to reword and properly explain my problem. I executed two show create table commands for my two tables. Here are the results.

Table  Create Table rlbbulbs 

CREATE TABLE `rlbbulbs` (
 `bulbID` int(11) NOT NULL AUTO_INCREMENT,
 `item` char(255) DEFAULT NULL,
 `brandID` int(11) NOT NULL,
 `bulbDesc` longtext,
 `cost` text,
 `price` text,
 `qtyPerPrice` char(100) DEFAULT NULL,
 `wattage` char(255) DEFAULT NULL,
 `voltage` char(255) DEFAULT NULL,
 `base` char(255) DEFAULT NULL,
 `glass` char(255) DEFAULT NULL,
 `filament` char(255) DEFAULT NULL,
 `avgLife` char(255) DEFAULT NULL,
 `beamAngle` char(255) DEFAULT NULL,
 `catagory` char(255) DEFAULT NULL,
 `oldPage` char(255) DEFAULT NULL,
 `keyWords` longtext,
 `image1` char(255) DEFAULT NULL,
 PRIMARY KEY (`bulbID`),
 UNIQUE KEY `bulbID_3` (`bulbID`),
 UNIQUE KEY `bulbID_4` (`bulbID`),
 KEY `bulbID` (`bulbID`),
 KEY `item` (`item`),
 KEY `bulbID_2` (`bulbID`),
 KEY `item_2` (`item`),
 KEY `item_3` (`item`)
) ENGINE=InnoDB AUTO_INCREMENT=1595 DEFAULT CHARSET=latin1

Create Table rlbbrand 

 TABLE `rlbbrand` (
 `brandID` int(11) NOT NULL AUTO_INCREMENT,
 `brandName` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`brandID`),
 UNIQUE KEY `brandID` (`brandID`),
 UNIQUE KEY `brandID_2` (`brandID`),
 KEY `brandName` (`brandName`)
) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=latin1

I know I have duplicate entries for keys and unique keys. I want to create a foreign key for brandID which is what relates the rlbbrand table to the rlbbulbs table. Many bulbs can have the same brand. It was suggested to me that I should make sure all the fields and data types were the same for both tables, which I have. It was even suggested that I set brandID to "Unique" within the rlbbulbs table, which I think is wrong, but tried it anyway and got an error saying "1062 Duplicate entry '1' for key brandID". Please I need to resolve this. I have been trying to correct this issue for a week now, but cant seem to figure it out and noone has seemed to be able to help me in forums. Thanks for any help and suggestions.

Tony

[teedoff]

wow ok this forum has suddenly become dead the last day or so! lol....

[tesuji]

Hello

obviously there is a one-to-many relationship between table rlbbrand and table rlbbulbs. That means from the view point of rlbbrand that there are many rows of rlbbulbs could be related to. From the view point of rlbbulbs a specific row can be related to only one row of rlbbrand.

"Graphically" it is: rlbbrand -------< rlbbulbs, where --<, also named crowfoot, points to the table,rlbbulbs, where the primary from the related table, rlbbrand, moves in and become here a foreign key.

Therefore table rlbbulbs must have the following objects at least:

brandID int -- not necessary: not null
and
foreign key brandID references rlbbrand(brandID)

There is nothing more!

No, I assumed that the other guys would have already solved your problem, usually I don't meddle in into advanced discussions.

-- tesu

[tesuji]

As for your both tables:

Create Table rlbbrand 
 TABLE `rlbbrand` (
                                          `brandID` int(11) NOT NULL AUTO_INCREMENT,
 `brandName` varchar(255) DEFAULT NULL,
                                          PRIMARY KEY (`brandID`),
) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=latin1

Table  Create Table rlbbulbs 

CREATE TABLE `rlbbulbs` (
                                         `bulbID` int(11) NOT NULL AUTO_INCREMENT,
 `item` char(255) DEFAULT NULL,
                                         `brandID` int(11), -- ------ NOT NULL usually not necessary
 `bulbDesc` longtext,
 `cost` text,
 `price` text,
 `qtyPerPrice` char(100) DEFAULT NULL,
 `wattage` char(255) DEFAULT NULL,
 `voltage` char(255) DEFAULT NULL,
 `base` char(255) DEFAULT NULL,
 `glass` char(255) DEFAULT NULL,
 `filament` char(255) DEFAULT NULL,
 `avgLife` char(255) DEFAULT NULL,
 `beamAngle` char(255) DEFAULT NULL,
 `catagory` char(255) DEFAULT NULL,
 `oldPage` char(255) DEFAULT NULL,
 `keyWords` longtext,
 `image1` char(255) DEFAULT NULL,
                                          PRIMARY KEY (`bulbID`),
                                          FOREIGN KEY brandID REEFRENCES rlbbrand(brandID) 
) ENGINE=InnoDB AUTO_INCREMENT=1595 DEFAULT CHARSET=latin1

Don't get confused with other unique or not-unique indexes/keys. You only need such stuff if you have to manage many many rows and the performance is going down. Also foreign keys usually don't need extra indexes, except for many-to-many relationships.

The most important thing is that all primary keys as well as all foreign keys be correctly defined.

As for foreign keys of one-to-many relationships: My above suggestion denotes the common case of such applications The foreign brandID key is so-called "non-identifying" in table rlbbulbs, that is, it is NOT a member of the primary key of rlbbulbs. Sometimes it is necessary that the foreign key also become member of the primary key, thus identifying foreign key. This case is considerably rarer and depends on the specific application.

-- tesu

[teedoff]

Tesu, thanks! I appreciate the info and help, however I realize all this that you pointed out. The problem is I CANT define a foreign key ANYWHERE. lol I have tried to define rlbbulbs(brandID) as a foreign key that references rlbbrand(brandID), but it always gives me an error.
1452 - Cannot add or update a child row: a foreign key constraint fails('rlb',<results 2 when explaining filename '#sql-85c_a9'>, CONSTRAINT 'fk_brandID'FOREIGN KEY ('brandID') REFERENCES 'rlbbrand'(brandID'))

[tesuji]

Hi

I just did creating both tables on mysql 5.1.41-3ubuntu12.6 using tOra 2.1.1, and it works:

Create Table rlbbrand(
                                          brandID int NOT NULL AUTO_INCREMENT,
 brandName varchar(255) DEFAULT NULL,
                                          PRIMARY KEY (brandID)
);

show create table rlbbrand;

CREATE TABLE rlbbulbs (
                                         bulbID int(11) NOT NULL AUTO_INCREMENT,
 item char(255) DEFAULT NULL,
                                         brandID int(11), -- ------ NOT NULL usually not necessary
 
                                          PRIMARY KEY (bulbID),
                                          FOREIGN KEY (brandID) REFERENCES rlbbrand(brandID) 
);
commit

show create table rlbbrand;
CREATE TABLE `rlbbrand` (
  `brandID` int(11) NOT NULL AUTO_INCREMENT,
  `brandName` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`brandID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

show create table rlbbulbs;

CREATE TABLE `rlbbulbs` (
  `bulbID` int(11) NOT NULL AUTO_INCREMENT,
  `item` char(255) DEFAULT NULL,
  `brandID` int(11) DEFAULT NULL,
  PRIMARY KEY (`bulbID`),
  KEY `brandID` (`brandID`),
  CONSTRAINT `rlbbulbs_ibfk_1` FOREIGN KEY (`brandID`) REFERENCES `rlbbrand` (`brandID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
select version();

There was a little typo in the word "REFRENCES".

-- tesu

[teedoff]

Ok I just did a quick test with another database using navicat, which I was using with the rlb database. I created a foreign key constrain with no problems! Did it exactly the same way I have been trying to do the one above, so obvioulsy something is either different in the way the rlb db is set up, or something else is going on.

CREATE TABLE `city` (
 `ID` int(11) NOT NULL AUTO_INCREMENT,
 `Name` char(35) NOT NULL DEFAULT '',
 `CountryCode` char(3) NOT NULL DEFAULT '',
 `District` char(20) NOT NULL DEFAULT '',
 `Population` int(11) NOT NULL DEFAULT '0',
 PRIMARY KEY (`ID`),
 KEY `fk_country_code` (`CountryCode`)
) ENGINE=MyISAM AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1

[teedoff]

Can you give me the SQL command so I can actually go into phpadmin and type it again. I have already tried this several times from someone else's code, but I want to make sure I typed the right command. Thanks!

[tesuji]

Here is it:

Create Table rlbbrand(
                                          brandID int NOT NULL AUTO_INCREMENT,
 brandName varchar(255) DEFAULT NULL,
                                          PRIMARY KEY (brandID)
);

show create table rlbbrand;

CREATE TABLE rlbbulbs (
                                         bulbID int(11) NOT NULL AUTO_INCREMENT,
 item char(255) DEFAULT NULL,
                                         brandID int(11), -- ------ NOT NULL usually not necessary
 
                                          PRIMARY KEY (bulbID),
                                          FOREIGN KEY (brandID) REFERENCES rlbbrand(brandID) 
);
commit

show create table rlbbrand;
CREATE TABLE `rlbbrand` (
  `brandID` int(11) NOT NULL AUTO_INCREMENT,
  `brandName` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`brandID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

show create table rlbbulbs;

CREATE TABLE `rlbbulbs` (
  `bulbID` int(11) NOT NULL AUTO_INCREMENT,
  `item` char(255) DEFAULT NULL,
  `brandID` int(11) DEFAULT NULL,
  PRIMARY KEY (`bulbID`),
  KEY `brandID` (`brandID`),
  CONSTRAINT `rlbbulbs_ibfk_1` FOREIGN KEY (`brandID`) REFERENCES `rlbbrand` (`brandID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
select version();

My prior posting contained some mistakes I already corrected them now. Above code it the exact copy from tora editor and executed without any error.

There is also a screen shot.

tesu

[teedoff]

So I need to type all of your post in phpMyadmin sql? Navicat doesnt seem to have a text editor/window to enter sql commands.

[tesuji]

No, you can copy the code from daniweb MySQL Syntax (Toggle Plain Text) to phpmyadmin and complete it by the lines I dropped, sorry for that.

(Usually I don't use mysql for serious projects :) )

[tesuji]

He, I have forgotten to mention that the table where you want to add a foreign key by ALTER statement usually must be empty. Also referenced table could be required to be empty too, especially on simple database systems.

-- tesu

[teedoff]

lol maybe then thats the problem all along! Neither tables are empty! I created them both in Access and migrated them to MySQL with Navicat import wizard. I had the tables already set up as a one to many relationship in Access and I guess just assumed they would be that way in MySQL. So now, how can I add the foreign key with a populated table without losing the records already there?

ok but wait, the test table I added a foreign key to a few minutes ago already had data in it too and it worked!

[tesuji]

Hello

mysql isn't a simple database system! It does the job very well introducing a foreign key for tables rlbbrand (father) and rlbbulbs (son) by way of an ALTER statement, even if both have already inserted rows !!! Like Oracle and DB/2 it also obtrusively checks whether each foreign key stored in rlbbulbs also has its father in rlbbrand. If there is an orphan, the following error message arises:

Cannot add or update a child row: a foreign key constraint fails (`teedoff`.`#sql-20ff_29`, CONSTRAINT `#sql-20ff_29_ibfk_1` FOREIGN KEY (`brandID`) REFERENCES `rlbbrand` (`brandID`))

You may check your table rlbbulbs whether there are orphans. Such things could happen if foreign keys are not properly defined.

Here is some code to prove how clever mysql is:

Create Table rlbbrand(
                                          brandID int NOT NULL AUTO_INCREMENT,
 brandName varchar(255) DEFAULT NULL,
                                          PRIMARY KEY (brandID)
);
insert into rlbbrand (brandName) values ('First');
insert into rlbbrand (brandName) values ('Second');
insert into rlbbrand (brandName) values ('Third');
commit;
select * from rlbbrand;

CREATE TABLE rlbbulbs (
                                         bulbID int(11) NOT NULL AUTO_INCREMENT,
 item char(255) DEFAULT NULL,
                                         brandID int(11), -- ------ NOT NULL usually not necessary
 
                                          PRIMARY KEY (bulbID)
                                     --    ,FOREIGN KEY (brandID) REFERENCES rlbbrand(brandID) 
);
insert into rlbbulbs (item, brandID) values ('References Second', 2);
insert into rlbbulbs (item, brandID) values ('References First', 1);
insert into rlbbulbs (item, brandID) values ('References does not exists', 33); -- <---- should not allow to execute below alter statement

commit;
select * from rlbbulbs;

alter table rlbbulbs add foreign key (brandID) references rlbbrand (brandID);

-- Aha, mysql has learnt from Oracle, DB/2 and Sybase:

Cannot add or update a child row: a foreign key constraint fails (`teedoff`.`#sql-20ff_29`, CONSTRAINT `#sql-20ff_29_ibfk_1` FOREIGN KEY (`brandID`) REFERENCES `rlbbrand` (`brandID`))

This is a very good result for mysql inhibits violation of referential integrity of this type !

-- tesu

[teedoff]

Hi Tesu, thanks again so much for your help. I just am not understanding this all. I don't have any foreign keys defined at all as of this moment, but still get the errors. If I use this:

alter table rlbbulbs add foreign key (brandID) references rlbbrand (brandID);

I get the child error.

You may check your table rlbbulbs whether there are orphans

How can I do this?

Again, why can't I add a foreign key to the brandID field in rlbbulbs. Isn't that what I WANT to do? To make sure when someone adds a new record to rlbbulbs table, they HAVE to specify a brandID. Maybe I should forget about that. I mean I guess it wouldnt hurt to let people add records in rlbbulbs and leave the brandID field null.

[tesuji]

>>> How can I do this?

-- how many orphans ?
select count(*) as `so many orphans:` from rlbbulbs where brandID not in (select brandID from rlbbrand);

Again, if foreign key column brandID of rlbbulbs contains values other then NULL, which are not stored in column brandID of rlbbrand, you cannot add foreign key constraint to table rlbbulbs afterwards by means of simply altering table.

If you drop foreign key definitions, you should be clear that:

# each user data entry can destroy referential integrity,

# sooner or later your boss will get aware of that garbage data you are responsible for (except you are the boss yourself),

# to avoid garbage data you have to do considerably extra programming everyplace your application is modifying table rlbbulbs.

Ask yourself: Can I put stock in garbage data to seriously making money?

-- tesu

[teedoff]

Here is the results of the SQL command you just gave me.

Host: localhost
Database: rlb
Generation Time: Aug 24, 2010 at 07:39 PM
Generated by: phpMyAdmin 3.2.0.1 / MySQL 5.1.48-community
SQL query: SELECT count(*) as `so many orphans:` FROM rlbbulbs WHERE brandID NOT IN (SELECT brandID FROM rlbbrand); 
Rows: 1 

so many orphans:  
3

Now what that measn I have no idea...lol I see it says there are three orphans, but I dont know what it means, how to get rid of it, or how they got there...lol Again, I am not a DBA...I'm a web admin thats been forced out of neccessity to learn some DBA because my current employer had no database whatsoever and wanted web content that required use of a DB.

# each user data entry can destroy referential integrity,

Really, as far as I can tell, the only thing that could destroy the db integrity is if someone were to delete a brand from the rlbbrand table. I have not created a page for data entryt o do this (delete query on rlbbrand) so that shouldnt be an issue. The only thing I was concerned with was when adding new records to rlbbulbs, I wanted to ensure that a new record would also be forced to contain a brandID, whether is was brandID.unknown....or brandID.GE. Noone would be able to go into MySQL server to delete anything either.