Mysql 4.0.17 contraint problem !

[kmthien]

Hi,


I created 2 tables as follows:

create table employee (id varchar(30) Primary key NOT NULL,
department_id tinyint unsigned NOT NULL,
designation_id tinyint unsigned NOT NULL,
location varchar(30) NOT NULL,
INDEX (id),
INDEX (department_id),
INDEX (designation_id),
INDEX (location),
FOREIGN KEY (department_id) REFERENCES department(id),
FOREIGN KEY (designation_id) REFERENCES designation(id),
FOREIGN KEY (location) REFERENCES company_branch(code),
) TYPE=INNODB;

============================================

create table emp_contact (id varchar(30) NOT NULL,
info VARCHAR(50) NOT NULL,
type varchar(10) NOT NULL,
INDEX (id),
INDEX (type),
FOREIGN KEY (id) REFERENCES employee(id) ON DELETE CASCADE,
FOREIGN KEY (type) REFERENCES emp_contact_type(id)
) TYPE=INNODB;

============================================

where emp_contact id is reference to employee.id
I tried to insert emp_contact id = 2000, which is not inside employee.id, the transaction created successfully, where it shouldn't since there is no id = 2000 inside employee table.

Pls help, thanks !

 

[kmthien]

Pls help guys guys !!

 

[WildHorse]

A foreign key in one table points to the primary key in another table.

It looks like your table emp_contact is missing a primary key.

 

[kmthien]

Hi, so how should I solve this problem ?? Thanks for ur advise !!

 

[kmthien]

anyone ??