INT Foreign Key - weird input issue

Databases
1

Trying to create a 1:M cart : cart_items table structure:



CREATE  TABLE IF NOT EXISTS `carts` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `token` CHAR(32) NOT NULL ,
  `date_created` DATETIME NOT NULL ,
  `date_modified` DATETIME NOT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;


CREATE  TABLE `cart_items` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `cart_id` INT UNSIGNED NOT NULL ,
  `date_added` DATETIME NOT NULL ,
  `code` CHAR(19) NOT NULL ,
  `qty` MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 ,
  PRIMARY KEY (`id`) ,
  INDEX `the_cart` (`cart_id` ASC) ,
  CONSTRAINT `the_cart`
    FOREIGN KEY (`cart_id` )
    REFERENCES `carts` (`id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

This works, except that when I go to insert a row into cart_items in PhpMyAdmin the interface is weird. Instead of the cart_id field being a normal text box to enter an id the field is a select box (see this screenshot)

The parent table has sample rows with ids of 25 and 31.

Is this a PhpMyAdmin bug?
Using MySQL 5.1.41 community on Windows. The same thing happens on the live server (5.0.91-community on Linux)

2

It’s not that weird, since the foreign key field can only contain values that already exist in the other table. I have no idea if you can disable that select box in PHPMyAdmin if you prefer to enter the value manually and get an error if it doesn’t exist in the other table.

3

yeah, but the parent table has rows with ids of 25 and 31 only (there are only two rows). The select box has 3 empty options and 25 -, 31 -, - 25 and -31.

I think it’s a PhpMyAdmin bug.

4

i think it’s a shame that so many people start out with this tool

have you ever tried a different one?

5

well locally I use MySQL Query Browser, but it’s kind of a different purpose as I’m writing queries into it, no GUI for data entry as such.

Had a quick play with SQLBuddy, but nothing in depth.

Do you recommend one in particular?

6

heidisql

:slight_smile:

7

Thanks, will check it out.