locked
Restrict a PK value to only one of the child tables RRS feed

  • Question

  • Requirement:

    User can save comments related to various entities viz. Audits and Claims. There will be more entities in future.

    The comments can be of one these types -

    1. Internal
    2. External
    3. Departmental or
    4. Mixed

    There could be more types in future. Types can be restricted to specific entities. E.g. Audits can have only Internal and External comment types, Claims can have only Internal, External or Departmental types.

    A comment can be associated with only one entity, although one entity can have multiple comments.

    First design -

    Design 1

    Issues with the First design -

    1. How to implement a constraint so that an entity can have only specific comment types?
    2. How to implement a constraint that a [Comment].[Id] (PK) can be used in only one XREF table so that one comment is not accidentally associated with different entities?

    Second design -

    Design 2

    Here I've moved the CommentType to the XREF tables and created CHECK constraints to restrict the Comment Types.

    ALTER TABLE dbo.AuditCommentXREF
    ADD CONSTRAINT CK_AuditCommentType CHECK(CommentType = 1 OR CommentType = 2)
    
    ALTER TABLE dbo.ClaimCommentXREF
    ADD CONSTRAINT CK_ClaimCommentType CHECK(CommentType = 1 OR CommentType = 2 OR CommentType = 3)

    This solves the issue #2 but the issue #1 remains.

    Issue with the Second design -

    1. How to implement a constraint that a [Comment].[Id] (PK) can be used in only one XREF table so that one comment is not accidentally associated with different entities?

    EDIT: 12/22/2012 - I have created one UDF for each XREF table that checks that the [Comment].[Id] is not used in the other XREF table. This UDF is then made a part of the CHECK constraint. Script is in the comments below. This resolves the issue # 1. But this solution means that every time we add a new entity, we will have to create a new UDF+CHECK for its XREF table and update the existing UDFs to include verification for the new XREF table. Could there be a better way to implement this kind of constraint?

    Suggestions please.


    Thanks!
    Aalam | Blog (http://aalamrangi.wordpress.com)



    • Edited by Aalamjeet Rangi Sunday, December 23, 2012 4:59 AM Updated description about the UDF solution and requesting if there is a better way.
    Thursday, December 20, 2012 3:44 AM

Answers

All replies

  • >How to implement a constraint that a CommentId can be referred only in one XREF table?

    Implement a UDF CHECK constraint in each XREF table to check is there is an FK pointing to CommentID:

    http://www.sqlusa.com/bestpractices/udf-check-constraint/

    USE AdventureWorks2012;
    
    SELECT	OBJECT_NAME(FK.parent_object_id) as ReferencingTable, 
            OBJECT_NAME(FK.constraint_object_id) as FKConstraint
    FROM sys.foreign_key_columns as FK
    WHERE FK.referenced_object_id = OBJECT_ID('Sales.SalesOrderHeader', 'U')
     /*
    ReferencingTable	FKConstraint
    SalesOrderDetail	FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID
    SalesOrderHeaderSalesReason	FK_SalesOrderHeaderSalesReason_SalesOrderHeader_SalesOrderID
    */


    Kalman Toth SQL 2008 GRAND SLAM
    New Book: SQL Server 2012 Programming

    Thursday, December 20, 2012 8:04 AM
  • Thanks Kalman,

    A CHECK constraint with a UDF (or even creating a TRIGGER) on the XREF tables is a viable option. But this would mean that everytime there is a new XREF table, all the existing UDFs will need to be updated to include the new table. Could there be a better implementation?

    The UDFs in my case implemented as follows -

    create table dbo.Comment
    (Id int identity(1,1) primary key,
    CommentType int,
    Comment varchar(1000),
    CreatedDate datetime,
    CreatedBy varchar(10))
    
    insert into dbo.Comment (CommentType, Comment, CreatedDate, CreatedBy)
    values (1, 'Demo internal comment', getdate(), 'DemoUser')
    insert into dbo.Comment (CommentType, Comment, CreatedDate, CreatedBy)
    values (2, 'Demo external comment1', getdate(), 'DemoUser')
    insert into dbo.Comment (CommentType, Comment, CreatedDate, CreatedBy)
    values (2, 'Demo external commen2', getdate(), 'DemoUser')
    insert into dbo.Comment (CommentType, Comment, CreatedDate, CreatedBy)
    values (3, 'Demo departmental comment', getdate(), 'DemoUser')
    
    create table dbo.Audit
    (Id int identity(1,1) primary key
    ,AuditNumber varchar(10))
    
    insert into dbo.Audit (AuditNumber)
    values ('Audit1000')
    
    create table dbo.AuditCommentXREF
    (Id int identity(1,1),
    AuditId int foreign key references dbo.Audit(Id),
    CommentId int foreign key references dbo.Comment(Id)
    )
    go
    
    create table dbo.Claim
    (Id int identity(1,1) primary key
    ,ClaimNumber varchar(10))
    
    insert into dbo.Claim (ClaimNumber)
    values ('Claim1000')
    go
    
    create table dbo.ClaimCommentXREF
    (Id int identity(1,1),
    ClaimId int foreign key references dbo.Claim(Id),
    CommentId int foreign key references dbo.Comment(Id)
    )
    go
    
    create function dbo.udf_CheckIsAuditCommentAlreadyAssigned (@CommentId int)
    returns bit
    as
    begin
    	if exists (select 1 from dbo.ClaimCommentXREF where CommentId = @CommentId)
    		return (1)
    
    	return(0)
    end
    go
    
    alter table dbo.AuditCommentXREF
    with check
    add constraint CK_CheckIsAuditCommentAlreadyAssigned check
    (
    	dbo.udf_CheckIsAuditCommentAlreadyAssigned(CommentId) = 0
    )
    go
    
    create function dbo.udf_CheckIsClaimCommentAlreadyAssigned (@CommentId int)
    returns bit
    as
    begin
    	if exists (select 1 from dbo.AuditCommentXREF where CommentId = @CommentId)
    		return (1)
    
    	return(0)
    end
    go
    
    alter table dbo.ClaimCommentXREF
    with check
    add constraint CK_CheckIsClaimCommentAlreadyAssigned check
    (
    	dbo.udf_CheckIsClaimCommentAlreadyAssigned(CommentId) = 0
    )
    
    insert into dbo.AuditCommentXREF (AuditId, CommentId)
    values (1, 1)
    
    -- This should give an error because CommentId 1 is already assigned in the AuditCommentXREF above
    insert into dbo.ClaimCommentXREF (ClaimId, CommentId)
    values (1, 1)
    -- This is ok
    insert into dbo.ClaimCommentXREF (ClaimId, CommentId)
    values (1, 2)
    
    
    select * from dbo.Comment
    select * from dbo.Audit
    select * from dbo.AuditCommentXREF
    select * from dbo.Claim
    select * from dbo.ClaimCommentXREF
    
    drop table dbo.AuditCommentXREF
    drop table dbo.ClaimCommentXREF
    drop table dbo.Comment
    drop table dbo.Audit
    drop table dbo.Claim
    drop function dbo.udf_CheckIsAuditCommentAlreadyAssigned
    drop function dbo.udf_CheckIsClaimCommentAlreadyAssigned


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.
    Thanks!
    Aalam | Blog (http://aalamrangi.wordpress.com)

    Thursday, December 20, 2012 12:50 PM
  • No. The CHECK constraint is created on the XREF table FOREIGN KEY column, not on the PRIMARY KEY column.

    So whenever a new XREF table is created, the UDF CHECK constraint added to the table.

    Related thread:

    http://social.msdn.microsoft.com/Forums/en/databasedesign/thread/7712a43c-223b-4696-9f62-7dc1f4ce4dff


    Kalman Toth SQL 2008 GRAND SLAM
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

    Thursday, December 20, 2012 1:48 PM
  • I'm sorry I couldn't understand your suggestion!

    Data example:

    If a CommentId is being used in the AuditXREF table, then the system should not let the same CommentId to be used in any other XREF table - in this case the ClaimXREF table.

    -- Associate one CommentId to an entity
    insert into dbo.AuditCommentXREF (AuditId, CommentId)
    values (1, 1)
    -- This one below should give an error because CommentId 1 is already assigned in the AuditCommentXREF above
    insert into dbo.ClaimCommentXREF (ClaimId, CommentId)
    values (1, 1)
    -- This one below is ok because it is a different CommentId i.e. 2
    insert into dbo.ClaimCommentXREF (ClaimId, CommentId)
    values (1, 2)

    The UDF that I've shown in my example script previously handles this pretty well. But there will more entities in the future and thus more XREF tables. So in addition to creating new UDFs for the new entities, I'll have to update the existing UDFs too for the new XREF tables. Could there be a generic solution?


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.
    Thanks!
    Aalam | Blog (http://aalamrangi.wordpress.com)

    Friday, December 21, 2012 12:38 PM
  • Hello Aalam Rangi,

       One more approach would be , Add a column in Comments table as "UsedBy". Once the comment is being used , you can update the table with the XRef which is using it. Also you can have a generic function to check if the commentid selected is having UsedBy as Blank or Null. One more benefit is , you can easily find out which commentid is used in which XREF table.

    Hope this helps.


    Best Regards Sorna

    Tuesday, December 25, 2012 6:38 AM