Jump to content
micky007

Foreign key constraint is incorrectly formed

Recommended Posts

Posted (edited)

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 by micky007

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
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
);

 

  • Thanks 1

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

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.