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þã <orasn...@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=vegiv...@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
