How do I add a foreign key to a table in Sequel Pro?

Question

I am trying to add a foreign key to a table in Sequel Pro (using the UI).

I have two tables: "titles" and "categories" as below:

CREATE TABLE `titles` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `title` tinytext NOT NULL,
  `category` varchar(256) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `category` (
  `key` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(256) NOT NULL DEFAULT '',
  PRIMARY KEY (`key`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

I want to create a foreign key, but nothing I try works.

The category table should be a simple lookup table. I want to assign each title a category from about 6 - 8 different choices.

Originally I had the category fields as tinytext, but I would get the error:

"MySQL Error 1170 (42000): BLOB/TEXT Column Used in Key Specification Without a Key Length".

Searched here and discovered you can't use text field that way, so I switched to Varchar and added a length of 256. Now I get:

MySQL said: Can't create table 'lit.#sql-2bf3_2' (errno: 150).

How can I create a foreign key for my table?

In Access this is pretty easily done. Somehow Access associates the unique key in the table with the lookup, but then hides the key and shows you the text field instead. How can I get a similar result with Sequel Pro and MySQL?

EDIT:

So, to clarify this is where I'm at right now. I've added an index on the category field in the titles table (first picture).

I've changed the "key" field in the category table to CategoryID (second picture).

However, I still can't seem to create the relationship between the two tables. I get the same error

Answer 1

As category will be your lookup table off of titles, you'd need to create an index on category which would refer to the foreign key. They would both need to be the same datatype (usually an INT, though sometimes you could use a CHAR(2) variable in some cases, but usually not necessary). Since you only expect 6-8 categories, I'd make it INT(1) (or may be INT(2) to be safe).

In this case, you would need to create something like categoryId which would first need to be indexed, then connect to the foreign key on categorywhich does not appear to exist; I'm not sure you want to use a term like key. Why not just make categoryId the primary key on category? this way when you create the foreign key on titles with the same name, it should link up fine.

Edit:

To clarify a little, after you've created categoryID on category you can do this under titles

ALTER TABLE Orders
ADD CONSTRAINT fk_categoryID
FOREIGN KEY (`categoryId`) REFERENCES `category`(`categoryId`)

Edit:

Here's a modification using your original layout. this should work for you:

CREATE TABLE IF NOT EXISTS `category` (
  `key` int(2) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(256) NOT NULL DEFAULT '',
  PRIMARY KEY (`key`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;



CREATE TABLE IF NOT EXISTS `titles` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `title` tinytext NOT NULL,
  `categoryID` int(2) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `categoryID` (`categoryID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


ALTER TABLE `titles`
  ADD CONSTRAINT `titles_ibfk_1` FOREIGN KEY (`categoryID`) REFERENCES `category` (`key`);