Composite primary key with foreign key relationships to same table

Question

so I have a table called "Event" and I want to create another table where an Event can contain more Events from the same table. This is what I have so far.

This is the current existing table...

CREATE TABLE [dbo].[EventEvents]
    (
       [step_id] [uniqueidentifier] NOT NULL PRIMARY KEY,
       [title] [nvarchar](200) NOT NULL,
       [Enabled] [bit] NOT NULL,
    )

Then this is the table I am trying to create...

CREATE TABLE [dbo].[EventEvents]
(
   [EventId] [uniqueidentifier] NOT NULL,
   [EventChildId] [uniqueidentifier] NOT NULL,
   [Enabled] [bit] NOT NULL,
   CONSTRAINT [PK_EventEvents] PRIMARY KEY ([EventId], [EventChildId]),
   CONSTRAINT [FK_Event_EventChild] FOREIGN KEY ([EventId],[EventChildId]) REFERENCES [dbo].[Event] ([step_id], [step_id])
)

So both EventId and EventChildId both are foreign keys to Event - step_id as 1 event can other events as children within it. But I need both EventId and EventChildId to be composite primary keys. How can I do this?

At the moment I get an error saying:

Duplicate columns specified in FOREIGN KEY constraint key list

Thanks

score 0 · Accepted Answer · answered Jan 24 '14 at 10:47

I've figured it out, Thanks anyway.

    CREATE TABLE [dbo].[EventChildren]
(
[EventId] [uniqueidentifier] NOT NULL,
[EventChildId] [uniqueidentifier] NOT NULL,
[Enabled] [bit] NOT NULL,
CONSTRAINT [EventEvents] PRIMARY KEY CLUSTERED 
(
    [EventId] ASC,
    [EventChildId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[EventChildren]  WITH CHECK ADD  CONSTRAINT [FK_EventChildren_Event] FOREIGN KEY([EventId])
REFERENCES [dbo].[Event] ([step_id])
GO

ALTER TABLE [dbo].[EventChildren]  WITH CHECK ADD  CONSTRAINT [FK_EventChildren_EventChild] FOREIGN KEY([EventChildId])
REFERENCES [dbo].[Event] ([step_id])
GO

ALTER TABLE [dbo].[EventChildren] CHECK CONSTRAINT [FK_EventChildren_Event]
GO
ALTER TABLE [dbo].[EventChildren] CHECK CONSTRAINT [FK_EventChildren_EventChild]
GO

Composite primary key with foreign key relationships to same table

1 Answers1