Home > IT Answers > Database > Composite foreign Key in SQL Server
GabyCostilla
5 pts.
Q:
Composite foreign Key in SQL Server
Foreign keys, SQL Server 2008, Composite Foreign Keys, SQL Server error messages
I have a composite foreign key that has to be part of the primary key of the new table... but an error appears in SQL server...



CREATE TABLE Se_paga_con



(IDpel INT NOT NULL,



IDvta INT NOT NULL,


IDnota INT NOT NULL,



CantidadPeliculas INT,



PRIMARY KEY (IDpel,IDvta,IDnota),



FOREIGN KEY (IDpel, IDvta) REFERENCES PeliculaEnVenta(IDpel, IDvta));




IDpel AND IDvta ARE the primary key from PeliculaEnVenta


There are no primary or candidate keys in the referenced table 'PeliculaEnVenta' that match the referencing column list in the foreign key 'FK__Se_paga_con__51851410'.



Software/Hardware used:
SQL server 2008
ASKED: Nov 17 2009  1:56 AM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
GabyCostilla
5 pts.
A:
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
  • Bookmark and Share
Last Answered: Nov 17 2009  1:56 AM GMT by GabyCostilla   5 pts.
  •   
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

Carlosdl   34775 pts.  |   Nov 17 2009  2:59PM GMT

That should work if IDpel and IDvta are the primary key of PeliculaEnVenta.

Can you run the following query, to check the pk of the table ?

SELECT <a href="http://kc.name" title="http://kc. " target="_blank">kc.name</a>, <a href="http://c.name" title="http://c. " target="_blank">c.name</a>
FROM sys.key_constraints kc
JOIN sys.index_columns ic
ON kc.parent_object_id = ic.object_id
JOIN sys.columns c
ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
WHERE kc.type = ‘PK’
AND OBJECT_NAME(kc.parent_object_id) = ‘PeliculaEnVenta’;

 

Carlosdl   34775 pts.  |   Nov 17 2009  3:01PM GMT

The first line should be:

SELECT kc dot name, c dot name