REFERENCEing two columns to the same key

Hi

I'm just wondering whether SQL Server will let me link two columns in one table to the same column in another table. Basically, my table looks like this:

BOOKINGS
-----
BookingID (PK)
BorrowerID (FK1)
ModuleID (FK2)
LecturerID (FK3)
CollectionDateTime
ReturnDateTime
Authorised
TakenUp

FK1 and FK3 are related to BorrowerID - both lecturers and students are stored in the same table because lecturers are able to book and borrow equipment as well as students. Will SQL Server let me create a Foreign Key on BorrowerID and LecturerID that both point to Borrowers.BorrowerID?

My understanding of Foreign Keys is that they're just a way of enforcing referential integrity, so they'll only come into play when data is being deleted that might cause orphaned rows in the dependent table. So there shouldn't be too much of a problem with this, is that right?

Thanks
JonYes, absolutely. SQL Server will let you link FK1 and FK3 to the same table. However, if you want to enforce referential integrity systematically (ie cascading updates and deletes), you will only be able to do so on one of the foreign keys. With the other foreign key you will have to use a trigger. This is one of SQL's annoying "personalities" .

Terri|||Cool - thanks for your reply

You might be able to tell, but I'm quite a SQL novice. I'm guessing the trigger would be in effect on INSERTs and would check that one of the Foreign Keys is a valid value? Would it need to be effective on anything else? I guess UPDATES, but what about DELETES?

Great, now I have to learn triggers!!!

Cheers
Jon|||This KB article describes the problem you will encounter:PRB: Error Message 1785 Occurs When You Create a FOREIGN KEY Constraint That May Cause Multiple Cascade Paths

So, in your case you cannot use what is referred to as Declarative Referential Integrity (DRI).

This is an area where I am not particularly knowledgeable. I have not used DRI nor triggers for referential integrity. For better or worse, the stored procedure doing the data modification is where I have put such code.

Terri

reference to either of two tables

I must link from one table to either of two tables X & Y. I can store
this link in columns:
REF_X_ID NULLABLE
REF_Y_ID NULLABLE
plus a constraint to ensure only one REF_*_ID is set for a row
OR
in columns:
REF_ID NOT NULL
WHICH_TABLE
i.e. store ID for both tables in only one column, and add an other
column that identifies which table REF_ID refers.
Which would be smarter idea considering table design, adding rows and
quering the table?On 23 Apr 2005 14:31:30 -0700, bbla32@.op.pl wrote:

>I must link from one table to either of two tables X & Y. I can store
>this link in columns:
>REF_X_ID NULLABLE
>REF_Y_ID NULLABLE
>plus a constraint to ensure only one REF_*_ID is set for a row
>OR
>in columns:
>REF_ID NOT NULL
>WHICH_TABLE
>i.e. store ID for both tables in only one column, and add an other
>column that identifies which table REF_ID refers.
>Which would be smarter idea considering table design, adding rows and
>quering the table?
Hi bbla32,
Since you can't use a FOREIGN KEY constraint on the latter and you can
use it on the former, I'd definitely favor the first version.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Re-establishing link to SQL from ACCESS XP (wireless)

Hi,
I am developing an ACCESS ADP that links to MS SQL, the SQL is on a server,
the ACCESS links to it via a wireless network. When it goes out of wireless
range, I will lose the link to the database. Is there a standard way to hav
e
it re-link effortlessly (quickly) when it comes back into range. I don't
want the operation to have to do anything.
Thanks,
SteveYou can write code to relink the tables. The Knowledge Base
used to have sample code to do this, the Access FAQ has
sample code - http://www.mvps.org/access/
-Sue
On Mon, 10 Apr 2006 13:40:02 -0700, SteveInBeloit
<SteveInBeloit@.discussions.microsoft.com> wrote:

>Hi,
>I am developing an ACCESS ADP that links to MS SQL, the SQL is on a server,
>the ACCESS links to it via a wireless network. When it goes out of wireles
s
>range, I will lose the link to the database. Is there a standard way to ha
ve
>it re-link effortlessly (quickly) when it comes back into range. I don't
>want the operation to have to do anything.
>Thanks,
>Steve

Reducing many-to-many to one-to-one

Hi,

I have the following three tables below containing Resources,
Categories and a link table so each Resource can belong to one or more
Categories. I would like to create a view (ResourceID, ResourceName,
CategoryID, CategoryName) that includes one row for each Resource with
just one of the Categories that it belongs to.

Resource table
- ResourceID
- ResourceName
- etc..

Category table
- CategoryID
- CategoryName
- etc..

ResourceCategory table
- ResourceID
- CategoryID

Can anyone help? Thanks.On Fri, 14 Sep 2007 14:43:33 -0000, nick@.nova5.net wrote:

Quote:

Originally Posted by

>Hi,
>
>I have the following three tables below containing Resources,
>Categories and a link table so each Resource can belong to one or more
>Categories. I would like to create a view (ResourceID, ResourceName,
>CategoryID, CategoryName) that includes one row for each Resource with
>just one of the Categories that it belongs to.

Hi Nick,

That's only possible if you somehow specify WHICH of the categories you
want. Lowest CategoryID? Longest CategoryName? CategoryID closest to a
multiple of 42?

I'm sure that you don't care, but since SQL Server has no "just give me
any I don't care which" operator, you'll have to specify something.

Assuming you want the lowest CategoryID, you can use the following on
SQL Server 7.0 and up (on SQL 2005, you might find a better solution
with CTEs and CROSS APPLY, but since you failed to specify the version,
I'll play it safe):

CREATE VIEW YourShinyNewView
AS
SELECT r.ResourceID, r.ResourceName,
c.CategoryID, c.CategoryName
FROM Resource AS r
INNER JOIN (SELECT Resource, MIN(CategoryID) AS MinCategoryID
FROM ResourceCategory
GROUP BY Resource) AS rc
ON rc.ResourceID = r.ResourceID
INNER JOIN Category AS c
ON c.CategoryID = rc.MinCategoryID;

(Untested - see www.aspfaq.com/5006 if you prefer a tested reply)

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||Hi Hugo,

Thanks for help, just what I needed. I was trying to put a 'top 1' in
the nested select which obviously (now!) didn't work..

Nick.

Quote:

Originally Posted by

Hi Nick,
>
That's only possible if you somehow specify WHICH of the categories you
want. Lowest CategoryID? Longest CategoryName? CategoryID closest to a
multiple of 42?
>
I'm sure that you don't care, but since SQL Server has no "just give me
any I don't care which" operator, you'll have to specify something.
>
Assuming you want the lowest CategoryID, you can use the following on
SQL Server 7.0 and up (on SQL 2005, you might find a better solution
with CTEs and CROSS APPLY, but since you failed to specify the version,
I'll play it safe):
>
CREATE VIEW YourShinyNewView
AS
SELECT r.ResourceID, r.ResourceName,
c.CategoryID, c.CategoryName
FROM Resource AS r
INNER JOIN (SELECT Resource, MIN(CategoryID) AS MinCategoryID
FROM ResourceCategory
GROUP BY Resource) AS rc
ON rc.ResourceID = r.ResourceID
INNER JOIN Category AS c
ON c.CategoryID = rc.MinCategoryID;
>
(Untested - seewww.aspfaq.com/5006if you prefer a tested reply)
>
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis