Error Code 1215 foreign key in mysql

Question

Error Code 1215 foreign key in mysql

Navigation

#1 by (0 votes)

1

I'm having problems with three FKs. It is an N: N relationship that points to its PK. All have the same type (SMALLINT)

This is the table where I have to put those FK

CREATE TABLE IF NOT EXISTS result(
municipalityID SMALLINT,
districtID     SMALLINT,
sectionID      SMALLINT,
tableID        SMALLINT,
partyID        SMALLINT,
provinceID     SMALLINT, 
votes          INTEGER,

Table that has the stations and I want them to be FK.

introducir el código aquí
CREATE TABLE IF NOT EXISTS  tableElection ( 
municipalityID  SMALLINT,
districtID      SMALLINT,
sectionID         SMALLINT,
tablesID        SMALLINT,
possibleVotes   INTEGER,
totalVotes      INTEGER,
nullVotes       INTEGER,
blankVotes      INTEGER,
pollingstationID SMALLINT NOT NULL,
CONSTRAINT pk_tableElection PRIMARY KEY 
(municipalityID,districtID,sectionID,tablesID), 
CONSTRAINT fk_tableElection_pollingstation FOREIGN KEY 
(pollingstationID) 
                         REFERENCES pollingstation(ID) 
                               ON DELETE CASCADE ON UPDATE CASCADE

);

I've only had problems with those three FKs. I am adding them with this sentence

ALTER TABLE result
ADD FOREIGN KEY (districtID) REFERENCES tableElection (districtID);


ALTER TABLE result
ADD FOREIGN KEY (sectionID) REFERENCES tableElection (ID);


ALTER TABLE result
ADD FOREIGN KEY (tableID) REFERENCES tableElection(tableID);

And I get an "ERROR CODE 1215 CAN NOT ADD FOREIGN KEY CONSTRAINT". Why do I get this error if I have the table created and the data type matches?

Thanks !!!

sql

asked by kitkat 07.03.2018 в 17:12

source

1 answer

Send several "entry" fields in a signal_callback Error migrating from SQLite3 to PostGreSQL: No operator matches the given name and argument type (s). You might need to add explicit type casts

score 0 · Accepted Answer

To be able to create your tables, you should ideally do so in the following order:

CREATE TABLE pollingstation(
    id SMALLINT NOT NULL,
    name VARCHAR(100)
);

CREATE TABLE IF NOT EXISTS result(
municipalityID SMALLINT,
districtID     SMALLINT,
sectionID      SMALLINT,
tableID        SMALLINT,
partyID        SMALLINT,
provinceID     SMALLINT, 
votes          INTEGER
);

CREATE TABLE IF NOT EXISTS  tableElection ( 
municipalityID  SMALLINT,
districtID      SMALLINT,
sectionID         SMALLINT,
tablesID        SMALLINT,
possibleVotes   INTEGER,
totalVotes      INTEGER,
nullVotes       INTEGER,
blankVotes      INTEGER,
pollingstationID SMALLINT NOT NULL,
CONSTRAINT pk_tableElection PRIMARY KEY (municipalityID,districtID,sectionID,tablesID), 
CONSTRAINT fk_tableElection_pollingstation FOREIGN KEY (pollingstationID) REFERENCES pollingstation(ID) ON DELETE CASCADE ON UPDATE CASCADE
);

I just added an example table at the beginning, to complement the exercise and in this way you avoid the commands of ALTER TABLE