Foreign key constraint is incorrectly formed

[micky007]

Hi everyone,

I keep getting the error (see title of this post) on the first ALTER query but i cant seem to work out why. Below is the SQL and the Error.

SQL:

CREATE TABLE `affiliates` 
(
	`affiliate_id` int AUTO_INCREMENT,
	`email` varchar(255),
	`password` varchar(255),
	`first_name` varchar(255),
	`last_name` varchar(255),
	`company` varchar(255),
	`vat_id` varchar(255),
	`address_1` varchar(255),
	`address_2` varchar(255),
	`city` varchar(255),
	`county` varchar(255),
	`postcode` varchar(255),
	`country` varchar(255),
	`telephone` varchar(255),
	`mobile` varchar(255),
	`ip` varchar(255),
	`status` tinyint,
	`signup_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY(affiliate_id)
);

CREATE TABLE `affiliate_deals` 
(
	`affiliate_deal_id` int AUTO_INCREMENT,
	`revenue_share_percentage` decimal,
	`ftd_amount` decimal,
	`cpa` decimal,
	`cpc` decimal,
	`start_date` timestamp NULL DEFAULT NULL,
	`end_date` timestamp NULL DEFAULT NULL,
	`added_by` varchar(255),
PRIMARY KEY(affiliate_deal_id)
);

CREATE TABLE `deal_type` 
(
	`deal_type_id` int AUTO_INCREMENT,
	`affiliate_id` int,
	`program_id` int,
	`affiliate_deal_id` int,
PRIMARY KEY(deal_type_id,affiliate_id,program_id,affiliate_deal_id)
);

CREATE TABLE `clicks` 
(
	`click_id` int AUTO_INCREMENT,
	`affiliate_id` int,
	`program_id` int,
	`clicks_data_id` int,
	`creative_id` int,
PRIMARY KEY(click_id,affiliate_id,program_id,clicks_data_id,creative_id)
);

CREATE TABLE `clicks_data` 
(
	`click_data_id` int AUTO_INCREMENT,
	`sub_id` varchar(255),
	`referer` varchar(255),
	`ip` varchar(255),
	`timestamp` timestamp,
	`ip_region` varchar(255),
	`ip_town` varchar(255),
	`ip_isp` varchar(255),
	`ip_long` varchar(255),
	`ip_lat` varchar(255),
	`ip_country` varchar(255),
	`ip_county` varchar(255),
PRIMARY KEY(click_data_id)
);

CREATE TABLE `creatives` 
(
	`creative_id` int AUTO_INCREMENT,
	`type` int,
	`size` varchar(255),
	`code` text,
	`banner_url` text,
	`status` tinyint,
PRIMARY KEY(creative_id)
);

CREATE TABLE `players` 
(
	`player_id` int AUTO_INCREMENT,
	`deal_id` int,
	`creative_id` int,
	`click_id` int,
	`sub_id` varchar(255),
	`ad_id` int,
	`status` int,
	`username` varchar(255),
	`user_id` int,
	`signup_date` timestamp NULL DEFAULT NULL,
	`first_deposit_amount` int,
	`ftd_matched` int,
	`ftd_matched_Date` timestamp NULL DEFAULT NULL,
PRIMARY KEY(player_id,deal_id,creative_id,click_id)
);

CREATE TABLE `players_stats` 
(
	`player_stats_id` int AUTO_INCREMENT,
	`player_id` int,
	`deposits` decimal(10,2),
	`withdrawals` decimal(10,2),
	`bonus` decimal(10,2),
	`tax` decimal(10,2),
	`house_earnings` decimal(10,2),
	`stats_date` date NULL DEFAULT NULL,
PRIMARY KEY(player_stats_id,player_id)
);

CREATE TABLE `programs` 
(
	`program_id` int AUTO_INCREMENT,
	`name` varchar(255),
	`url` varchar(255),
	`aff_url` varchar(255),
	`status` tinyint,
	`description` varchar(255),
	`image` varchar(255),
PRIMARY KEY(program_id)
);

ALTER TABLE `affiliates` ADD FOREIGN KEY (`affiliate_id`) REFERENCES `deal_type` (`affiliate_id`);

ALTER TABLE `programs` ADD FOREIGN KEY (`program_id`) REFERENCES `deal_type` (`program_id`);

ALTER TABLE `deal_type` ADD FOREIGN KEY (`affiliate_deal_id`) REFERENCES `affiliate_deals` (`affiliate_deal_id`);

ALTER TABLE `players` ADD FOREIGN KEY (`player_id`) REFERENCES `players_stats` (`player_id`);

ALTER TABLE `players` ADD FOREIGN KEY (`deal_id`) REFERENCES `deal_type` (`deal_type_id`);

ALTER TABLE `players` ADD FOREIGN KEY (`creative_id`) REFERENCES `creatives` (`creative_id`);

ALTER TABLE `players` ADD FOREIGN KEY (`click_id`) REFERENCES `clicks` (`click_id`);

ALTER TABLE `clicks` ADD FOREIGN KEY (`clicks_data_id`) REFERENCES `clicks_data` (`click_data_id`);

ALTER TABLE `clicks` ADD FOREIGN KEY (`creative_id`) REFERENCES `creatives` (`creative_id`);

ALTER TABLE `clicks` ADD FOREIGN KEY (`program_id`) REFERENCES `programs` (`program_id`);

ALTER TABLE `clicks` ADD FOREIGN KEY (`affiliate_id`) REFERENCES `affiliates` (`affiliate_id`);

Error:

Quote

Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.

I'm not sure i fully understand what that means so maybe the answer is right there. But as I'm still learning i don't understand it.

Edited March 4, 2019 by micky007

[micky007]

I've gone ahead and Indexed the Primary Keys too and i still get the error.

[Barand]

You are building your foreign keys the wrong way round, EG

ALTER TABLE `programs` ADD FOREIGN KEY (`program_id`) REFERENCES `deal_type` (`program_id`);

should be
ALTER TABLE `deal_type` ADD FOREIGN KEY (`program_id`) REFERENCES `programs` (`program_id`);

program_id is the primary key of the programs table. When it appears in another table (such as deal_type) it is, therefore, a foreign key linking back the programs record.

 

Also

CREATE TABLE `deal_type` 
(
	`deal_type_id` int AUTO_INCREMENT,
	`affiliate_id` int,
	`program_id` int,
	`affiliate_deal_id` int,
PRIMARY KEY(deal_type_id,affiliate_id,program_id,affiliate_deal_id)  -- really? PK should be deal_type_id
);