• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Not able to add two foreign keys in one table in MySQL

 
Greenhorn
Posts: 27
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I have to add two foregn keys from two different table into on single but it showing error that "Cannot add foreign Key Constraint".....Please help me

FIRST TABLE

CREATE TABLE Train_Details
(
Train_ID int PRIMARY KEY not null,
Train_Name varchar(60) not null,
Train_Type varchar(25) not null
);

SECOND TABLE

CREATE TABLE Passenger_Details
(
Passenger_ID int unsigned auto_increment primary key,
Full_Name varchar(25) not null,
Address varchar(100) not null,
Age int unsigned not null,
Gender varchar(10) not null,
DOB date not null,
Phone long not null,
Train_ID int,
Foreign key (Train_ID) references Train_Details(Train_ID)
);


THIRD TABLE

CREATE TABLE Booking_Passenger_Details
(
Serial_No int unsigned auto_increment primary key,
Train_ID int unsigned not null,
Passenger_ID int unsigned not null,
Seat_No int unsigned not null,
Class varchar(25) not null,
FOREIGN KEY (Passenger_ID) REFERENCES Passenger_Details(Passenger_ID),
FOREIGN KEY (Train_ID) REFERENCES Train_Details(Train_ID)
)Engine=InnoDB;
 
Marshal
Posts: 4501
572
VSCode Eclipse IDE TypeScript Redhat MicroProfile Quarkus Java Linux
  • Likes 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You have the data type for Train_ID in one table as int, but as int unsigned in another. You will need to make the types the same to create the foreign key relationship.

Also, it seems odd that you have Train_ID as a column in the Passenger_Details table. I would think that the relationship between and passenger and a train should be through a booking, unless the passenger is only permitted to ride on a single train in the system.
 
Zeona Neo
Greenhorn
Posts: 27
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thank You So Much Sir....

My Problem is Solved.....
It is working finely....
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic