-----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-----
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
