rui404 rui404 - 7 months ago 17
SQL Question

Can't see why it does not work

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

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)
);
Comments