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.
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 ;)