SQL: Primary and foreign key

Question

I am creating a table that has two values that are both primary and foreign at the same time. I do not know how to create them in SQL. Here is what I did but I get the error, there must only be one primary key. what is the correct way?

  CREATE TABLE movie_director(
    director_id CHAR(8)
    constraint pk_director_id_movie_director
    PRIMARY KEY
    constraint fk_director_id_movie_director
    REFERENCES director,
    movie_id VARCHAR(30)
    constraint pk_movie_id_movie_director
    PRIMARY KEY
    constraint fk_movie_id_movie_director
    REFERENCES movie





)

Answer 1

What you seem to be looking for is a compound primary key. Change your table definition to something like the following:

CREATE TABLE movie_director(
  director_id CHAR(8)
    constraint fk_director_id_movie_director
    REFERENCES director,
  movie_id VARCHAR(30)
    constraint fk_movie_id_movie_director
    REFERENCES movie,
  CONSTRAINT PK_MOVIE_DIRECTOR
    PRIMARY KEY (DIRECTOR_ID, MOVIE_ID));

Answer 2

Your error seems pretty clear. There can only be one PRIMARY KEY on a table.

You need to create a COMPOUND PRIMARY KEY which consists of two columns (director_id,movie_id).

From wikipedia:

In database design, a compound key is a key that consists of two or more attributes that uniquely identify an entity occurrence.