relationship between tables

i had to create tables using innodb type;i had to define relationships such
as one-to-many,many-to-many, one -to -one.can anyone suggest me how to
create tables of innodb type using foreign key constraints.I tried but i got
error 1005, cannot create table error 105.I trief it like this while trying
to create one to many relationship;



mysql>create table parent(id int,PRIMARY KEY(id)) type=innodb;
Query ok,0 rows affected,1 warning(0.19 secs)

mysql> create table child(cid int,pid int,PRIMARY KEY(cid),FOREIGN KEY(pid)
REFERENCES parent(id)) TYPE=INNODB;
ERROR 1005 (HY000): cant create table '.\library\child.frm' (error105)

where library is my database name;

please suggest me whats wrong above.also if you cant suggest me some links
where i can such relationship between table using innodb table type.

thank you.

____________________________________________________________ _____
Express yourself instantly with MSN Messenger! Download today it's FREE!
http://messenger.msn.click-url.com/go/onm00200471ave/direct/ 01/


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: relationship between tables

Hi,

I tested your create table statements on a server running MySQL 4.1.10a on=
Windows XP Pro and second one running MySQL 4.1.7 on Trustix. I found no=
problems.

I checked the MySQL website for InnoDB errors=
http://dev.mysql.com/doc/mysql/en/innodb-error-codes.html. It didn't shed=
any light on how to fix it except to say there was a problem with forming=
the constraint.

Marcel

*********** REPLY SEPARATOR ***********

On 20/03/2005 at 08:49 sony nakarmi wrote:

>i had to create tables using innodb type;i had to define relationships
>such
>as one-to-many,many-to-many, one -to -one.can anyone suggest me how to
>create tables of innodb type using foreign key constraints.I tried but i
>got
>error 1005, cannot create table error 105.I trief it like this while
>trying
>to create one to many relationship;
>
>
>
>mysql>create table parent(id int,PRIMARY KEY(id)) type=3Dinnodb;
>Query ok,0 rows affected,1 warning(0.19 secs)
>
>mysql> create table child(cid int,pid int,PRIMARY KEY(cid),FOREIGN
>KEY(pid)
>REFERENCES parent(id)) TYPE=3DINNODB;
>ERROR 1005 (HY000): cant create table '.\library\child.frm' (error105)
>
>where library is my database name;
>
>please suggest me whats wrong above.also if you cant suggest me some links=

>where i can such relationship between table using innodb table type.
>
>thank you.
>
>___________________________________________________________ ______
>Express yourself instantly with MSN Messenger! Download today it's FREE!
>http://messenger.msn.click-url.com/go/onm00200471ave/direct /01/
>
>
>--
>MySQL Windows Mailing List
>For list archives: http://lists.mysql.com/win32
>To unsubscribe:=
http://lists.mysql.com/win32?unsub=3Dlmforget2@rogers.com


Marcel


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32@m.gmane.org

Re: relationship between tables

--Boundary_(ID_KkKdfNm7P5yJ8CWdOcRsIQ)
Content-type: text/plain; charset=iso-8859-1
Content-transfer-encoding: 7BIT

Hi Sony, I might be able to help you on this. Just add an INDEX for the foreign-key field:

mysql> create table child(cid int,pid int,PRIMARY KEY(cid), INDEX(pid), FOREIGN KEY(pid)
REFERENCES parent(id)) TYPE=INNODB;



Jason


----- Original Message -----
From: sony nakarmi
To: win32@lists.mysql.com
Sent: Sunday, March 20, 2005 11:04
Subject: relationship between tables


i had to create tables using innodb type;i had to define relationships such
as one-to-many,many-to-many, one -to -one.can anyone suggest me how to
create tables of innodb type using foreign key constraints.I tried but i got
error 1005, cannot create table error 105.I trief it like this while trying
to create one to many relationship;



mysql>create table parent(id int,PRIMARY KEY(id)) type=innodb;
Query ok,0 rows affected,1 warning(0.19 secs)

mysql> create table child(cid int,pid int,PRIMARY KEY(cid),FOREIGN KEY(pid)
REFERENCES parent(id)) TYPE=INNODB;
ERROR 1005 (HY000): cant create table '.\library\child.frm' (error105)

where library is my database name;

please suggest me whats wrong above.also if you cant suggest me some links
where i can such relationship between table using innodb table type.

thank you.

____________________________________________________________ _____
Express yourself instantly with MSN Messenger! Download today it's FREE!
http://messenger.msn.click-url.com/go/onm00200471ave/direct/ 01/


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=jasonlsm@tm.net.my

--Boundary_(ID_KkKdfNm7P5yJ8CWdOcRsIQ)--

Re: relationship between tables

--=_alternative 004D43DB85256FCB_=
Content-Type: text/plain; charset="US-ASCII"

"sony nakarmi" wrote on 03/19/2005 10:04:41
PM:

> i had to create tables using innodb type;i had to define relationships
such
> as one-to-many,many-to-many, one -to -one.can anyone suggest me how to
> create tables of innodb type using foreign key constraints.I tried but i
got
> error 1005, cannot create table error 105.I trief it like this while
trying
> to create one to many relationship;
>
>
>
> mysql>create table parent(id int,PRIMARY KEY(id)) type=innodb;
> Query ok,0 rows affected,1 warning(0.19 secs)
>
> mysql> create table child(cid int,pid int,PRIMARY KEY(cid),FOREIGN
KEY(pid)
> REFERENCES parent(id)) TYPE=INNODB;
> ERROR 1005 (HY000): cant create table '.\library\child.frm' (error105)
>
> where library is my database name;
>
> please suggest me whats wrong above.also if you cant suggest me some
links
> where i can such relationship between table using innodb table type.
>
> thank you.
>
> ____________________________________________________________ _____
> Express yourself instantly with MSN Messenger! Download today it's FREE!

> http://messenger.msn.click-url.com/go/onm00200471ave/direct/ 01/
>
>

You must have missed it while RTFM. From:
http://dev.mysql.com/doc/mysql/en/innodb-foreign-key-constra ints.html

(Second bullet point)>>>>>>>>>>
In the referencing table, there must be an index where the foreign key
columns are listed as the first columns in the same order.
<<<<<<<<<<

Put an index on child.pid and your foreign key will work. If you need to
see more details from an InnoDB error you can use the SHOW INNODB STATUS
command.

http://dev.mysql.com/doc/mysql/en/innodb-monitor.html

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

--=_alternative 004D43DB85256FCB_=--