CODEWITHSUNDEEP
sqlpostgresql
chazefate
chazefate

Reputation: 822

FOREIGN KEY in CREATE NEW TABLE AS in SQL

I am following https://www.w3schools.com/sql/sql_create_table.asp to create new table AS and my task is to add foreign key into it. It fails and gives syntax error while I try to do it.

CREATE TABLE movie_genre AS 
SELECT movie.movie_id, movie.genres
FROM movie
FOREIGN KEY (movie_id) REFERENCES movie(movie_id);

There is no guide how to put it when using AS

Upvotes: 0

Views: 113

Answers (2)

chazefate
chazefate

Reputation: 822

Correct way to make this is to add foreign key afterwards.

CREATE TABLE movie_genre
 AS (SELECT movie.movie_id, movie.genres
    FROM movie
    );

ALTER TABLE movie_genre
  ADD FOREIGN KEY (movie_id)
    REFERENCES movie(movie_id);

Upvotes: 1

przemo_li
przemo_li

Reputation: 4053

CREATE TABLE [...] FROM do not support any constraints/foreign keys.

But do not worry. You can issue follow up ALTER TABLE to add necessary constraints/foreign keys.

Upvotes: 1

Related Questions