Home Ask Login Register

Developers Planet

Your answer is one click away!

robertjuh February 2016

Creating a foreign key constraint with non-matching data


I have inserted data from sql insert files into an ms sql database. Apperantly this data is not fully complete. I discovered this when I was trying to make the ERD and create key constraints between tables. When I try to connect article_review with order1 where order1 has a primary key and article review has a foreign key.

I have a query where it selects all records with non-matching key values: see image: http://imgur.com/vDbCuG8

So what I want to do now: insert new rows into article_review with the missing ID values. The values of the other columns do not really matter, they can be NULL or random generated.

A simple join won't really cut it because all of the other columns are non-identical.

ps. all above is needed because ms sql 2016 wont let me create a key constraint between 2 tables where one of them contains a value that is not in the other one and thus throws the error:

'order1' table saved successfully
'article_review' table saved successfully
'review' table
- Unable to create relationship 'FK_review_order1'.  
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_review_order1". The conflict occurred in database "superDatabase", table "dbo.order1", column 'id'.


Henrik Staun Poulsen March 2016

Do you just want to add the Foreign key for documentation only?

You could add the FK like this:

ADD CONSTRAINT FK_review_order1 FOREIGN KEY (id) REFERENCES dbo.article_review (id)


This way the foreign key is created, but is not trusted and disabled, which can be seen with this query:

SELECT SCHEMA_NAME(fk.schema_id) AS sch, T.name,
        fk.name, is_disabled, is_not_trusted, fk.is_not_for_replication
FROM    sys.foreign_keys fk WITH (NOLOCK)
        INNER JOIN sys.tables T WITH (NOLOCK) ON T.object_id = fk.parent_object_id
WHERE   fk.is_not_trusted = 1
        AND fk.is_disabled = 0
ORDER BY SCHEMA_NAME(fk.schema_id), T.name, fk.name;

idea from https://www.brentozar.com/blitz/foreign-key-trusted/

Post Status

Asked in February 2016
Viewed 3,234 times
Voted 10
Answered 1 times


Leave an answer

Quote of the day: live life