Foreign Key failure

Databases
1

Hi guys,

I’m having problems setting up a foreign key between two fields. I have to do it via PHPMyAdmin unfortunately, and it’s saying that the data types are different. In the past I’ve found some weird issues like needing the key name to be uppercase, and sometimes I’ve needed to create a specific key for the field, even if it’s already the PK, so I’ve tried all of these and it’s still not working. So anyway, I’ve done a SHOW CREATE TABLES on both tables and I can’t see what the difference is. Apart from one being an auto-incrementing PK, they’re both TINYINT(3) UNSIGNED NOT NULL. Any other ideas?

CREATE TABLE `departments` (
 `dept_id` tinyint(3) unsigned NOT NULL auto_increment COMMENT 'Department ID',
 `dept_name` varchar(255) NOT NULL COMMENT 'Department Name',
 PRIMARY KEY  (`dept_id`),
 KEY `FK_DEPT` (`dept_id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 COMMENT='Company Departments'

CREATE TABLE `users_contact` (
 `cont_id` int(10) unsigned NOT NULL auto_increment COMMENT 'Contact ID',
 `auid` int(10) unsigned NOT NULL COMMENT 'Admin User ID',
 `dept_id` tinyint(3) unsigned NOT NULL COMMENT 'Department',
 `int_no` smallint(3) unsigned NOT NULL COMMENT 'Internal Phone Number',
 `ext_no` varchar(16) NOT NULL COMMENT 'External Phone Number',
 PRIMARY KEY  (`cont_id`),
 KEY `FK_DEPT` (`dept_id`),
 KEY `FK_AUID` (`auid`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT='Contact details for users'

I’m trying to apply the FK to the dept_id fields

2

i dunno, i don’t use phpmyadmin

what happened when you tested it?

3

Just tested it and it appears to have worked fine referencing

4

why can’t you take the ALTER statement and run it in the SQL tab of phpmyadmin?

5

Would that be any different to it running the query itself? Is it doing some checking of it’s own if I do it through the relationship page?

6

Hmm, figured as much. Takes me back to my “who uses PMA by choice” thread. Gah, hate it! If only they’d let me connect with HeidiSQL or something similar. Oh well, thanks anyway. FKs are out :frowning:

7

worked fine when i tested your code and then ran this –

ALTER TABLE `users_contact`
ADD FOREIGN KEY ( dept_id ) 
  REFERENCES departments ( dept_id )