471,108 Members | 1,264 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,108 software developers and data experts.

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
Jul 20 '05 #1
3 2039
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

Jul 20 '05 #2
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
Jul 20 '05 #3
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.
Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Jeremiah Jacks | last post: by
5 posts views Thread by Barbara Lindsey | last post: by
5 posts views Thread by Peter Erickson | last post: by
2 posts views Thread by Ian Davies | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.