Error Code: 1215. Cannot add foreign key constraint

Question

I have two tables and I want there reference between the two.

I tried using the alter table command, but it gives me an error, can anyone help me please?

CREATE TABLE `registos` (
`data_registo` char(10) NOT NULL,
`hora_registo` time NOT NULL,
`idSensor` varchar(8) NOT NULL,
`Temperatura` char(6) DEFAULT NULL,
`Humidade` char(6) DEFAULT NULL,
`pt_orvalho` char(6) DEFAULT NULL,
`idRegisto` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`idRegisto`,`idSensor`,`data_registo`,`hora_registo`),
KEY `fk_registos_sensores1_idx` (`idSensor`),
CONSTRAINT `fk_registos_sensores1` FOREIGN KEY (`idSensor`) REFERENCES `sensores`   (`idSensor`) ON DELETE NO ACTION ON UPDATE NO ACTION
 )




CREATE TABLE `alarmes` (
`idAlarme` int(11) NOT NULL AUTO_INCREMENT,
`descricao_alarme` varchar(45) DEFAULT NULL,
`data_criacao` datetime DEFAULT CURRENT_TIMESTAMP,
`idRegisto` int(11) NOT NULL,
PRIMARY KEY (`idAlarme`,`idRegisto`)
)

ALTER TABLE alarmes

ADD CONSTRAINT FK_alarmes1
FOREIGN KEY (idRegisto) REFERENCES registos(idRegisto)
ON UPDATE CASCADE
ON DELETE CASCADE;

Air · Answer 1 · 2014-03-28T16:38:10.853

2

You have defined idRegisto as a signed integer in one table and an unsigned integer in the other table. Foreign key references can only be made between fields with the same type.

Another problem is that you created the table with the name registos but you are trying to add a key referencing the table registo.

edited Mar 28 '14 at 16:38

answered Mar 28 '14 at 16:02

Air

8,274
2
53
88

I'm sorry, I copy an old script...I edit code, but give me same error :( – strange_098 Mar 28 '14 at 16:10
@user3320956 Post the edit you've made in your question. We're not going to guess what you edited. – Kermit Mar 28 '14 at 16:15
@user3320956 See my edit. It works fine for me with both the field type and table name corrected. – Air Mar 28 '14 at 16:42
You have right..I change code, and tested, but give me the same error :( I don't understand... – strange_098 Mar 28 '14 at 16:59

Error Code: 1215. Cannot add foreign key constraint

1 Answers1