Prev: Reference architecture for mobile data acquisition?
Next: SQL 2005EE - 64 memory question
From: Ross on 27 Apr 2010 09:25
Hi,

I have been reviewing the database objects in one of our development team's
databases and have found something that i haven't seen before.

There is a foreign key on one of the tables that has been created on the
same column that it references i.e

ALTER TABLE [dbo].[tbl_Address] WITH CHECK ADD CONSTRAINT
[FK_tbl_Address_tbl_Address] FOREIGN KEY([ID])
REFERENCES [dbo].[tbl_Address] ([ID])
GO

The ID column is an identity column.

Is there a point to doing this, or is it a mistake? I can only surmise that
the system will act as if it is not there at all.

Any light shed on this matter would be greatly appreciated,

Ross


From: Plamen Ratchev on 27 Apr 2010 10:54
This seems like a mistake, it does not make sense to reference the same column.

--
Plamen Ratchev
http://www.SQLStudio.com
From: Erland Sommarskog on 27 Apr 2010 18:18
Ross (Ross(a)discussions.microsoft.com) writes:
> I have been reviewing the database objects in one of our development
> team's databases and have found something that i haven't seen before.
>
> There is a foreign key on one of the tables that has been created on the
> same column that it references i.e
>
> ALTER TABLE [dbo].[tbl_Address] WITH CHECK ADD CONSTRAINT
> [FK_tbl_Address_tbl_Address] FOREIGN KEY([ID])
> REFERENCES [dbo].[tbl_Address] ([ID])
> GO
>
> The ID column is an identity column.
>
> Is there a point to doing this, or is it a mistake? I can only surmise
> that the system will act as if it is not there at all.

No, that does not make sense.

I have however fully conciously defined a table where the PK is an FK
to itself. The difference to the table above is that my table has a
two-column key.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: Reference architecture for mobile data acquisition?
Next: SQL 2005EE - 64 memory question