CODEWITHSUNDEEP
mysqlsqlreferential-integrity
Jacques Amar
Jacques Amar

Reputation: 1833

Foreign Key Constraints using multiple fields

I have two InnoDB tables. The first table has a 2 field primary key with both fields validated with referential integrity and all works well. The second table validates against the first using the same two fields:

Table 1:

CREATE TABLE valid_combo(
season_id          CHAR(16)    NOT NULL,
ad_id              INT UNSIGNED  NOT NULL,
PRIMARY KEY (season_id, ad_id)
)ENGINE=INNODB
COMMENT='Parent Table of valid choices'
;

Creating a record

INSERT INTO valid_combo (season_id, ad_id) VALUES ('SEASON', 100);

Table 2:

CREATE TABLE user_combo(
uid               INT UNSIGNED  NOT NULL,
season_id         CHAR(3)     NOT NULL,
ad_id             INT UNSIGNED  NOT NULL,
PRIMARY KEY (uid, season_id, ad_id), 
INDEX IDX_season_ad(season_id, ad_id),
UNIQUE INDEX AK_seasons_users_ads(season_id, uid, ad_id), 
CONSTRAINT Refseason_ads451 FOREIGN KEY (season_id, ad_id)
REFERENCES valid_combo(season_id, ad_id)
)ENGINE=INNODB
COMMENT='Child table with users choices out of valid combos'
;

Somehow, I cannot add a record that should validate.

INSERT INTO user_combo (uid, season_id, ad_id) VALUES (200, 'SEASON', 100);

I get the error :

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`user_combo`, CONSTRAINT `Refseason_ads451` FOREIGN KEY (`season_id`, `ad_id`) REFERENCES `valid_combo` (`season_id`, `ad_id`))

I know and see that the referenced values exist in the parent table. I suspect multiple field primary keys and referential integrity is the culprit. But I could be wrong and not seeing something obvious.

The reason I don't use individual fields for validations into their respective primary table is because the combination has to be valid and existing.

Any thoughts?

Upvotes: 0

Views: 117

Answers (1)

Jacques Amar
Jacques Amar

Reputation: 1833

The fields season_id where not the same size ...

CHAR(16) and CHAR(3).

I don't know how to close the subject ..

Upvotes: 1

Related Questions