From: Johan De Meersman Date: October 24 2010 11:19am Subject: Re: Foreign key with more columns and a constant value List-Archive: http://lists.mysql.com/mysql/223427 Message-Id: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary=00504501601658f0ba04935b0cc2 --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=E2=BAni=FE=E3 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@stripped= 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--