Re: foreign keys deppending on two different tables in mysql
- From: Jim Dunleavy <jim.dunleavy@xxxxxx>
- To: programmingblind@xxxxxxxxxxxxx
- Date: Wed, 19 Sep 2007 17:12:28 +0100
Hi Marlon,
You could write a SQL function that returns a value dependant
on the existance of records in table a or b.
Then you could add a "check constraint" to table c e.g.:
alter table c add constraint c_check check (myfunc(altid) > 0);
Older versions of MySQL don't have user-defined SQL functions though.
HTH
--Jim
HTH
--Jim
----- Original Message -----
From: Marlon Brandão de Sousa <splyt.lists@xxxxxxxxx>
To: <programmingblind@xxxxxxxxxxxxx>
Sent: Tuesday, September 18, 2007 8:30 PM
Subject: foreign keys deppending on two different tables in mysql
> Hello,
> I have this situation:
> Table a has an id and a name field
> Table b has an id and a name field
> Table c has an id field, an external code field and a name field
> The external code field of table c must be an existing id in table a
> or in table b, thus it is a foreign key.
> I do know, for example, how to make the external code field from table
> c deppend on the id field of table b ... what I don't know is if it is
> possible to make it refer to the id field from table b or to the id
> field of table a.
> I know this would cause some ambiguity in a ondelete rule, but what I
> need is to guarantee that the external field from table c will accept
> only values present on table a or on table b.
> Do you know if this is possible and, if it is, how to do it in the
> mysql database?
> Sure I can implement this in the code but I was woundering if it is
> possible to build this kind of rule in the database.
> Thanks
> Marlon
> --
> When you say "I wrote a program that crashed Windows," people just
> stare at you blankly and say "Hey, I got those with the system, for
> free."
> Linus Torvalds
> __________
> View the list's information and change your settings at
> //www.freelists.org/list/programmingblind
>
----------------------------------------------------------------------------
"Information in this email (including attachments) is confidential.
It is intended for receipt and consideration only by the intended recipient.
If you are not an addressee or intended recipient, any use, dissemination,
distribution, disclosure, publication or copying of information contained in
this email is strictly prohibited. Opinions expressed in this email may be
personal to the author and are not necessarily the opinions of the HSE.
If this email has been received by you in error we would be grateful if you
could immediately notify the ICT Service Desk by telephone at +353 1 6352757
or by email to service.desk@xxxxxxxxxxxx and thereafter delete this
e-mail from your system"
----------------------------------------------------------------------------
__________
View the list's information and change your settings at
//www.freelists.org/list/programmingblind
Other related posts:
