foreign keys 
Author Message
 foreign keys
from sql 2000 database design and implementation.

"A FOREIGN KEY constraint can reference columns in tables in the same
database or within the same table (self-referencing tables)."

What are self - referencing tables can anyone give examples ?

and how do i create a foreign key constraint that can reference columns
within the same table can anyone give an example ?



Sat, 28 Feb 2004 20:26:06 GMT
 foreign keys

The Employees table in the sample Northwind database has an example of this:

ALTER TABLE [dbo].[Employees] ADD CONSTRAINT [FK_Employees_Employees]
FOREIGN KEY
 (
  [ReportsTo]
 ) REFERENCES [Employees] (
  [EmployeeID]
 )
GO

To list employees and their manager (if any):

SELECT
 a.FirstName AS [Employee First Name],
 a.LastName AS [Employee Last Name],
 b.FirstName AS [Manager First Name],
 b.LastName AS [Manager Last Name]
FROM Employees a
LEFT JOIN Employees b ON b.EmployeeId = a.ReportsTo

--
Hope this helps.

-----------------------
SQL FAQ links (courtesy  Neil Pike):

 http://forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps
 (faqxxx.zip in lib 7)
 or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
 or www.sqlserverfaq.com
 or www.mssqlserver.com/faq
-----------------------


Quote:
> from sql 2000 database design and implementation.

> "A FOREIGN KEY constraint can reference columns in tables in the same
> database or within the same table (self-referencing tables)."

> What are self - referencing tables can anyone give examples ?

> and how do i create a foreign key constraint that can reference columns
> within the same table can anyone give an example ?



Sat, 28 Feb 2004 20:31:47 GMT
 foreign keys
Hi Zen,

Here's some example code you can run in Query Analyzer:

Create Table SelfRefTest (
  Id Int Not Null Primary Key,
  SelfRefFK Int Null References SelfRefTest (Id)
)

Insert Into SelfRefTest (Id, SelfRefFK) Values (1, NULL)
Insert Into SelfRefTest (Id, SelfRefFK) Values (2, 1)
Insert Into SelfRefTest (Id, SelfRefFK) Values (3, 1)
Insert Into SelfRefTest (Id, SelfRefFK) Values (4, 1)

-- This will fail:
Insert Into SelfRefTest (Id, SelfRefFK) Values (5, 0)

-- This won't fail:
Insert Into SelfRefTest (Id, SelfRefFK) Values (5, NULL)

Select * From SelfRefTest



Sat, 28 Feb 2004 22:32:45 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Trigger to check a foreign key without using foreign key

2. Trigger to check a foreign key without using foreign key

3. foreign key w/o foreign/primary key

4. Update primary key & Foreign key

5. Foreign Key as subset of Primary Key

6. How to create primary key/foreign key?

7. delete primary key records together with the foreign keys records

8. Update primary keys and foreign keys

9. Foreign key references to non-primary key columns

10. One FOREIGN KEY refrences a Table with two Primary Keys

11. Foreign Keys to Non-primary keys?

12. Primary key, Foreign key, referential integrity, etc?


 
Powered by phpBB® Forum Software