Status

[dino2dy]

All right i didn't even bother searchin for this topic cos im a 100% certain no one has ever had this problem.

 

Im designing a database to take care of tenders for a company, which told me to do it according to an erd they created (which sucks) so i did. By the way im not getting paid for this, its a school thing. So in this database we have a table tenderi and a table statuses which are 5 different statuses a tender can be in like not offering for this tender, offer made, contract made etc. So i made this web page form where u can insert a tender and where u choose a status from a drop down list which is gathered from the table statuses whose primary key, statusID is a name of the status since there are only 5 of em. Status ID is an enum value with 5 possible values. Ok so the mistake im getting is when i try to insert a tender. It tells me:

Could not execute query!

Cannot add or update a child row: a foreign key constraint fails (`dino`.`tenderi`, CONSTRAINT `tenderi_ibfk_4` FOREIGN KEY (`Status`) REFERENCES `status` (`StatusID`))

 

So far so familiar u think right? Well here's where it gets weird, if from the drop down list i choose a value of status called 'offered' it works, it inputs it into the database like everything is hunky dory, but if i choose any other value it gives me that mistake, I even tried deleting the table statuses and building it again, still the same, when i changed the table offered to be something else like tender offered it didnt work, for some reason it works only on the offered but i have no idea why, as its not specified as anywhere in my code which is like this for statuses (i did most of it in phpmyadmin, but i coded some in php but i don't wanna drown u with code so ill give u only the status and tenderi table sql dump)

 

 

CREATE DATABASE /*!32312 IF NOT EXISTS*/`dino` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `dino`;

/*Table structure for table `status` */

DROP TABLE IF EXISTS `status`;

