Can't see why it does not work

Question

I'm trying to create a few tables and I can't manage to get my keys to work properly.

Create table FAQ (

 id int(10) PRIMARY KEY AUTO_INCREMENT,

 question text,

 answer text

);

Create table templates (

 id int(10) AUTO_INCREMENT,

 name varchar(80),

 value varchar(30),

 sql_db text,

 sql_table text,

 Primary Key(id,name,value)

);

Create table clientes(

 id int(10) AUTO_INCREMENT,

 nome varchar(80),

 email varchar(30),

 website varchar(80),

 template_name varchar(80),

 template_value varchar(30),

 modo varchar(10),

 data datetime,

 Primary Key (id),

 FOREIGN KEY (template_name) REFERENCES templates(name),

 FOREIGN KEY (template_value) REFERENCES templates(value)

);

I just want to make simple relations between those 2 tables.

Answer 1

The immediate answer to your question is that a foreign key needs to reference all columns a unique key. That also requires a unique index on the two columns, so it should look like:

Create table FAQ (
  id int(10) PRIMARY KEY AUTO_INCREMENT,
  question text,
  answer text
);

Create table templates (
  id int(10) AUTO_INCREMENT,
  name varchar(80),
  value varchar(30),
  sql_db text,
  sql_table text,
  Primary Key(id),
  Unique (name, value)
);

Create table clientes (
  id int(10) AUTO_INCREMENT,
  nome varchar(80),
  email varchar(30),
  website varchar(80),
  template_name varchar(80),
  template_value varchar(30),
  modo varchar(10),
  data datetime,
  Primary Key (id),
  FOREIGN KEY (template_name, template_value) REFERENCES templates(name, value)
);

However, it is better to use the primary key (the auto-incremented id). Then you look up the name and value using join:

Create table clientes (
  id int(10) AUTO_INCREMENT,
  nome varchar(80),
  email varchar(30),
  website varchar(80),
  template_id int,
  modo varchar(10),
  data datetime,
  Primary Key (id),
  FOREIGN KEY (template_id) REFERENCES templates(id)
);

Can't see why it does not work

Sign up