Foreign Key constraint on fixed value field

foreign keyodbcsybase

Note: I am a developer…

I have an Asset table that has many codes that are foreign keys into a second table OutlineFiles. The OutlineFiles table has Type and Code as the primary key. The foreign key constraint need to look like this…

ALTER TABLE "dbo"."Assets" 
ADD CONSTRAINT "Locations" FOREIGN KEY ( "LN", "LocationCode" ASC ) 
REFERENCES "dbo"."OutlineFiles" ( "Type", "Code" );

ALTER TABLE "dbo"."Assets" 
ADD CONSTRAINT "CostCentre" FOREIGN KEY ( "CC", "CostCentreCode" ASC ) 
REFERENCES "dbo"."OutlineFiles" ( "Type", "Code" );

So there will be a relationship to the outlines table via a fixed value of "LN" and "CC". Or will it be easier to change the database to have two table and remove the Type column on the OutlineFiles table? I am attempting this in Sybase BUT we support many databases.

Best Answer

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.

Related Question