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.
Recommended for you: Get network issues from WhatsUp Gold. Not end users.
Answer Source
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)
);
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download