October 13, 2009 at 3:11 pm
DDL Sample:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PersonCredit](
[PersonCreditUID] [uniqueidentifier] NOT NULL, --clustered index
[PersonUID] [uniqueidentifier] NOT NULL,
...
CONSTRAINT [PK_PersonCredit] PRIMARY KEY CLUSTERED
(
[PersonCreditUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [DataFileGroup]
) ON [DataFileGroup] TEXTIMAGE_ON [TextFileGroup]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[PersonCredit] WITH CHECK ADD CONSTRAINT [FK__PersonCre__Perso__3651FAE7] FOREIGN KEY([PersonUID])
REFERENCES [dbo].[Person] ([PersonUID])
GO
ALTER TABLE [dbo].[PersonCredit] CHECK CONSTRAINT [FK__PersonCre__Perso__3651FAE7]
GO
ALTER TABLE [dbo].[PersonCredit] WITH CHECK ADD CONSTRAINT [FK__PersonCre__Perso__4B4D17CD] FOREIGN KEY([PersonUID])
REFERENCES [dbo].[Person] ([PersonUID])
GO
ALTER TABLE [dbo].[PersonCredit] CHECK CONSTRAINT [FK__PersonCre__Perso__4B4D17CD]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Person](
[PersonUID] [uniqueidentifier] NOT NULL,
[SeqID] [int] IDENTITY(1,1) NOT NULL, --Clustered Index created on this column
...
CONSTRAINT [PK_Person] PRIMARY KEY NONCLUSTERED
(
[PersonUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [DataFileGroup]
) ON [DataFileGroup]
GO
SET ANSI_PADDING OFF
GO
This is something similar to what I have inherited. I am trying to figure out why I might need two FKs from PersonCredit to Person on the PersonUID column. If they were different columns I might buy that. I don't see the reason for two foreign keys to the same column from the same table though.
I am hoping that somebody out there might now something different and can enlighten me.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 13, 2009 at 11:18 pm
There is no good reason can think of, and since the constraint names in your case appear to be system generate my bet would be that at some point a script containing an alter table.. add constraint statement was simply run twice - and hence two identical constraints.
I would treat this as another good reason for always explcitly naming constraints and delete one of them.
Mike John
October 14, 2009 at 12:39 am
Agreed..
CEWII
October 14, 2009 at 9:18 am
This is as I figured. I could think of no sound reason for this type of setup. When I checked the original creation script for this table, both of the FKs were there as well. It seemed really absurd but I figured there must have been some sort of obscure reason and maybe somebody might know that reason.
Thanks
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply