robertjuh February 2016

Creating a foreign key constraint with non-matching data

Greetings!

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'.

Answers


Henrik Staun Poulsen March 2016

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

You could add the FK like this:

ALTER TABLE dbo.order1 WITH NOCHECK 
ADD CONSTRAINT FK_review_order1 FOREIGN KEY (id) REFERENCES dbo.article_review (id)

note the WITH NOCHECK

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

Search




Leave an answer