Foreign key constraint is incorrectly formed

Databases
1

I get that error in phpmyadmin when trying to crate this table

create table connection_history (
   connection_id INT NOT NULL,
   updated_to_device_input VARCHAR(100) NOT NULL,
   updated_to_device_output VARCHAR(100) NOT NULL,
   updated_to_display CHAR(1) DEFAULT '1',
   updated_to_notes TEXT DEFAULT NULL,
   updated_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   FOREIGN KEY ( connection_id ) REFERENCES connections (connection_id),
   FOREIGN KEY ( updated_to_device_input ) REFERENCES devices (device_id),
   FOREIGN KEY ( updated_to_device_output ) REFERENCES devices (device_id)

);

can I not have three foreign keys in a table?

2

of course you can

please show the definitions of the tables being referenced

3

Two of your foreygn keys are VARCHAR. I think, that is the problem.

4

here are the two tables im referencing

create table connections (
   connection_id INT NOT NULL AUTO_INCREMENT,
   device_input_id INT NOT NULL,
   device_output_id INT NOT NULL,
   cable_type VARCHAR(25) NOT NULL,
   display CHAR(1) DEFAULT '1',
   notes TEXT DEFAULT NULL,
   FOREIGN KEY ( device_input_id ) REFERENCES devices (device_id),
   FOREIGN KEY ( device_output_id ) REFERENCES devices (device_id),
  PRIMARY KEY ( connection_id )
);

and

create table devices (
   device_id INT NOT NULL AUTO_INCREMENT,
   rack_id INT NOT NULL,
   orientation CHAR(1) DEFAULT '1',
   beginning_slot DECIMAL(3,1) NOT NULL,
   ending_slot DECIMAL(3,1) NOT NULL,
   device VARCHAR(100) NOT NULL,
   notes TEXT DEFAULT NULL,
   width INT DEFAULT NULL,
   beginning_x INT DEFAULT NULL,
   display CHAR(1) DEFAULT '1',
   updated_by VARCHAR(25) DEFAULT 'Luke Utnowski',
   updated_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   FOREIGN KEY ( rack_id ) REFERENCES racks (rack_id),
   PRIMARY KEY ( device_id )
);

I think the data types are all INT

5 
create table connection_history (

updated_to_device_input VARCHAR(100) NOT NULL,
updated_to_device_output VARCHAR(100) NOT NULL,

FOREIGN KEY ( updated_to_device_input ) REFERENCES devices (device_id),
FOREIGN KEY ( updated_to_device_output ) REFERENCES devices (device_id)
1 Like
6

well, you musta changed them real quick from VARCHAR(100)

you still getting that foreign key error now?

7

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.