[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql
Subject:    Re: Foreign key with more columns and a constant value
From:       Johan De Meersman <vegivamp () tuxera ! be>
Date:       2010-10-24 11:19:25
Message-ID: AANLkTin9uUgWRf_A78TF=qqWjaJenC+212ZiZN9Ec0hL () mail ! gmail ! com
[Download RAW message or body]


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@gmail.com>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=vegivamp@tuxera.be
>
>


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


[prev in list] [next in list] [prev in thread] [next in thread]