Search code examples
mysqlsqlconstraintsforeign-key-relationship

MySql error 1215: Cannot add foreign key constraint when create table

I altered the table Company, modifying the column Comp_discountrate. Then, I create table TQuote, and there is an error (1215) due to Comp_discountrate as a foreign key constraint. Without the last line of the table defininition, it works well. How can I handle this?

Alter table Company
modify Comp_discountrate double(2,2) not null default '0.4';

create table TQuote(
    Quo_id varchar(7) not null,
    Mem_id varchar(7) not null,
    Quo_date DATETIME NOT NULL DEFAULT NOW(),
    Comp_discountrate double(2,2) not null default '0.4',
    primary key(Quo_id, Mem_id),
    foreign key(Mem_id) references Tuser(Mem_id) on delete cascade,
    foreign key(Comp_discountrate) references Company(Comp_discountrate) on update cascade
)
Solution

  • You need a unique index on Company(Comp_discountrate)