You can't do it efficiently with foreign key relationships, but you can do
through triggers. Create an insert trigger that checks the value of
[assigned_to] and verifies that it exists in one of the two tables. If it
does not, throw the error and rollback the transaction. You'll also need
an update trigger for when a row is modified and you might want to consider
triggers to restrict or cascade changes that take place in the [Users] and
[Groups] tables.
Note that there are some problems with this:
1. Since you're only using one column, you have no way to know
whether your [assigned_to] value should be looked up in the [Users] table
or the [Groups] table.
2. You can only ever insert or update one row at a time, unless
you're using Oracle and implement your triggers using row-level triggers,
or using a cursor on any other DBMS. Cursors will work, but they are very
inefficient and can have a significant performance impact if you have a lot
of activity on the [Task] table.
You can get around #1 by implementing more than one "Assigned To" column:
[Task]
[Task_ID]
[Task_Desc]
[Assigned_User_ID]
[Assigned_Group_ID]
Your triggers then check that only one of those two columns has a value, in
addition to making sure that the value exists in the appropriate parent
table. I have something similar in one of my databases that keeps track of
Contact Information for network resources:
create table ContactInfo (
TypeID int not null,
PersonID int not null,
AccountID int not null,
GroupID int not null,
ProjectID int not null,
ComputerID int not null,
DomainID int not null,
Comments text null,
CreateDate datetime NOT Null,
CreatedBy varchar(128) not null,
ModifyDate datetime null,
ModifiedBy varchar(128) null) ON 'PRIMARY' TEXTIMAGE_ON 'PRIMARY'
go
alter table ContactInfo
add constraint ContactInfo_PK primary key nonclustered (TypeID,
PersonID, AccountID, GroupID, ProjectID, ComputerID, DomainID)
go
|-----------+-------------------------------------------------------------|
|Eric A. |"If time be of all things most precious, wasting time must be|
|Laney |the greatest prodigality, since lost time is never found |
|Systems |again; and what we call time enough always proves little |
|Architect |enough." |
|Verizon | |
|Information| |
|Security | Benjamin Franklin|
|Voice: | |
|813.293.309| |
|3 | |
|Pager: | |
|888.551.371| |
|8 | |
|-----------+-------------------------------------------------------------|
|---------+--------------------------->
| | Manish Bansal |
| | <manish@myrealbo|
| | x.com> |
| | |
| | 01/10/2002 12:09|
| | AM |
| | Please respond |
| | to sql |
| | |
|---------+--------------------------->
>---------------------------------------------------------------------------------------------------------------|
|
|
| To: SQL <[EMAIL PROTECTED]>
|
| cc:
|
| Subject: referential integrity question
|
>---------------------------------------------------------------------------------------------------------------|
Hello all
I have three tables
[Users]
user_id
user_name
[Groups]
group_id
group_name
[Task]
task_id
task_desc
assigned_to (should contain an id which can be user_id or group_id)
The problem is i want to enforce referential integrity on the assigned_to
field in the TASK table.
Obviously a foreign key can refer to only one table. Is there a way to
solve this issue without creating another table containing the records from
the USERS and GROUPS tables ?
Thanks
Manish
______________________________________________________________________
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
