Sql-server – SSIS Failed foreign key constraint

It sounds like you have a "One True Lookup Table" (OTLT) anti-pattern and you are mixing entities in this table. You've found why it isn't a good idea:

• can't have filtered foriegn keys
• can't FK to constants
• can't have multiple parents

Your sample code above is confusing (you have multiple parents for the same Code column) so I'll give you what I understand

CREATE TABLE OutlineFilesCostCentre (
    CostCentreCode ...NOT NULL --PK
    ...
    )

CREATE TABLE OutlineFilesLocations (
    LocationCode ... NOT NULL --PK
    ...
    )

CREATE TABLE Assets (
    ...
    CostCentreCode ... NOT NULL,
    LocationCode ... NOT NULL,
    ...

    CONSTRAINT FK_Assets_CostCentre" FOREIGN KEY ("CostCentreCode")
                REFERENCES "dbo"."OutlineFilesCostCentre" ("CostCentreCode"),
    CONSTRAINT FK_Assets_Locations" FOREIGN KEY ("LocationCode")
                REFERENCES "dbo"."OutlineFilesLocations" ("LocationCode")
   ...)

If you have 30 codes to lookup you will have 30 lookup tables: this is correct.

If you insist on OTLT you'll have to add extra columns to store type in Assets and FK them to your OTLT. I wouldn't do this.

    ...
    CostCentreType char(2) NOT NULL,
    CostCentreCode ... NOT NULL,
    LocationType  char(2) NOT NULL,
    LocationCode ... NOT NULL,
    ...

Or use triggers to maintain the correct codes. I wouldn't do this either.

You need to have already declared the table that the foreign key references, before you can define a foreign key that references it.

Once you declare the second table, you can then declare the first table. Tested here on MySQL 5.5.27.

If you need to bypass the validation, you can do this:

SET FOREIGN_KEY_CHECKS = 0;
-- declare tables
SET FOREIGN_KEY_CHECKS = 1;

http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_foreign_key_checks