Jump to content

Foreign key constraint is incorrectly formed


micky007

Recommended Posts

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.

Link to comment
Share on other sites

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
);

 

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.