Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2011
    Posts
    4

    Question Unanswered: Error: Multiple columns - ForeignKey Constraint - Msg1776

    Hi everyone!

    I am struggeling with this problem for a few days now. Unfortunately the table and column-names are in German, but I guess this shouldn't be a problem.

    Basically I have around 20 tables that are all connected in some way. But when I want to create the foreign keys for the following Tables I end up getting the msg 1776 by MS SQL Server 2008.

    The first table is a person who works since a specific date in a company (see primary key). The second table stores projects and these projects are in co-operation with a person who works at a company.

    So what I thought was to connect these with a foreign key covering the whole primary key of the first table. But somehow I get an error. I hope you can figure out what the problem is.
    Here's the sql code:


    CREATE TABLE Firmenbetreuer (
    FirmenbetreuerID INT NOT NULL ,
    FirmenID INT NOT NULL ,
    gueltigAb DATE NOT NULL ,
    Abteilung VARCHAR(45) NOT NULL ,
    Funktion VARCHAR(45) NOT NULL ,
    Anmerkung VARCHAR(45) NULL ,
    Newsletter BIT NOT NULL ,
    PRIMARY KEY (FirmenbetreuerID, gueltigAb, FirmenID) ,
    CONSTRAINT fk_Firmenbetreuer_Person
    FOREIGN KEY (FirmenbetreuerID )
    REFERENCES Person (PersonenID ),
    CONSTRAINT fk_Firmenbetreuer_Firma
    FOREIGN KEY (FirmenID )
    REFERENCES Firma (FirmenID ) );



    CREATE TABLE Projekt (
    ProjektID INT PRIMARY KEY IDENTITY ,
    ProjektStatusID INT NOT NULL ,
    ProjektleiterID INT NOT NULL ,
    FirmenbetreuerID INT NOT NULL ,
    FirmendID INT NOT NULL ,
    gueltigAb DATE NOT NULL ,
    ProjekttypID INT NOT NULL ,
    HauptProjekt INT NULL ,
    Ziele VARCHAR(45) NULL ,
    von DATE NOT NULL ,
    bis DATE NOT NULL ,
    SAP_ProjektNr INT NULL ,
    Anmerkung VARCHAR(45) NULL ,
    CONSTRAINT fk_Projekt_ProjektStatus
    FOREIGN KEY (ProjektStatusID )
    REFERENCES ProjektStatus (ProjektStatusID ),
    CONSTRAINT fk_Projekt_Person_Leiter
    FOREIGN KEY (ProjektleiterID )
    REFERENCES Person (PersonenID ),
    CONSTRAINT fk_Projekt_Firmenbetreuer
    FOREIGN KEY (FirmenbetreuerID , FirmendID , gueltigAb )
    REFERENCES Firmenbetreuer (FirmenbetreuerID , FirmenID , gueltigAb ),
    CONSTRAINT fk_Projekt_ProjektTyp
    FOREIGN KEY (ProjekttypID )
    REFERENCES ProjektTyp (ProjektTypID ),
    CONSTRAINT fk_Projekt_Projekt_Haupt
    FOREIGN KEY (HauptProjekt )
    REFERENCES Projekt (ProjektID ) );



    Thanks for your help
    Last edited by krenng; 07-26-11 at 11:52.

  2. #2
    Join Date
    Jul 2011
    Posts
    4

    Workaround

    I found a workaround.

    To be honest I have no idea why it works, but it does and that's at least something right? []

    what I did (apart from correcting my typo "FirmendID" to "FirmenID") was the following:

    I changed the order of the foreign keys so that it is INT, DATE, INT and then only referenced the table, not the particular columns. And that's where my logic ends. Why does changing the order of the columns help? any ideas?

    Heres the new extract regarding the foreign key which works now:
    CONSTRAINT fk_Projekt_Firmenbetreuer
    FOREIGN KEY (FirmenbetreuerID , gueltigAb, FirmendID )
    REFERENCES Firmenbetreuer ,

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by krenng View Post
    Why does changing the order of the columns help? any ideas?
    because that's the order that the columns are defined in the PK that the FK is referencing
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jul 2011
    Posts
    4
    Quote Originally Posted by r937 View Post
    because that's the order that the columns are defined in the PK that the FK is referencing
    sounds reasonable =D
    Thank you for the answer

    The DDL-Code is an export of MySQL-Workbench manually modified for SQL Server 2008 so that's why I didn't specifically pay attention to the order of the columns in the PK definition.

    Do you also have an idea why the changes are necessary? Or why it originally did not work?

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by krenng View Post
    Or why it originally did not work?
    because of the column datatypes

    your PK was defined as INT, DATE, INT

    therefore the foreign key must also be INT, DATE, INT

    when you tried the foreign key with INT, INT, DATE, you naturally got the error

    note that you actually could have defined the foreign key as

    ... FOREIGN KEY ( FirmendID , gueltigAb , FirmenbetreuerID )

    this would have worked, but then you would have noticed some very verrückte error messages when you started loading data


    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jul 2011
    Posts
    4
    haha okay, thanks for everything

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •