0

So I've got Table ActorInMovies, which has 3 foreign keys.

CREATE TABLE ActorInMovie(
    ID_ROLE bigserial REFERENCES Role(ID_ROLE) ON DELETE CASCADE,
    ID_ACTOR bigserial REFERENCES Actor(ID_Actor) ON DELETE CASCADE,
    ID_MOVIE bigserial REFERENCES Movie(ID_Movie) ON DELETE CASCADE,
    CONSTRAINT ActorInMovie_pk PRIMARY KEY (ID_ROLE));

I assumed that when I try to insert something like:

INSERT INTO ActorInMovie (ID_ROLE, ID_ACTOR) values (1,1);

that it would result in an error as ID_MOVIE was not specified (null I supposed).. but it automatically starts assigning indexes staring from 1.

What am I doing wrong? As written here, I thought that "PostgreSQL automatically creates indexes on primary keys and unique constraints, but not on the referencing side of foreign key relationships."

Community
  • 1
  • 1
Kuba Spatny
  • 26,618
  • 9
  • 40
  • 63

2 Answers2

7

I have a very hard time imagining a use case where a serial(or bigserial) column references another column. It's usually the other way round: the serial column should go on the other end of the foreign key constraint.

I have an equally hard time imagining a design where a movie_id needs to be bigint instead of just int. There aren't nearly enough movies on this planet.

Also, there is a good chance, a column called movie_id in a table called actor_in_movie should be defined as NOT NULL.

In short: I doubt your design flies at all. Maybe something like:

CREATE TABLE actor (actor_id serial PRIMARY KEY, actor text, ...);
CREATE TABLE movie (movie_id serial PRIMARY KEY, movie text, ...);
CREATE TABLE actor_in_movie(
   role_id  serial PRIMARY KEY
  ,actor_id int NOT NULL REFERENCES actor(actor_id) ON DELETE CASCADE
  ,movie_id int NOT NULL REFERENCES movie(movie_id) ON DELETE CASCADE
);

A NOT NULL constraint is redundant, while the column is included in the primary key.

You probably want indices on actor_id and on movie_id in actor_in_movie.

More details:

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • I think you just hit the nail on the head! I just started with databases and thought that `bigserial` is a data type and conversion to `int` was not possible. What you wrote is exactly what I was trying to achieve. Fortunately this is just scholar example, so no harm done. Thank you! – Kuba Spatny Nov 04 '13 at 21:24
  • 1
    @KubaSpatny: You may be interested in this closely related answer with a lot more explanation and links: http://stackoverflow.com/questions/9789736/how-to-implement-a-many-to-many-relationship-in-postgresql/9790225#9790225 – Erwin Brandstetter Nov 04 '13 at 21:28
3

This is simply bigserial working exactly as advertised. It has nothing to do with the foreign key constraint, or with an index.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
jjanes
  • 37,812
  • 5
  • 27
  • 34