Foreign key with more columns and a constant value

Hi,

I have the following table:

create table client(
id int unsigned not null auto_increment primary key,
name varchar(200),
type1 int unsigned not null,
type2 int unsigned not null,
constraint foreign key(type1, type2) references constants(id, type)
) engine=InnoDB;

This table is OK, but the column type2 contains a unique value for all the
records from this table, let's say the value "1".

Is it possible to remove that column and use a definition like the following
that uses the constant value 1?

create table client(
id int unsigned not null auto_increment primary key,
name varchar(200),
type1 int unsigned not null,
constraint foreign key(type1, 1) references constants(id, type)
) engine=InnoDB;

If I use this table format, it gives an error although it is strange that
MySQL can't use that constant value instead of a column name.

I have more tables that have foreign keys which reference the table
constants and in this table the IDs of the constants are not unique alone,
but only in combination with the column "type". This is why I need to use a
foreign key with 2 columns.

Is there a solution for what I want, or I will need to add that extra column
with unique values in all the tables that reference the table `constants`?

Thank you.

Octavian



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Foreign key with more columns and a constant value

--00504501601658f0ba04935b0cc2
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

The idea of a foreign key is that is is, well, a *foreign key* :-) It's
meant to match up data that is in one table with data that is in another
table, and a constant obviously isn't data in your table. To be precise,
what you specify in your constraint are not even fields, but *indices* - an=
d
a constant is not an index field.

I'm afraid you're stuck with that particular column, if you really need it.





On Sat, Oct 23, 2010 at 7:23 PM, Octavian Râºniþã om>wrote:

> Hi,
>
> I have the following table:
>
> create table client(
> id int unsigned not null auto_increment primary key,
> name varchar(200),
> type1 int unsigned not null,
> type2 int unsigned not null,
> constraint foreign key(type1, type2) references constants(id, type)
> ) engine=3DInnoDB;
>
> This table is OK, but the column type2 contains a unique value for all th=
e
> records from this table, let's say the value "1".
>
> Is it possible to remove that column and use a definition like the
> following
> that uses the constant value 1?
>
> create table client(
> id int unsigned not null auto_increment primary key,
> name varchar(200),
> type1 int unsigned not null,
> constraint foreign key(type1, 1) references constants(id, type)
> ) engine=3DInnoDB;
>
> If I use this table format, it gives an error although it is strange that
> MySQL can't use that constant value instead of a column name.
>
> I have more tables that have foreign keys which reference the table
> constants and in this table the IDs of the constants are not unique alone=
,
> but only in combination with the column "type". This is why I need to use=
a
> foreign key with 2 columns.
>
> Is there a solution for what I want, or I will need to add that extra
> column
> with unique values in all the tables that reference the table `constants`=
?
>
> Thank you.
>
> Octavian
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dvegivamp@tuxera.b=
e
>
>


--=20
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--00504501601658f0ba04935b0cc2--