CODEWITHSUNDEEP
sqlsql-serverconstraints
John Ohara
John Ohara

Reputation: 2901

SQL constraint over multiple tables

I have a small group of tables that I'm connecting with SQL Server.

Here's a basic diagram:

enter image description here

Note: there are other columns in some tables which I've removed for brevity.

All of this works okay, but the design isn't as tight as it could/should be.

Here's the issue:

PageParam also connects to RouteParam, and that causes an issue with regard to integrity.

PageParam ultimately relates back to a Page. So does RouteParam.

However, with the current design, the Page could be different in each case - there is no constraint to ensure it's the same, and it needs to be. I'm aware that an extra table/columns may be required, but I'm struggling to see the logic of how to make this work.

Any advice appreciated.

Upvotes: 0

Views: 529

Answers (1)

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112352

Add a PageID to RouteParam and make the FK (PageID, RouteID). Also propagate the PageID down to PageParam. PageParam would then have the two FKs: FK (PageID, PageVarianID), FK (PageID, RouteParamId).

Since the foreign keys must relate to the primary keys or to unique indexes, it will also be necessary to either create compound primary keys or to add compound unique indexes.

   PageParam                                              PageVariant
  ┌────────────────────────┐                             ┌───────────────────────┐
  │ PageParamID    PK      │    PageID, PageVariantID    │ PageVariantID   PK, UX│
  │ PageID         FK1, FK2├───────────────────────────► │ PageID          FK, UX│
  │ PageVariantID  FK1     │                             └─────────┬─────────────┘
  │ RouteParamID   FK2     │                                       │
  └─────────────┬──────────┘                                       │
                │                                                  │PageID
                │PageID, RouteParamID                              │
                │                                                  │
   RouteParam   ▼                Route                    Page     ▼
  ┌──────────────────────┐      ┌──────────────────┐     ┌──────────────┐
  │ RouteParamID  PK, UX │      │  RouteID  PK, UX │     │ PageID  PK   │
  │ PageID        FK, UX ├─────►│  PageID   FK, UX ├────►│              │
  │ RouteID       FK     │      └──────────────────┘     └──────────────┘
  └──────────────────────┘ PageID                    PageID
                           RouteID

Upvotes: 2

Related Questions