Foreign key referencing a 2 columns primary key in SQL Server

Question

This question is pretty much similar to this one  but for SQL Server 2005    I have 2 tables in my database   --    denotes the primary key  Libraries   ID    Application  Name  1     MyApp        Title 1  2     MyApp        Title 2    Content   ID   Application  LibraryID  Content  10    MyApp       1          xxx  11    MyApp       1          yyy    the database is obviously much more complex and having this double key makes sense   Each library is identified by its unique ID and Application name  I m trying to ensure that each content is properly referencing an existing library   When creating the constraint  using the Wizard  as  Primary key table            Foreign key table  Libraries                    Content  ID                  --- gt      LibraryID Application         --- gt      Application   I have the following error       The columns in table  Libraries  do   not match an existing primary key or   UNIQUE constraint   Do you have any idea of what is going on  and if it s possible at all using SQL Server   I can t modify the  Library  table at all   Thanks a lot for your help

User · Accepted Answer

Of course it s possible to create a foreign key relationship to a compound  more than one column  primary key  You didn t show us the statement you re using to try and create that relationship - it should be something like   ALTER TABLE dbo Content    ADD CONSTRAINT FK Content Libraries    FOREIGN KEY LibraryID  Application     REFERENCES dbo Libraries ID  Application    Is that what you re using   If  ID  Application  is indeed the primary key on dbo Libraries  this statement should definitely work   Luk   just to check - can you run this statement in your database and report back what the output is    SELECT     tc TABLE NAME      tc CONSTRAINT NAME       ccu COLUMN NAME FROM      INFORMATION SCHEMA TABLE CONSTRAINTS tc INNER JOIN      INFORMATION SCHEMA CONSTRAINT COLUMN USAGE ccu        ON ccu TABLE NAME   tc TABLE NAME AND ccu CONSTRAINT NAME   tc CONSTRAINT NAME WHERE     tc TABLE NAME IN   Libraries    Content

User · Answer

I had the same problem and I think I have the solution   If your field Application in table Library has a foreign key that references a field in another table  named Application I would bet   then your field Application in table Library has to have a foreign key to table Application too   After that you can do your composed foreign key   Excuse my poor english  and sorry if I m wrong

User · Answer

The Content table likely to have multiple duplicate Application values that can t be mapped to Libraries  Is it possible to drop the Application column from the Libraries Primary Key Index and add it as a Unique Key Index instead

User · Answer

Note that the fields must be in the same order  If the Primary Key you are referencing is specified as  Application  ID  then your foreign key must reference  Application  ID  and NOT  ID  Application  as they are seen as two different keys

User · Answer

The key is  the order of the column should be the same   Example   create Table A       A ID char 3  primary key      A name char 10  primary key      A desc desc char 50     create Table B       B ID char 3  primary key      B A ID char 3       B A Name char 10       constraint  Fk B 01  foreign key  B A ID B A Name  references A A ID A Name      the column order on table A should be --  A ID then A Name  defining the foreign key should follow the same order as well

[sql] Foreign key referencing a 2 columns primary key in SQL Server

Examples related to sql

Examples related to sql-server-2005

Examples related to foreign-key-relationship