locked
Trouble adding foriegn keys RRS feed

  • Question

  • I am trying to add a foriegn key to a table through the wizard in the sql server diagram tools. I select the foriegn key table which is film_actors_lookup. The film_actors_lookup table has 2 fields: ActorID and FilmID. Both are primary keys for the film_actors_lookup table. The Film table has a FilmID primary key. The Actor table has an ActorID as a primry key. When I go to add a foriegn key relationship between the Actors table and the film_actors_lookup table, I get the error that both tables must have the same number of fields. Why is this not working?
    Monday, May 19, 2014 12:14 PM

Answers

  • Did you try it from t-sql code and see if it works?

    ALTER TABLE film_actors_lookup ADD CONSTRAINT FK_Actors_Lookup_Actor_ActorID FOREIGN KEY (ActorID) REFERENCES Actor (ActorID)


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by Sofiya Li Monday, May 26, 2014 2:08 AM
    Monday, May 19, 2014 12:40 PM
    Answerer
  • Please post the error message.

    Both ActorID and FilmID needs to be PRIMARY KEY in respective tables.

    You can see it with below script:

    USE [TestDatabase]
    GO
    
    CREATE TABLE [dbo].[film_actors_lookup] ([FilmID] INT, [ActorID] INT)
    GO
    
    CREATE TABLE [dbo].[actors] ([ActorID] INT PRIMARY KEY)
    GO
    
    CREATE TABLE [dbo].[films] ([FilmID] INT PRIMARY KEY)
    GO
    
    /* Add Foreign Key - ActorID */
    ALTER TABLE [dbo].[film_actors_lookup] ADD CONSTRAINT [FK_Actors_Lookup_Actors_ActorID] FOREIGN KEY ([ActorID]) REFERENCES [dbo].[Actors] ([ActorID])
    GO
    
    /* Add Foreign Key - FilmID */
    ALTER TABLE [dbo].[film_actors_lookup] ADD CONSTRAINT [FK_Actors_Lookup_Films_FilmID] FOREIGN KEY ([FilmID]) REFERENCES [dbo].[Films] ([FilmID])
    GO


    - Vishal

    SqlAndMe.com

    • Proposed as answer by Sofiya Li Tuesday, May 20, 2014 7:44 AM
    • Marked as answer by Sofiya Li Monday, May 26, 2014 2:08 AM
    Tuesday, May 20, 2014 3:41 AM

All replies

  • Did you try it from t-sql code and see if it works?

    ALTER TABLE film_actors_lookup ADD CONSTRAINT FK_Actors_Lookup_Actor_ActorID FOREIGN KEY (ActorID) REFERENCES Actor (ActorID)


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by Sofiya Li Monday, May 26, 2014 2:08 AM
    Monday, May 19, 2014 12:40 PM
    Answerer
  • Hi,

    You need to create two FOREIGN KEYS here.

    I have never used Diagram Tools, but try creating both KEYS separately in diagram, that should work. (1 for film_actor_lookup.FilmID to FilmTable.FilmID and 2 for film_actor_lookup.ActorID tot ActorsTable.ActorID)

    You can also create using T-SQL as mentioned by @Visakh16.


    - Vishal

    SqlAndMe.com

    Monday, May 19, 2014 1:00 PM
  • The T-SQL code worked for the first foreign key.  When I tried to add the second foriegn key  to the lookup table, ?I got a weird remote debugging error message. I am on the machine where the sql server runs. it is my home machine.

    the code that errored out is as follows:

    ALTER TABLE film_actors_lookup ADD CONSTRAINT FK_Actors_Lookup_FilmID 
    FOREIGN KEY (FilmID) REFERENCES Films (Film_ID)

    Monday, May 19, 2014 2:29 PM
  • The T-SQL code worked for the first foreign key.  When I tried to add the second foriegn key  to the lookup table, ?I got a weird remote debugging error message. I am on the machine where the sql server runs. it is my home machine.

    the code that errored out is as follows:

    ALTER TABLE film_actors_lookup ADD CONSTRAINT FK_Actors_Lookup_FilmID 
    FOREIGN KEY (FilmID) REFERENCES Films (Film_ID)

    Can you post the error message? The query looks fine to me

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Monday, May 19, 2014 2:44 PM
    Answerer
  • Please post the error message.

    Both ActorID and FilmID needs to be PRIMARY KEY in respective tables.

    You can see it with below script:

    USE [TestDatabase]
    GO
    
    CREATE TABLE [dbo].[film_actors_lookup] ([FilmID] INT, [ActorID] INT)
    GO
    
    CREATE TABLE [dbo].[actors] ([ActorID] INT PRIMARY KEY)
    GO
    
    CREATE TABLE [dbo].[films] ([FilmID] INT PRIMARY KEY)
    GO
    
    /* Add Foreign Key - ActorID */
    ALTER TABLE [dbo].[film_actors_lookup] ADD CONSTRAINT [FK_Actors_Lookup_Actors_ActorID] FOREIGN KEY ([ActorID]) REFERENCES [dbo].[Actors] ([ActorID])
    GO
    
    /* Add Foreign Key - FilmID */
    ALTER TABLE [dbo].[film_actors_lookup] ADD CONSTRAINT [FK_Actors_Lookup_Films_FilmID] FOREIGN KEY ([FilmID]) REFERENCES [dbo].[Films] ([FilmID])
    GO


    - Vishal

    SqlAndMe.com

    • Proposed as answer by Sofiya Li Tuesday, May 20, 2014 7:44 AM
    • Marked as answer by Sofiya Li Monday, May 26, 2014 2:08 AM
    Tuesday, May 20, 2014 3:41 AM