Search code examples
sqlsql-serversql-server-2014-express

What would be the best way to link these two tables

I have two tables in a database, both of which are derived from official government reference tables originally supplied in spreadsheet form.

The structure of these two tables are illustrated below.

Table 1 (Species Codes)

enter image description here

Table 2 (Allowed presentation codes)

enter image description here

When I try and create a relationship between the first and the second (so as to make full use of the ability to look up values in the second table I get the following error when trying to link speciescodes.FAOCode to allowedstates.ErsSpeciesCodes).

'SpeciesCodeLookup' table saved successfully 'AllowedPresentationAndStateCodesLookup' table - Unable to create relationship 'FK_AllowedPresentationAndStateCodesLookup_SpeciesCodeLookup'.
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_AllowedPresentationAndStateCodesLookup_SpeciesCodeLookup". The conflict occurred in database "FishTrackerPro", table "dbo.SpeciesCodeLookup", column 'FAOCode'.

Can anyone enlighten me as to 1) why is this error occurring 2) is there a way (by altering one or other table where such a relation might be established?

Solution

  • It seems you are getting this issue because referential integrity is not met. I.e Foreign key table must not have values which does not exists in primary key table.

    Check these links :

    1. Alter table conflicted with foreign key constraint
    2. SQL conflicted with the FOREIGN KEY constraint