CREATE TABLE `status` (
  `Namjena` varchar(50) DEFAULT NULL,
  `Zapisano` datetime DEFAULT NULL,
  `Promjena` datetime DEFAULT NULL,
  `StatusID` enum('Ponuda napravljena','ne nuditi','isteko rok','ponuda prihvacena','sklopljen ugovor') NOT NULL DEFAULT 'Ponuda napravljena',
  PRIMARY KEY (`StatusID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

/*Data for the table `status` */

insert  into `status`(`Namjena`,`Zapisano`,`Promjena`,`StatusID`) values ('Ponuda napravljena za Tender','2009-06-14 23:11:21','2009-06-15 23:11:23','Ponuda napravljena'),('Na tender se nece nuditi','2009-06-14 23:11:41','2009-06-28 23:11:44','ne nuditi');

/*Table structure for table `tenderi` */

DROP TABLE IF EXISTS `tenderi`;

CREATE TABLE `tenderi` (
  `TenderID` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `RedniBroj` smallint(5) unsigned NOT NULL,
  `BrProtTend` varchar(50) NOT NULL,
  `ImeTendera` varchar(100) NOT NULL,
  `Predmet` varchar(255) DEFAULT NULL,
  `DatumLicitacije` date DEFAULT NULL,
  `PosebniKriteriji` varchar(255) DEFAULT NULL,
  `UsloviPlacanja` varchar(255) DEFAULT NULL,
  `ProcjenaVrijednosti` float DEFAULT NULL,
  `LicitacionaGarancija` float DEFAULT NULL,
  `OpcijaLG` varchar(2) DEFAULT NULL,
  `Valuta` enum('KM','$','EUR') DEFAULT NULL,
  `Paritet` float DEFAULT NULL,
  `Lokacija` varchar(255) DEFAULT NULL,
  `IzvedGaranc` float DEFAULT NULL,
  `AvansnaGarancija` float DEFAULT NULL,
  `KomitentID` smallint(6) DEFAULT NULL,
  `Drzavaid` enum('BIH','USA','HRV','SRB','SLO') DEFAULT NULL,
  `InvestitorID` smallint(6) DEFAULT NULL,
  `NosiocPoslaID` varchar(50) DEFAULT NULL,
  `OstaliUcesnici` varchar(255) DEFAULT NULL,
  `Telefon` varchar(50) DEFAULT NULL,
  `Kontakt` varchar(200) DEFAULT NULL,
  `Zapisano` datetime DEFAULT NULL,
  `Promjena` datetime DEFAULT NULL,
  `RatingTendera` enum('1','2','3','4','5') NOT NULL,
  `Odlozeno` varchar(50) DEFAULT NULL,
  `Status` enum('ponudjen','ne nuditi','isteko rok','ponuda prihvacena','sklopljen ugovor') DEFAULT NULL,
  PRIMARY KEY (`TenderID`),
  UNIQUE KEY `RedniBroj` (`RedniBroj`),
  KEY `NosiocPoslaID` (`NosiocPoslaID`),
  KEY `Valuta` (`Valuta`),
  KEY `FK_tenderi` (`KomitentID`),
  KEY `Status` (`Status`),
  CONSTRAINT `tenderi_ibfk_4` FOREIGN KEY (`Status`) REFERENCES `status` (`StatusID`),
  CONSTRAINT `FK_tenderi` FOREIGN KEY (`KomitentID`) REFERENCES `komitenti` (`KomitentID`),
  CONSTRAINT `tenderi_ibfk_1` FOREIGN KEY (`NosiocPoslaID`) REFERENCES `nosiocposla` (`NosiocPoslaID`),
  CONSTRAINT `tenderi_ibfk_3` FOREIGN KEY (`Valuta`) REFERENCES `kursnalista` (`ValutaID`)
) ENGINE=InnoDB AUTO_INCREMENT=42 DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

/*Data for the table `tenderi` */

 

(the names are in Bosnian but u should get the gist)

[kickstart]

Hi

 

Can't see anything that I would think would cause a problem. However the default value for StatusID on the status table is not one of the enums on the tenderi table, while one of the enums on the tenderi is not valid on the status table.

 

All the best

 

Keith

[dino2dy]

The sql dump sucked it was old and i didnt check it so ignore it, let me tell u a few things, both tables are InnoDB, both are of the same values unlike the sql dump so both enum with the same values, tried makin em both unique or both indexes, tried makin one a primary key and te other an index, all failed, and the most common reason is the foreign key constraint. So after searchin for the past couple of hours on some forums, i used the showInnoDB status thing to get the following message

 

CONSTRAINT `FK_tenderi_status` FOREIGN KEY (`Status`) REFERENCES `statusi` (`StatusID`)

Trying to add in child table, in index `IndexStatus` tuple:

DATA TUPLE: 2 fields;

0: len 1; hex 00; asc ;; 1: len 2; hex 002e; asc .;;

 

But in parent table `dino`.'statusi', in index `IndexStatusID`,

the closest match we can find is record:

PHYSICAL RECORD: n_fields 2; compact format; info bits 0

0: len 1; hex 01; asc ;; 1: len 6; hex 00000000060b; asc ;; 2:

 

So if anyone has any ideas i would love them forever, because i searched everywherer using this error and couldn't find anything, so PLEASE PLEASE PLEASE HELP

[kickstart]

Hi

 

Afraid I have never really used Enums in MySQL. I would prefer to use a lookup table, but from you comments I presume you are not allowed to do that.

 

I presume that there are alerady records on your status table with the various values? Rows must exist for the foriegn key to work (not just a column with those values allowed).

 

Also, my understanding is that for the Enums MySQL stores the number rather than the actual value, and just takes the value when required from the table declaration (could well be wrong). Or it might store both. In either case have them in different orders could potentially cause an issue.

 

All the best

 

Keith

[dino2dy]

Yeh they're in the same order (the enum values im guessing u mean) cos i copied and pasted them from the StatusID column to the Status column. Also the StatusId column has records, its not empty, i created just 2 for testing purposes, one ponudjeni for some reason works, but any other that i create doesn't. Hope someone figures it out.

[fenway]

You probably have to disable the FK constraints durin the import.