Re: Getting foreign keys. - Mofeed Shahin - com.mysql.lists.java

18 messages in com.mysql.lists.javaRe: Getting foreign keys.

From	Sent On	Attachments
Mofeed Shahin	11 Dec 2003 22:44
Jeff Newmiller	12 Dec 2003 10:20
Chris McGowan	12 Dec 2003 10:38
Mark Matthews	12 Dec 2003 10:55
Chris McGowan	12 Dec 2003 11:52
Mofeed Shahin	14 Dec 2003 15:08
Christian Mack	17 Dec 2003 10:02
Mofeed Shahin	17 Dec 2003 14:31
Susidzaimah Pg Sulaiman	30 Jan 2004 00:38
Scott Queen	30 Jan 2004 06:43
Mike Markovich	30 Jan 2004 07:12
Susidzaimah Pg Sulaiman	31 Jan 2004 17:26
Susidzaimah Pg Sulaiman	31 Jan 2004 17:27
Scott Queen	31 Jan 2004 19:07
Keith Hatton	02 Feb 2004 01:26
Mike Markovich	02 Feb 2004 07:49
Susidzaimah Pg Sulaiman	02 Feb 2004 23:40
Susidzaimah Pg Sulaiman	03 Feb 2004 19:01

Subject:	Re: Getting foreign keys.
From:	Mofeed Shahin (mofe...@dsto.defence.gov.au)
Date:	12/17/2003 02:31:22 PM
List:	com.mysql.lists.java

I'm pretty sure I'm using the InnoDB now. I did a SHOW INNODB STATUS and it gave me the error message. See my other email I just sent to the list for the actual message.

Mof.

On Thu, 18 Dec 2003 04:32 am, Christian Mack wrote:

Did you enable InnoDB in your my.ini/my.cnf file? If not, MySQL will go back to it's default (= MyISAM). InnoDB support is only compiled in by default as in 3.23.x-max versions.

Bye Christian

Mofeed Shahin wrote:

Thanks for that guys, but I am still having problems. My new create statements :

CREATE TABLE Student ( Student_ID INT PRIMARY KEY, First_Name varchar(50), Last_Name varchar(50), UNIQUE (First_Name, Last_Name) ) TYPE=INNODB;

CREATE TABLE Address ( First_Name varchar(50), Last_Name varchar(50), Address varchar(50), PRIMARY KEY (First_Name, Last_Name), FOREIGN KEY (First_Name, Last_Name) REFERENCES student (First_Name, Last_Name) ) TYPE=INNODB;

Then in mysql I do 'show create table Address' and it gives me this : | Address | CREATE TABLE `Address` (

`First_Name` varchar(50) NOT NULL default '', `Last_Name` varchar(50) NOT NULL default '', `Address` varchar(50) default NULL, PRIMARY KEY (`First_Name`,`Last_Name`) ) TYPE=MyISAM

Why did it go back to MyISAM!?!?!?!

I'm using 4.0.15 (Linux Mandrake). I was reading the docs, and they said the InnoDB was now the default database in v4 of MySQL. So what happened here ?

Mof.

On Sat, 13 Dec 2003 06:22 am, Chris McGowan wrote:

Mofeed (and Mark),

Mark was right and I downloaded J/Connector 3.0.9. (My original tested failed with 3.0.8).

You should be able to get it working now. Try creating these tables ######################### CREATE TABLE parent ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(25) ) TYPE=INNODB; CREATE TABLE child ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, parent_id INT, name VARCHAR(25), INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE SET NULL ) TYPE=INNODB; #########################

I then obtained a connection and ran your original Java code (with modifed table names): ######################### private static void test(Connection con) throws SQLException { DatabaseMetaData meta = con.getMetaData (); ResultSet primRS = meta.getPrimaryKeys (null, null, "Parent"); while (primRS.next ()) { System.out.println("column key == " + primRS.getString("COLUMN_NAME")); } ResultSet metaRS = meta.getImportedKeys(null, null, "child"); while (metaRS.next()) { System.out.println("foreign key == " + metaRS.getString("FK_NAME")); } } #########################

The Java output was: column key == id foreign key == 0_173

I don't understand the "0_173" value, but I guess that it has something to do with how MySQL stores innoDB tables and hashes. I'm unaware of ways to actually name a foreign key and have it show when using DatabaseMetaData.getString("FK_NAME"). I hope this helped.