Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
krenng
Starting Member
2 Posts |
 Posted - 2011-07-26 : 10:30:47
|
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. 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: quote:
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 :) |
|
|
krenng
Starting Member
2 Posts |
Posted - 2011-07-26 : 11:42:56
|
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:quote:
CONSTRAINT fk_Projekt_FirmenbetreuerFOREIGN KEY (FirmenbetreuerID , gueltigAb, FirmendID )REFERENCES Firmenbetreuer ,
|
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-07-26 : 12:16:33
|
| sounds like some forward reference was happening which caused this.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
Multiple columns - ForeignKey Constraint - Msg1776