Vague error when trying to add a foreign key to an existing table

I need to add a new foreign key to my table called starList. It currently does not have a foreign key relationship with the table called planetList.

So I ran this command:

ALTER TABLE [dbo].[starList] WITH CHECK 
    ADD CONSTRAINT [FK_starList_planetList] 
        FOREIGN KEY([planetId]) REFERENCES [dbo].[planetList] ([planetId])
                ON UPDATE CASCADE
                ON DELETE CASCADE
GO

But I get this error:

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint “FK_starList_planetList”.
The conflict occurred in database “astro101”, table “dbo.planetList”, column ‘planetID’.

I am not sure what that means.

I tried looking around my tables on SQL Server but I don’t see anything wrong.

Could anyone help me figure out what the error means?

Thanks!

My starList table looks like this:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[starList]
(
    [starID] [nvarchar](50) NOT NULL,
    [galaxyID] [uniqueidentifier] NOT NULL,
    [starTitle] [nvarchar](3000) NULL,
    [planetID] [uniqueidentifier] NULL,

    CONSTRAINT [PK_StarList] 
        PRIMARY KEY CLUSTERED ([starID] ASC)
                    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                          IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                          ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[starList] WITH CHECK 
    ADD CONSTRAINT [FK_starList_galaxyList] 
        FOREIGN KEY([galaxyID]) REFERENCES [dbo].[galaxyList] ([galaxyID])
                ON UPDATE CASCADE
                ON DELETE CASCADE
GO

ALTER TABLE [dbo].[starList] CHECK CONSTRAINT [FK_starList_galaxyList]
GO

And the planetList table is this:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[planetList]
(
    [planetID] [uniqueidentifier] NOT NULL,
    [planetText] [nvarchar](max) NULL,

    PRIMARY KEY CLUSTERED ([planetID] ASC)
                WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                      ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Answer

You need to check your data in both columns.

There is a value in the column of foreign key table whose associated value doesn’t exist in the primary key table.

You have data in starlist.planetid which does not match/correspond to planetlist.planetid.

Both of them need to be the same with values values. It would be better to truncate them and then try creating the contraint.

In short, there is violation of referential integrity rules.

If this is the case, keep in mind to create foreign key before populating the tables.

Advertisement

Answer