February 10, 2007 at 3:43 pm
Hi,
I have a question about the MEANING of a table column 'PriorLeaseID' which is a FOREIGN key of table 'lease' and then the column references to the same table itself 'lease':
ALTER TABLE [dbo].[Lease] WITH NOCHECK ADD FOREIGN KEY([PriorLeaseID])
REFERENCES [dbo].[Lease] ([leaID])
Thanks in advance
February 10, 2007 at 5:24 pm
Are you really asking us for the meaning of data in your database?
We can make guesses, but how would we really know?
February 12, 2007 at 7:10 am
AS Posted by Micheal, you are not clear on what you want to know. However from your post LeaID is your Primary Key of Lease table and PriorLeaseID is a foreign key reference to LeaID of the same table.
This could be defined this way when you may be having multi-tier leases. In the first tier your PriorLeaseID would be null. If you are renewing your lease and want to add a new record with also maintaining a relationship with the previous lease record you can reference the Primary key, this case LeaID in PriorLeaseID column.
You may want to use a query something like this
SELECT L1.colName AS PrimaryLease,L2.ColName AS RenewedLease
FROM
dbo.[Lease] L1
INNER JOIN
dbo.[Lease] L2 ON L1.LeaID = L2.PriorLeaseID
Prasad Bhogadi
www.inforaise.com
February 12, 2007 at 7:13 am
It can also be termed as Master and Child records residing on the Same table. One LeaID can be referenced by Multiple PriorLeaseIDs .
Prasad Bhogadi
www.inforaise.com
February 13, 2007 at 4:09 pm
This is somtimes known as a fishhook join/relation, since the line representing the relation can be shaped like a fishhook. The classic example is:
CREATE TABLE Employee2
(EmpID INT PRIMARY KEY,
FName VARCHAR(30),
LName VARCHAR(30),
Title VARCHAR(15),
MgrID INT REFERENCES Employee2(EmpID)
)
INSERT INTO Employee2
VALUES(1,'Bob','Smith','President',NULL)
INSERT INTO Employee2
VALUES(2,'Jane','Doe','CFO',1)
INSERT INTO Employee2
VALUES(3,'Jane','Smythe','CIO',1)
INSERT INTO Employee2
VALUES(4,'Joe','Doe','Network Admin',2)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply