List:General Discussion« Previous MessageNext Message »
From:Octavian Râºniþã Date:October 23 2010 5:23pm
Subject:Foreign key with more columns and a constant value
View as plain text  
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


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