Can't create table 'student.#sql-f40_3' (errno: 150)-CodePudding

Table 1

create table personal(

id int not null auto_increment unique,
name char(20) not null,
age int not null,
city varchar(20) not null default 'Delhi'
);
insert into personal(name,age,city) values
('anubhav',22,'delhi'),
('rohit',24,'agra');

Table 2

create table applications(
 app_id int(5) not null auto_increment unique,
 city varchar(10) not null default 'Delhi'
);
insert into applications(city) values
 ('kolkata'),
 ('mumbai'),
 ('mumbai'),
 ('delhi'),
 ('agra'),
 ('agra');

Then i apply foreign key here with the help of Alter command-

alter table personal add foreign key(city) references applications(app_id)

but i am getting an error: ERROR 1005 (HY000): Can't create table 'student.#sql-f40_3' (errno: 150)

CodePudding user response：

MySQL specifies:

Conditions and Restrictions

1.Corresponding columns in the foreign key and the referenced key must have similar data types. The size and sign of fixed precision types such as INTEGER and DECIMAL must be the same. The length of string types need not be the same. For nonbinary (character) string columns, the character set and collation must be the same.

2.MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. This index might be silently dropped later if you create another index that can be used to enforce the foreign key constraint. index_name, if given, is used as described previously.

The data type must be the same.

You could do:

alter table personal add foreign key(city) references applications(city)

But, the columns on both tables should be indexed. See here

CodePudding user response：

you desing in not normalized

your personal table should only reference the id.

City name in the applications should be unique, so i added it in the create table, there is no need for two or more delhis in a table(see normalisation)

If you really want to use in personal the city name, you must like i already made refernece the coty name of appcations or define a KEY for that column.

Further the datatyoes of the columns must always be the saem in both table for the foreign key

create table personal(

id int not null auto_increment unique,
name char(20) not null,
age int not null,
city int not null default 0
);

create table applications(
 app_id int not null auto_increment primary key,
 city varchar(10) not null unique default 'Delhi'
);

alter table personal add foreign key(city) references applications(app_id)