Creating Foreign Key Constraint

[bk3]

I'm having a bit of a problem creating foreign keys on a MySQL database.
The tables create and everything seems to be fine. But I just wanted to
test out adding a row to one of the child tables without having anything in
the parent table and it lets me insert the row, which it shouldn't
obviously. How do i create it so that the constraints work?

Thanks,
Bill

Here is my create table code:

CREATE TABLE tblPhotographs
(PhotoID INT NOT NULL AUTO_INCREMENT,
Photo BLOB,
PhotoDate DATETIME,
PRIMARY KEY(PhotoID))
TYPE=InnoDB;

CREATE TABLE tblPeople
(PhotoID INT NOT NULL,
PersonID INT NOT NULL AUTO_INCREMENT,
Person VARCHAR(50),
INDEX FK_PhotoID(PhotoID),
FOREIGN KEY(PhotoID) REFERENCES tblPhotographs(PhotoID),
PRIMARY KEY(PersonID))
TYPE=InnoDB;

CREATE TABLE tblPlaces
(PhotoID INT NOT NULL,
PlaceID INT NOT NULL AUTO_INCREMENT,
Place VARCHAR(50),
INDEX FK_PhotoID(PhotoID),
FOREIGN KEY(PhotoID) REFERENCES tblPhotographs(PhotoID),
PRIMARY KEY(PlaceID))
TYPE=InnoDB;
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.772 / Virus Database: 519 - Release Date: 10/1/2004

[RP]

bk3 stuurde op 4-10-2004 8:28 het volgende bericht:

I'm having a bit of a problem creating foreign keys on a MySQL database.
The tables create and everything seems to be fine. But I just wanted to
test out adding a row to one of the child tables without having anything in
the parent table and it lets me insert the row, which it shouldn't
obviously. How do i create it so that the constraints work?

Thanks,
Bill

Here is my create table code:

CREATE TABLE tblPhotographs
(PhotoID INT NOT NULL AUTO_INCREMENT,
Photo BLOB,
PhotoDate DATETIME,
PRIMARY KEY(PhotoID))
TYPE=InnoDB;

CREATE TABLE tblPeople
(PhotoID INT NOT NULL,
PersonID INT NOT NULL AUTO_INCREMENT,
Person VARCHAR(50),
INDEX FK_PhotoID(PhotoID),
FOREIGN KEY(PhotoID) REFERENCES tblPhotographs(PhotoID),
PRIMARY KEY(PersonID))
TYPE=InnoDB;

CREATE TABLE tblPlaces
(PhotoID INT NOT NULL,
PlaceID INT NOT NULL AUTO_INCREMENT,
Place VARCHAR(50),
INDEX FK_PhotoID(PhotoID),
FOREIGN KEY(PhotoID) REFERENCES tblPhotographs(PhotoID),
PRIMARY KEY(PlaceID))
TYPE=InnoDB;

try adding the constraints ;-) :

FOREIGN KEY(PhotoID) REFERENCES tblPhotographs(PhotoID) ON DELETE
RESTRICT ON UPDATE CASCADE,

RP

[bk3]

Oh....I thought the FOREIGN KEY was the constraint...heh. Thanks. I'll
give that a try. :)
"RP" <do**@mail.me.please> wrote in message
news:cj**********@news1.zwoll1.ov.home.nl...

bk3 stuurde op 4-10-2004 8:28 het volgende bericht:

I'm having a bit of a problem creating foreign keys on a MySQL database.
The tables create and everything seems to be fine. But I just wanted to
test out adding a row to one of the child tables without having anything
in the parent table and it lets me insert the row, which it shouldn't
obviously. How do i create it so that the constraints work?

Thanks,
Bill

Here is my create table code:

CREATE TABLE tblPhotographs
(PhotoID INT NOT NULL AUTO_INCREMENT,
Photo BLOB,
PhotoDate DATETIME,
PRIMARY KEY(PhotoID))
TYPE=InnoDB;

CREATE TABLE tblPeople
(PhotoID INT NOT NULL,
PersonID INT NOT NULL AUTO_INCREMENT,
Person VARCHAR(50),
INDEX FK_PhotoID(PhotoID),
FOREIGN KEY(PhotoID) REFERENCES tblPhotographs(PhotoID),
PRIMARY KEY(PersonID))
TYPE=InnoDB;

CREATE TABLE tblPlaces
(PhotoID INT NOT NULL,
PlaceID INT NOT NULL AUTO_INCREMENT,
Place VARCHAR(50),
INDEX FK_PhotoID(PhotoID),
FOREIGN KEY(PhotoID) REFERENCES tblPhotographs(PhotoID),
PRIMARY KEY(PlaceID))
TYPE=InnoDB;

try adding the constraints ;-) :

FOREIGN KEY(PhotoID) REFERENCES tblPhotographs(PhotoID) ON DELETE
RESTRICT ON UPDATE CASCADE,

RP

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.772 / Virus Database: 519 - Release Date: 10/1/2004

[Bill Karwin]

bk3 wrote:

I'm having a bit of a problem creating foreign keys on a MySQL database.
The tables create and everything seems to be fine. But I just wanted to
test out adding a row to one of the child tables without having anything in
the parent table and it lets me insert the row, which it shouldn't
obviously. How do i create it so that the constraints work?

If you use MyISAM table types, referential integrity declarations are
parsed, but not enforced (or even stored).

See:
http://dev.mysql.com/doc/mysql/en/AN...eign_Keys.html

To get working constraints, you must use InnoDB table types.

Regards,
Bill K.