a design question

[junaid6t2]

Hi,

I have a design issue. Is it possible to declare a primary key of a table to be a foreign key as well? if its confusing please read on for an example of what I mean.

Suppose i have two tables, they both have the same primary key. Lets say that both tables have a primary key called member_id. The first table will store all the necessary info about a member and the second will have a foreign key referencing into the first table. so my question is: Would it be possible for the primary key of the second table to be a foreign key as well? (which well reference the primary key in the first table).

Thank you.
Junaid

 

[r937]

is it possible to declare a primary key of a table to be a foreign key as well?

certainly ;-)

 

[junaid6t2]

Thank you. Any ideas of what the syntax will be to create such a field? I'm having difficulty finding an example anywhere on the net.

Regards,
Junaid.

 

[r937]

in mysql, i wouldn't bother

it's just decoration, of little practical use

pity

 

[r937]

but to answer your question,

create table employeepersonneldetails
( empnumber integer not null primary key
, empfname varchar(50) null
, emplname varchar(50) null
)

create table employeehomelandsecurityinfo
( empnumber integer not null primary key
, fbiclearancelevel tinyint not null default 0
, ciaclearancelevel tinyint not null default 0
, foreign key empnumber
references employeepersonneldetails (empnumber)
)

not every row in the first table will have matching rows in the second -- in fact, there will only be a handful of rows in the second, for employees who have a clearance

this type of structure is usually deemed "better" than having those two fields in the first table, because even though they would have NULLs for most rows (note, 0 would be the wrong value!!), this will still "clutter" the first table and be prone to misinterpretation by people using just the first table

the empnumber in the second table is the primary key for its table, and foreign key to the first

make sense?

rudy

 

[junaid6t2]

Yes it does make sense. Can you please explain what you mean when you said it is just "decoration" in mysql. Do you mean that mysql doesn't automatically delete/update foreign key fields in the secondary table when the corresponding primary key fields are deleted/updated in the first table?

That is what my objective is, and if your example above can do that I am more than happy

Regards,
Junaid.

 

[r937]

that's exactly what i mean

if you declare a foreign key and get the syntax wrong, mysql will tell you, but if you get it right, nothing further will happen, mysql does not enforce referetial integrity!

here, check out the wimpy rationalization on their site: "Do keep in mind that foreign keys are often misused, which can cause severe problems. Even when used properly, it is not a magic solution for the referential integrity problem, although it does make things easier in some cases." (

http://www.mysql.com/doc/en/ANSI_diff_Foreign_Keys.html)

pity, eh?

 

[junaid6t2]

O well, this means I will need a few extra lines in my java program to deal with referential integrity.

I am new to mysql and have always heard about its realiability and ease of use. It comes as a surprise that such a well known database doesn't support referntial integrity. As you said, its a pity.

Thanks a bunch for your help.

Regards,
Junaid.