sqlforeign-keyscomposite-keyself-referencedbo

Composite Key on a Self Referencing Table


We have a composite primary key for the site table defined below. Functionally, this does exactly as we would like it to. Each site should have a parent site of the same district. Defining the table in this way allows specifically for that.

    CREATE TABLE [dbo].[site](
        [site_number] [nvarchar](50) NOT NULL,
        [district_id] [bigint] NOT NULL,
        [partner_site_number] [nvarchar](50) NULL,
     CONSTRAINT [PK_site] PRIMARY KEY CLUSTERED 
    (
        [site_number] ASC,
        [district_id] ASC
    )

    ALTER TABLE [dbo].[site]  WITH CHECK ADD  CONSTRAINT [FK_site_site] FOREIGN KEY([partner_site_number], [district_id])

My specific question is regarding the self-referencing FK defined on a composite PK. I've heard a few opinions on this particular design and they tend to be conflicting. Some like it particularly because it functions as it should within a general understanding of composite keys. Others insist that it is theoretically incorrect and that there should also be a [partner_district_id] field that is included in the FK instead of [district_id]. This design would require validation to enforce that the [district_id] = [partner_district_id], which could be done either with a check constraint or application level logic.

Further opinions on these solutions or any others would be appreciated.


Solution

  • I'd suggest SiteId on it's own be the primary key. DistrictId should probably be a foreign key?

    EDIT - in that case, i'd suggest adding the additional PartnerDistrictId to the foreign key; you never know, you may later want to partner one site with another in a different district. But personally, I'd be in favour of a surrogate key here. And in most cases ;)