Avoid circular reference with configuration tables I've got a few tables that define configuration information.  This defines what combination are allowed in the system.

However, I've got a circular reference on ProjectName which can cause potential invalid relationships.  I'm wondering how I can enforce this better in the database.  I don't want to trust the code to enforce it.

Table Schema (Table / Columns)

Project
    ProjectName

Category
    CategoryName

ProjectCategory
    ProjectCategoryID
    ProjectName
    CategoryName

Inventory
    ProjectName
    ProjectCategoryID


Populated Tables (Table/Column/Data)

Project
    ProjectName
        A
        B

Category
    CategoryName
        X
        Y

ProjectCategory
    ProjectCategoryID, ProjectName, ProjectCategory
        1, A, X
        2, A, Y
        3, B, Y

Inventory
    ProjectName, ProjectCategoryID
        A, 1
        A, 3


Inventory record (A, 3) is invalid.  The inventory belongs to project 'A' yet the configuration belongs to project 'B'.  Because of the circular reference, its possible to associate a record incorrectly.
You can solve this problem by either removing ProjectName from Inventory (since it is redundant because it is already specified in ProjectCategory), or enforce the relationship via a foreign key:

CREATE UNIQUE INDEX ON ProjectCategory (ProjectCategoryID, ProjectName);

ALTER TABLE Inventory ADD
  FOREIGN KEY (ProjectCategoryID, ProjectName) REFERENCES (ProjectCategoryID, ProjectName);