17 messages in com.mysql.lists.mysqlRe: Foreign Key Constraints
FromSent OnAttachments
kyu...@spymac.com26 May 2004 21:22 
Jeff Smelser26 May 2004 21:32 
Colin Bull27 May 2004 00:42 
Robert J Taylor27 May 2004 00:56 
Martijn Tonies27 May 2004 00:59 
SGr...@unimin.com27 May 2004 06:23 
Jeff Smelser27 May 2004 07:23 
Martijn Tonies27 May 2004 07:55 
Martijn Tonies28 May 2004 00:57 
Jeff Smelser28 May 2004 08:01 
Martijn Tonies28 May 2004 08:42 
Michael Stassen28 May 2004 09:25 
Michael Stassen28 May 2004 09:44 
Martijn Tonies28 May 2004 09:49 
Jeff Smelser28 May 2004 09:55 
Jeff Smelser28 May 2004 09:58 
Martijn Tonies28 May 2004 10:29 
Subject:Re: Foreign Key Constraints
From:Jeff Smelser (trad@smelser.org)
Date:05/28/2004 08:01:44 AM
List:com.mysql.lists.mysql

-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1

On Friday 28 May 2004 02:57 am, Martijn Tonies wrote:

Hi,

ok - I've checked.

Why not? What's wrong with this:

BORROWER BorrowerID

BOOKS BookID BorrowerID (nullable)

FK from Books.BorrowerID to Borrower.BorrowerID

I haven't checked, but this _should_ be possible.

With regards,

Its a foreign key, you can not null foreign keys.. Thats the problem.

That's not true. Here's what I got:

CREATE TABLE inno1 ( PK_Col Integer NOT NULL DEFAULT 0, Child_Col Integer, PRIMARY KEY ( PK_Col ) ) TYPE=InnoDB ROW_FORMAT=fixed DEFAULT;

CREATE INDEX I_Inno1_ChildCol ON inno1(Child_Col);

ALTER TABLE inno1 ADD FOREIGN KEY (Child_Col) REFERENCES inno1 (PK_Col) ON DELETE NO ACTION ON UPDATE NO ACTION;

After that, I inserted data: INSERT INTO inno1(PK_Col, Child_Col) VALUES (1, NULL); INSERT INTO inno1(PK_Col, Child_Col) VALUES (2, NULL); INSERT INTO inno1(PK_Col, Child_Col) VALUES (3, 1);

Now, can someone explain what the problem with NULLable FKs is?

With regards,

Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL Server. Upscene Productions http://www.upscene.com

CREATE TABLE inno2 (   PK_Col    Integer NOT NULL DEFAULT 0,   Child_Col Integer,   PRIMARY KEY (PK_Col) ) TYPE=InnoDB ;

CREATE INDEX I_Inno2_ChildCol  ON inno2(Child_Col);

CREATE TABLE inno3 (   PK_Col    Integer NOT NULL DEFAULT 0,   Child_Col Integer,   PRIMARY KEY (PK_Col) ) TYPE=InnoDB ;

ALTER TABLE inno3 ADD FOREIGN KEY (Pk_Col)   REFERENCES inno2 (Child_Col)   ON DELETE NO ACTION   ON UPDATE NO ACTION;

INSERT INTO inno2(PK_Col, Child_Col) VALUES (1, NULL); INSERT INTO inno2(PK_Col, Child_Col) VALUES (2, NULL); INSERT INTO inno2(PK_Col, Child_Col) VALUES (3, 1);

INSERT INTO inno3(PK_Col, Child_Col) VALUES (1, NULL); INSERT INTO inno3(PK_Col, Child_Col) VALUES (2, NULL); INSERT INTO inno3(PK_Col, Child_Col) VALUES (3, NULL);

select * from inno2;

The actual way he was doing it was above.. I am going to have look into this more since as you can see, this worked and considering I do not have a id 2 or 3.. it should have failed.. so something isn't right.. The entire point behind foreign keys is for constraints.. Its been awhile since I have done foreign keys on mysql...

- -- Enough research will tend to support your theory. -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.4 (GNU/Linux)

iD8DBQFAt1Ruld4MRA3gEwYRAvWEAKCcCqIsKLIPZk3od7Vn8z3rA9zAbACfZYhL 4VQLUYacl2HR9rmaBZC/pvw= =yiUm -----END PGP SIGNATURE-----