List:General Discussion« Previous MessageNext Message »
From:Johan De Meersman Date:October 24 2010 11:19am
Subject:Re: Foreign key with more columns and a constant value
View as plain text  
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* - and
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þã
<orasnita@stripped>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=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=1
>
>


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

Thread
Foreign key with more columns and a constant valueorasnita23 Oct
  • Re: Foreign key with more columns and a constant valueJohan De Meersman24 Oct