Re: Foreign Keys Dont Work

[Radu Chiriac]

Oh, yes, they do! :)

btw, make up your mind - do u really want to set master.dept_id to null on parent record deletion or or want this field to be not null?

if you want it to be not null then:

create table master (
admission_id int not null,
adm_start_date datetime not null,
adm_end_date datetime,
admission_class varchar(20),
dept_id int not null,
ward_no int default 0,
primary key (admission_id),
constraint admission_dept foreign key (dept_id) references dept1(dept_id)
) ENGINE=InnoDB;

if u want it to be not null and on parent record deletion to delete automatically the record from the child table:

create table master (
admission_id int not null,
adm_start_date datetime not null,
adm_end_date datetime,
admission_class varchar(20),
dept_id int not null,
ward_no int default 0,
primary key (admission_id),
constraint admission_dept foreign key (dept_id) references dept1(dept_id) on delete cascade
) ENGINE=InnoDB;


And finally, if u want the field to accept null values and to be set to null on delete from parent table:
create table master (
admission_id int not null,
adm_start_date datetime not null,
adm_end_date datetime,
admission_class varchar(20),
dept_id int,
ward_no int default 0,
primary key (admission_id),
constraint admission_dept foreign key (dept_id) references dept1(dept_id) on delete set null
) ENGINE=InnoDB;


Have fun with mysql :)
Radu