I have a set of predefined constants with a set of predefined
relationships and am looking for a way to link them if possible to
ensure data integrity.

An example would be if I wanted to keep track of programs I have
written and I have a table Programs with rows operating system type
and programming language. If both operating system type and
programming language are foreign keys to reference tables (just tables
with a primary key and a name to the element they are referencing) is
there a way to ensure data integrity for possible nonsensical pairings
such as programming language c# and operating system unix?

I know that this question isn't specific to MySQL, but I am
implementing my database in MySQL and so a solution, if there is a
clean one must be implementable in MySQL.

Thanks in advance,
Jim Howard

On Oct 3, 5:47 pm, JamesHoward <James.w.How...@gmail.com> wrote:
> I have a set of predefined constants with a set of predefined
> relationships and am looking for a way to link them if possible to
> ensure data integrity.
>
> An example would be if I wanted to keep track of programs I have
> written and I have a table Programs with rows operating system type
> and programming language. If both operating system type and
> programming language are foreign keys to reference tables (just tables
> with a primary key and a name to the element they are referencing) is
> there a way to ensure data integrity for possible nonsensical pairings
> such as programming language c# and operating system unix?
>
> I know that this question isn't specific to MySQL, but I am
> implementing my database in MySQL and so a solution, if there is a
> clean one must be implementable in MySQL.
>
> Thanks in advance,
> Jim Howard

Sure -- create a fourth table that lists all the valid pairs of
operating systems and languages. This table would have foreign keys
to the table of operating systems and the table of programming
languages. Then, your programs table would have a foreign key to this
table instead (foreign key constraints can span multiple columns).

On Oct 3, 5:58 pm, ZeldorBlat <zeldorb...@gmail.com> wrote:
> On Oct 3, 5:47 pm, JamesHoward <James.w.How...@gmail.com> wrote:
>
>
>
> > I have a set of predefined constants with a set of predefined
> > relationships and am looking for a way to link them if possible to
> > ensure data integrity.
>
> > An example would be if I wanted to keep track of programs I have
> > written and I have a table Programs with rows operating system type
> > and programming language. If both operating system type and
> > programming language are foreign keys to reference tables (just tables
> > with a primary key and a name to the element they are referencing) is
> > there a way to ensure data integrity for possible nonsensical pairings
> > such as programming language c# and operating system unix?
>
> > I know that this question isn't specific to MySQL, but I am
> > implementing my database in MySQL and so a solution, if there is a
> > clean one must be implementable in MySQL.
>
> > Thanks in advance,
> > Jim Howard
>
> Sure -- create a fourth table that lists all the valid pairs of
> operating systems and languages. This table would have foreign keys
> to the table of operating systems and the table of programming
> languages. Then, your programs table would have a foreign key to this
> table instead (foreign key constraints can span multiple columns).

Thanks. I am going to give that a shot!

Jim