PostgreSQL trying to create a relation between 2 tables

Question

Heyo everyone, im new at PostgreSQL and im trying to make a simple movies database, but I don't know how to or if it is possible to create a relation with 2 tables.

create table Movie(
    id_movie int PRIMARY KEY,
    title char(50),
    release date,
    url char(100),
    local char(50),
    resume char(200)
);

create table Categories( 
    category char(50),
    id_movie int,
    foreign key (id_film) references Movie(id_movie) on delete restrict
);

I wanted to make each movie associated to one or more categories, but with my code, all movies have all categories. Do anyone know if that is possible?

sticky bit · Accepted Answer · 2019-12-14 17:56:12Z

That would typically be modeled by third table. That table has an ID of a movie and an id of a category per row. If and only if a movie is in a category, there is a row with the movie's ID and the category's ID in it.

CREATE TABLE movie
             (id_movie integer,
              title char(50),
              release date,
              url char(100),
              local char(50),
              resume char(200),
              PRIMARY KEY (id_movie));

CREATE TABLE category
             (id_category integer,
              category char(50));

CREATE TBALE movie_category
             (id_movie integer,
              id_category integer,
              PRIMARY KEY (id_movie,
                           id_category),
              FOREIGN KEY (id_movie)
                          REFERENCES movie
                                     (id_movie),
              FOREIGN KEY (id_category)
                          REFERENCES category
                                     (id_category));

Belayer · Accepted Answer · 2019-12-14 19:53:09Z

@stickybit is correct if you want to allow a movie to have multiple categories. And that is not unreasonable. But if that is not the case and you want to ensure a movie is in only 1 category, then your fk structure is reversed. It's the difference between a M:M relationship and a 1:M relationship.

create table categories( 
       category_id  serial 
     , category     character varying(50) 
     , constraint   categories_pk primary key (category_id) 
     , constraint   category_uk unique(category )  
);


create table movies(
       movie_id     serial
     , category_id  int       
     , title        character varying(50)
     , release_dt   date
     , url          character varying(100)
     , _local       character varying(50)
     , resume       character varying(200)
     , constraint   movies_pk primary key(movie_id)   
     , constraint   movies_uk unique(title)
     , constraint   movies2category_fk
                    foreign key (category_id)
                    references categories(category_id)          
);

A couple other just random notes:

Don't use names "local" and/or "release". Although Postgres does not consider them key words the SQL standard does, at least according to Postgres Appendix C. Or any other listed values, they can become reserved in any future any time.
Use character varying instead of char. Char is fixed length so for title char(50) always requires 50 characters, even for the movie "W". Although Postgres makes 'allowances' for trailing spaces, most of the time.
You might want to get into the habit on naming constraints, espically if you work (will work) with other DBMS. Postgres does a pretty good job generating names, others not so much. You'll appreciate taking the extra time whin you have to solve an issue with a name constarint like SYS_C0010676.

Collectives™ on Stack Overflow

PostgreSQL trying to create a relation between 2 tables

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related