Foreign key in relational modal

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Foreign key in relational modal

Author

Topic

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-12-07 : 12:17:11

Tara says: Don't roll out a db without referential integrity. So here we go.
I have different tables in my database that have phone numbers (and different amounts of phone numbers).
So instead of having varying amounts of phonenumber columns in different tables, I opted for a phone numbers table.
Now I want to put a foreign key reference on my phone numbers table back to the 4 tables that use it.
Can this be done? The minute I try and create the 1st (of the 4 constraints) like this:

ALTER TABLE TelephoneNumber ADD CONSTRAINT
FK_TelephoneNumber_Company FOREIGN KEY
(
PHO_ParentID
) REFERENCES Company
(
CMY_CompanyID
)
GO

it fails because there are other records in the phone numbers table that map to other tables using the same column PHO_ParentID.
So this also fails:

ALTER TABLE TelephoneNumber ADD CONSTRAINT
FK_TelephoneNumber_Person FOREIGN KEY
(
PHO_ParentID
) REFERENCES Person
(
PEE_PersonID
)
GO

etc..

I do also have a PHO_ParentTypeEnum column which basically tells me which table that record is linked to.
Could the integrity check incorporate the PHO_ParentTypeEnum column somehow to get this to work? Or is there a simpler way?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-07 : 13:34:11

One approach is to create a PhoneNumber table with at least these 2 columns (phoneNunberID, PhoneNumber)

then in your other tables have a phoneNumberID column and a foreign key referencing phoneNumber (phonenumberid)

EDIT:
This is what I mean...


use northwind

create table phoneNumber
	(phoneid int identity(1,1) primary key clustered
	,phoneNumber varchar(50) not null unique)
go
create table person
	(personid int identity(1,1) primary Key clustered
	,LastName varchar(50) not null
	,phoneid int references PhoneNumber(phoneid))
create table company
	(companyid int identity(1,1) primary key clustered
	,companyname varchar(200) not null
	,phoneid int references PhoneNumber(phoneid))

go
drop table company
drop table person
go
drop table phoneNumber

Be One with the Optimizer
TG

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-12-07 : 14:27:16

No, that certainly wont work for me. And that's not relational at all!
If I have a phoneid column in company and person, i might as well not have a phoneNumber table and just put several phoneNumber columns in my company and person tables. you dont get much uglier than that.
Any other ideas? I cant believe I'm the first person to come accross this...

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-07 : 14:32:20

how about phonenumber table and association tables like personPhone, companyphone

Be One with the Optimizer
TG

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-12-07 : 14:48:27

I could do, but that seems a lot of work for something that I would have thought would be simpler & there's loads of code pointing at the existing structure. So I have to do the best I can with what I have. Everything is through SPs and deletes in the right order so as not to generate orphan records but I cant currently protect against accidental EM or Query Analyzer misuse.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-07 : 15:11:51

>>I cant currently protect against accidental EM or Query Analyzer misuse.

You could only allow access to that data via your existing SPs. ie: no direct table privs.

Be One with the Optimizer
TG

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-12-07 : 15:16:45

That's the plan. But I cant do that 100% for a while. And that wont cover dba mistakes.
"Proper" referential integrity should surely be the preferred route if possible. Are you saying it's not?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-07 : 15:56:51

>>Are you saying it's not?
of course not. I'm all for proper ri. I'm just throwin' out ideas.

I understand that you don't want to impact your system with structure changes which make the association tables undesirable. I don't think you are going to be able to come up with a CONSTRAINT for your current structure. So...

How about a "roll your own" constraint using a trigger on telephonenumber that checks the appropriate table for the parentID. btw, how do you know what the type of entity the parentid is?

Be One with the Optimizer
TG

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-12-08 : 04:20:33

quote:
Originally posted by TG

How about a "roll your own" constraint using a trigger on telephonenumber that checks the appropriate table for the parentID.

I guess that's my only option. I've never used triggers before. I'll have to look in to it.

quote:
Originally posted by TG

btw, how do you know what the type of entity the parentid is?

As I mentioned at the outset:
I do also have a PHO_ParentTypeEnum column which basically tells me which table that record is linked to.
But generally I dont even need it because when you join the two tables, you automatically only get the relevant matches.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-08 : 07:34:00

>>But generally I dont even need it because when you join the two tables, you automatically only get the relevant matches.
I see what you mean but I'd use the pho_parenttypeEnum in the joins anyway just in case a company and a person end up with the same ID.

Be One with the Optimizer
TG

jhermiz

3564 Posts

Posted - 2005-12-08 : 08:19:28

The original design posted already suffered since you are storing multiple attributes in several different tables.
All phone numbers should have been placed into one table with a foreign key pointing back to the "PhoneTypeID" of a phonenumber. PhoneTypeID categorizes your phone numbers.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-12-08 : 09:58:36

quote:
Originally posted by TG

>>But generally I dont even need it because when you join the two tables, you automatically only get the relevant matches.
I see what you mean but I'd use the pho_parenttypeEnum in the joins anyway just in case a company and a person end up with the same ID.

Be One with the Optimizer
TG

not possible. I use guids

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-12-08 : 10:00:18

quote:
Originally posted by jhermiz

The original design posted already suffered since you are storing multiple attributes in several different tables.
Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

no, i'm not

quote:
Originally posted by jhermiz

All phone numbers should have been placed into one table with a foreign key pointing back to the "PhoneTypeID" of a phonenumber. PhoneTypeID categorizes your phone numbers.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

that's what i'm doing

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources