Can I create a Foreign Key between 3 tables?

Question

I have 3 main tables in my database:

Actors, Moviesdata and Seriesdata.

Each film and each series has its own actors, but series and films can share the same actor. How do I create a key Foreign between Actors.Actorid, Moviesdata.Actorid and Seriesdata.Actorid?

Answer 1

It will not be a FK between 3 tables, it will be an independent table (Actors) and two dependent on Actors but independent of each other.

The links will be:

• Actors and MoviesData
• Actors and SeriesData

That is, each of the dependent tables will have a FK with the table Actors

Just create a PK in Actors(which will be in case the ActorID), and in each of the other tables create a field (if no) that will be linked to the Actors.ActorID, for example Actors.ActorID.

So create the FK in each of the tables, in SQL Server the syntax would be this:

ALTER TABLE MoviesData
ADD FOREIGN KEY (ActorID) REFERENCES Actors(ActorID);

Answer 2

Friend depends on the structure you are creating and relationships can change:

In case your structure has an actor per series and movie you can add a foreign key in Movies and series to Actors:

ALTER TABLE MoviesData 
ADD FOREIGN KEY (Actors) REFERENCES Actors(ActorsID);


ALTER TABLE SeriesData
ADD FOREIGN KEY (Actors) REFERENCES Actors(ActorsID);

If your Movies and Seires structure can have more than one Actor then its structure is N:N, many to many. Then 2 ration tables should be created for Moviesdata and Seriesdata:

CREATE TABLE MoviesData_Actors (
         ActorsID   INT UNSIGNED  NOT NULL,
         MoviesDataID  INT UNSIGNED  NOT NULL,
         PRIMARY KEY (ActorsID, MoviesDataID),
         FOREIGN KEY (ActorsID)  REFERENCES Actors(ActorsID),
         FOREIGN KEY (MoviesDataID) REFERENCES MoviesData(MoviesDataID)
)


CREATE TABLE SeriesData_Actors (
         ActorsID   INT UNSIGNED  NOT NULL,
         SeriesDataID  INT UNSIGNED  NOT NULL,
         PRIMARY KEY (ActorsID, SeriesDataID),
         FOREIGN KEY (ActorsID)  REFERENCES Actors(ActorsID),
         FOREIGN KEY (SeriesDataID) REFERENCES SeriesData(SeriesDataID)
)

But if you wanted to create a relationship table between the 3 tables just create a table with the 3 keys and reference them.

CREATE TABLE SeriesData_Actors (
         ActorsID   INT UNSIGNED  NOT NULL,
         SeriesDataID  INT UNSIGNED  NOT NULL,
         MoviesDataID  INT UNSIGNED  NOT NULL,
         PRIMARY KEY (ActorsID, MoviesDataID  ,SeriesDataID),
         FOREIGN KEY (ActorsID)  REFERENCES Actors(ActorsID),
         FOREIGN KEY (MoviesDataID) REFERENCES MoviesData(MoviesDataID),
         FOREIGN KEY (SeriesDataID) REFERENCES SeriesData(SeriesDataID)
)

I hope I helped.