Error creating Foreign key

Asked

Viewed 732 times

0

I am creating two references of Foreign key pointing to two different tables, however I am getting error in creating one of them.

There are no Primary or candidate Keys in the referenced table 'dbo.TB_PODERES' that match the referencing column list in the Foreign key 'FK_TB_GRUPOS_PODERES_PODERES_TB_PODERES'.

The reference table is as follows:

    CREATE TABLE [dbo].[TB_PODERES](
    [ID_PDR] [int] IDENTITY(1,1) NOT NULL,
    [CD_PODER] [char](10) NOT NULL,
    [NM_PODER] [varchar](100) NOT NULL,
    [IC_ATIVO] [bit] NOT NULL,
    [DT_ULT_ATUALIZACAO] [datetime] NOT NULL,
 CONSTRAINT [PK_TB_PODERES_PODER] PRIMARY KEY NONCLUSTERED 
(
    [ID_PDR] ASC,
    [CD_PODER] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

and the table I’m creating at FK is this.

    CREATE TABLE [dbo].[TB_GRUPOS_PODERES_PODERES](
    [ID_GPP] BIGINT IDENTITY(1,1) NOT NULL,
    [ID_GRP_PDR] [bigint] NOT NULL,
    [CD_PODER] [char](10) NOT NULL, 
    [IC_ATIVO] [bit] NOT NULL,
    [DT_ULT_ATUALIZACAO] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [ID_GPP],[ID_GRP_PDR],[CD_PODER] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

    ALTER TABLE [dbo].TB_GRUPOS_PODERES_PODERES  WITH CHECK ADD  CONSTRAINT FK_TB_GRUPOS_PODERES_PODERES_TB_PODERES FOREIGN KEY([CD_PODER])
    REFERENCES [dbo].TB_PODERES ([CD_PODER])
GO
ALTER TABLE [dbo].TB_GRUPOS_PODERES_PODERES CHECK CONSTRAINT FK_TB_GRUPOS_PODERES_PODERES_TB_PODERES
GO
sql sql-server constraint

  • Check that the primary key of the table TB_PODERES is actually the pair (ID_PDR, CD_PODER). If it is, which column in table TB_GRUPOS_PODERES_PODERES refers to column ID_PDR in table TB_PODERES?

1 answer

1


You need to use in the FK the same number of columns as in PK. In this case, you have a PK composed of [ID_PDR],[CD_PODER] your FK also need 2 columnas of the same data type.

I hope it helps you.

I tried your script and added this column ID_PDR at the table [TB_GRUPOS_PODERES_PODERES]

CREATE TABLE [dbo].[TB_PODERES](
    [ID_PDR] [int] IDENTITY(1,1) NOT NULL,
    [CD_PODER] [char](10) NOT NULL,
    [NM_PODER] [varchar](100) NOT NULL,
    [IC_ATIVO] [bit] NOT NULL,
    [DT_ULT_ATUALIZACAO] [datetime] NOT NULL,
 CONSTRAINT [PK_TB_PODERES_PODER] PRIMARY KEY NONCLUSTERED 
(
    [ID_PDR] ASC,
    [CD_PODER] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE [dbo].[TB_GRUPOS_PODERES_PODERES](
    [ID_GPP] BIGINT IDENTITY(1,1) NOT NULL,
    [ID_GRP_PDR] [bigint] NOT NULL,
    [CD_PODER] [char](10) NOT NULL, 
    [IC_ATIVO] [bit] NOT NULL,
    [DT_ULT_ATUALIZACAO] [datetime] NOT NULL,
    [ID_PDR] [int],
PRIMARY KEY CLUSTERED 
(
    [ID_GPP],[ID_GRP_PDR],[CD_PODER] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

    ALTER TABLE [dbo].TB_GRUPOS_PODERES_PODERES  WITH CHECK ADD  CONSTRAINT FK_TB_GRUPOS_PODERES_PODERES_TB_PODERES FOREIGN KEY([ID_PDR],[CD_PODER])
    REFERENCES [dbo].TB_PODERES ([ID_PDR],[CD_PODER])
GO

ALTER TABLE [dbo].TB_GRUPOS_PODERES_PODERES CHECK CONSTRAINT FK_TB_GRUPOS_PODERES_PODERES_TB_PODERES
GO

Browser other questions tagged sql sql-server constraint

You are not signed in. Login or sign up in order to post.