Error with SQL server Compound keys

2

this is the script for the first table

CREATE TABLE [dbo].[eva_cat_metodologias] (
    [IdMetodologia]  SMALLINT       IDENTITY (1, 1) NOT NULL PRIMARY KEY,
    [DesMetodologia] VARCHAR (200)  NULL,
    [Clave]          VARCHAR (50)   NULL,
    [Explicacion]    VARCHAR (3000) NULL
);

and this one of the second

CREATE TABLE [dbo].[eva_plantilla_metodologia] (
    [IdMetodologia]      SMALLINT      NOT NULL,
    [IdPlantillaMetodo]  SMALLINT      IDENTITY (1, 1) NOT NULL,
    [DesPlantillaMetodo] VARCHAR (200) NULL,
    [FechaReg]           DATETIME      NULL,
    [VersionActual]      CHAR (1)      NULL,
    FOREIGN KEY ([IdMetodologia]) REFERENCES [dbo].[eva_cat_metodologias] ([IdMetodologia]),
    PRIMARY KEY CLUSTERED (IdMetodologia,IdPlantillaMetodo)

);

When I want to create the third table with the foreign keys and the composite key does not leave me and I get the following error:

  

Msg 1776, Level 16, State 0, Line 1 There are no primary or candidate   keys in the referenced table 'dbo.eva_plantilla_metodologia' that   match the referencing column list in the foreign key   'FK__eva_plantilla_mo__5441852A'. Msg 1750, Level 16, State 0, Line 1   Could not create constraint. See previous errors.

this is the script I'm trying to insert

CREATE TABLE [dbo].[eva_plantilla_momentos_metodologia] (
    [IdMomento]         SMALLINT       IDENTITY (1, 1) NOT NULL,
    [IdMetodologia]     SMALLINT       NOT NULL,
    [IdPlantillaMetodo] SMALLINT       NOT NULL,
    [DesMomento]        VARCHAR (500)  NULL,
    [Objetivo]          VARCHAR (3000) NULL,
    [Secuencia]         SMALLINT       NULL,
    PRIMARY KEY CLUSTERED (IdMetodologia,IdPlantillaMetodo,IdMomento),
    FOREIGN KEY (IdPlantillaMetodo) REFERENCES [dbo].[eva_plantilla_metodologia] (IdPlantillaMetodo),
    FOREIGN KEY (IdMetodologia) REFERENCES [dbo].[eva_plantilla_metodologia] (IdMetodologia),

 );
    
asked by Ricardokr0 03.06.2018 в 04:28
source

1 answer

1

The eva_plantilla_metodologia key is:

PRIMARY KEY CLUSTERED (IdMetodologia,IdPlantillaMetodo)

So a foreign key that points to that key must have two fields to store the two values of the key referenced

FOREIGN KEY (IdMetodologia, IdPlantillaMetodo) REFERENCES [dbo]. 
[eva_plantilla_metodologia] (IdMetodologia, IdPlantillaMetodo)
    
answered by 03.06.2018 / 04:37
source