SQL Server Pros and Cons of Constraints across Many-To-Many relationship

gcuhipw9 于 2023-11-16 发布在其他

关注(0)|答案(1)|浏览(79)

I have tables with relationships like the following.

CREATE TABLE DocumentType
(
   ID uniqueidentifier primary key,
   ...
)
GO

CREATE TABLE LinkedDoc
(
   ID uniqueidentifier ROWGUIDCOL NOT NULL,
   DocumentTypeID uniqueidentifier NOT NULL,
   ...,
   CONSTRAINT [PK_LinkedDoc] 
       PRIMARY KEY CLUSTERED (ID, DocumentTypeID)
)
GO

ALTER TABLE LinkedDoc 
    ADD CONSTRAINT FK_DocumentType_LinkedDoc 
        FOREIGN KEY(DocumentTypeID) REFERENCES DocumentType (ID)
GO

CREATE TABLE SomeOtherTable
(
   ID uniqueidentifier primary key,
   ...
)

CREATE TABLE ManyToMany 
(
   ID uniqueidentifier primary key,
   LinkedDocID uniqueidentifier,
   DocumentTypeID uniqueidentifier
   SomeOtherTableID uniqueidentifier,

   CONSTRAINT IX_ManyToMany UNIQUE (DocumentTypeID, SomeOtherTable)
)
GO

ALTER TABLE ManyToMany WITH CHECK 
   ADD CONSTRAINT FK_SomeOtherTable_ManyToMany 
      FOREIGN KEY (SomeOtherTableID) REFERENCES SomeOtherTable (ID)
GO

ALTER TABLE ManyToMany WITH CHECK 
    ADD CONSTRAINT FK_LinkedDoc_ManyToMany 
        FOREIGN KEY (LinkedDocID, DocumentTypeID) REFERENCES LinkedDoc (ID, DocumentTypeID)
GO

I omitted some details not relevant to my question. My goal was to build the tables so that each unique entity in the SomeOtherTable cannot be linked to more than one LinkedDoc with a given doc type. This is achieved above via the IX_ManyToMany constraint.

While it works, this solution isn't sitting well with me, and I'd like to change it now before I load data into the system. The main point of concern is IX_ManyToMany has a partial dependency on the tables primary key. Additionally ID in LinkedDoc is a ROWGUIDCOL meaning my pk for the table is not minimal.

Is there a better alternative? I'd be open to refactor my tables, but I would like to avoid db triggers and functions because at that point I feel like I'm putting business logic in the db. I prefer to keep BL in my API, where it's source controlled and easier to maintain.

I found this Constraint for a many-to-many relationship table - both the related records need to reference the same dependent record? The first answer is similar to my solution. The second uses a db function which I'm willing to consider, but would like to see if it can be done by modifying the schema. Although, rather than using the function solution I may opt to simply allow the db to violate my requirement and then handle it in the API.

sql-server

来源：https://stackoverflow.com/questions/77391751/pros-and-cons-of-constraints-across-many-to-many-relationship

1条答案

按热度按时间

hfsqlsce1#

This is indeed the standard way to go about this:

..
   CONSTRAINT IX_ManyToMany UNIQUE (DocumentTypeID, SomeOtherTable)
..

Note also that ManyToMany does not actually need a separate ID column, the PK can be made up of just (LinkedDocID, SomeOtherTableID) .

The main point of concern is IX_ManyToMany has a partial dependency on the tables primary key. Additionally ID in LinkedDoc is a ROWGUIDCOL meaning my pk for the table is not minimal.
I'm not sure why you are using ROWGUIDCOL , which is really for replication scenarios, but it's not relevant. Yes, it is sort-of a dependency, but you need to think of LinkedDoc as having two keys: a PK on ID and a secondary UNIQUE on (ID, DocumentTypeID) , and that extra column is just part of its secondary key which needs to go into the foreign table.

So the definition should be

CREATE TABLE LinkedDoc
(
   ID uniqueidentifier NOT NULL,
   DocumentTypeID uniqueidentifier NOT NULL,
   ...,
   CONSTRAINT [PK_LinkedDoc] 
       PRIMARY KEY CLUSTERED (ID),
   INDEX UK_LinkedDoc_DocumentTypeID
       UNIQUE (ID, DocumentTypeID),
   CONSTRAINT FK_DocumentType_LinkedDoc
       FOREIGN KEY(DocumentTypeID) REFERENCES DocumentType (ID)

Note I have used a unique index rather than a unique constraint, as this allows you to add INCLUDE , and still works as a FK target.

The other option is to enforce this using an indexed view. This is unikely to be more efficient though, unless you need the indexed view anyway.

CREATE TABLE ManyToMany 
(
   LinkedDocID uniqueidentifier,
   SomeOtherTableID uniqueidentifier,
   PRIMARY KEY PK_ManyToMany (LinkedDocID, SomeOtherTableID),
   CONSTRAINT FK_SomeOtherTable_ManyToMany 
      FOREIGN KEY (SomeOtherTableID) REFERENCES SomeOtherTable (ID)
   CONSTRAINT FK_LinkedDoc_ManyToMany 
      FOREIGN KEY (LinkedDocID) REFERENCES LinkedDoc (ID)
)

Then create a joined view containing the DocumentTypeID

CREATE VIEW dbo.ManyToMany_DocumentTypeID
WITH SCHEMABINDING
AS

SELECT
  mm.LinkedDocID 
  mm.SomeOtherTableID,
  ld.DocumentTypeID
FROM dbo.ManyToMany mm
JOIN dbo.LinkedDoc ld ON ld.ID = mm.LinkedDocID;

And index it

CREATE UNIQUE CLUSTERED INDEX IX ON ManyToMany_DocumentTypeID (SomeOtherTableID, DocumentTypeID);

赞(0）回复(0）举报 2023-11-16

我来回答

SQL Server Pros and Cons of Constraints across Many-To-Many relationship

1条答案

相关问题

热门标签

最新问答