SQL Server - data consistency is important

Sometimes it becomes difficult to detect the reasons why SQL Server is complaining about failure of some statement if you are not paying close attention. Here is one of the examples:

Create a table "dbo.Records" and insert some records in it.

Create Table dbo.[Records]

(

Id int not null primary key identity (1,1),

Name nvarchar(100) not null

)

GO

Insert Into dbo.Records

(Name)

SELECT 'Record1'

Create a table dbo.MasterRecords and insert some records in it.

Create Table dbo.[MasterRecords]

(

Id int not null primary key,

Name nvarchar(50) not null

)

GO

Insert Into dbo.MasterRecords

(Id, name)

SELECT 1, 'TestMaster1'

Now if you realize that you should have a reference relationship between the two tables, you may choose to run Entity Framework Migrations. If you let it do it by itself then it will generate the script like following:

ALTER TABLE dbo.[Records]

ADD MasterRecordId Int NOT NULL Default (0)

GO

ALTER TABLE dbo.[Records]

Add constraint FK_RecordId_Master_MasterId Foreign Key (MasterRecordId) References MasterRecords(Id)

GO

You will notice that the last statement of creating foreign key fails. It fails for a very valid reason though. MasterRecords table does not have a record for Id "0" and hence the foreign key can not be created because data present in the two tables is not consistent with whatever is demanded by the sql statement.

Solution? Either make existing records consistent i.e. update the records in Records table to match with existing values in MasterRecords table or Add a record in MasterRecords that has Id value "0".

Insert Into dbo.MasterRecords

(Id, name)

SELECT 0, 'None'

Once you do that everything works fine.