trying to relate two table together

Question

so pretty new to SQL I created 2 tables which I wanted to be related to one another but I'm getting an error "#1215 - Cannot add foreign key constraint" can someone point me to the right direction of this problem?

CREATE TABLE movie(
  id INT(1) NOT NULL AUTO_INCREMENT,
  nearname VARCHAR(25) NOT NULL,
  release_date DATE NOT NULL,
  lang VARCHAR(10) NOT NULL,
  PRIMARY KEY(id),
  CONSTRAINT same_movie FOREIGN KEY(id) REFERENCES movie_cast(movie_id)
);

CREATE TABLE movie_cast(
  movie_id INT(1) NOT NULL AUTO_INCREMENT,
  director_name VARCHAR(20) NOT NULL,
  actor_name VARCHAR(20) NOT NULL,
  actress_name VARCHAR(20) NOT NULL,
  PRIMARY KEY(movie_id),
  CONSTRAINT same_movie FOREIGN KEY(movie_id) REFERENCES movie(id)
);

Seems you're adding the foreign key to the wrong table. Given the table and column names, I'd expect `movie_cast.movie_id` to be a foreign key for `movie.id` — Phil, Jan 21 '18 at 23:01
Is there only one `movie_cast` entry per `movie` entry? If so, this is what is referred to as a [_one-to-one_ relationship](https://en.wikipedia.org/wiki/One-to-one_(data_model)). It's also not very common as the `movie_cast` data could easily be included in the `movie` table but there are some reasons to use one. See https://stackoverflow.com/questions/517417/is-there-ever-a-time-where-using-a-database-11-relationship-makes-sense — Phil, Jan 21 '18 at 23:09
Try to use `VARCHAR(255)` as a default "string" column. MySQL can and will arbitrarily truncate things that don't fit which can lead to catastrophic data loss. — tadman, Jan 21 '18 at 23:13

Gordon Linoff · Answer 1 · 2018-01-21T23:02:30.360

You need to refer to the same column name as the primary key. In this case, it is called id:

CONSTRAINT same_movie FOREIGN KEY(movie_id) REFERENCES movie_cast(id)

Of course, your DDL doesn't define movie_cast. So, I am guessing the second table should be something like:

CREATE TABLE movie_cast (
  id INT NOT NULL AUTO_INCREMENT,
  movie_id int not null,
  cast_name varchar(255)
  PRIMARY KEY(id),
  CONSTRAINT fk_movie_cast_movie FOREIGN KEY(movie_id) REFERENCES movie(movie_id)
);

trying to relate two table together

1 Answers1