MySQL Foreign Key Issue
am 26.03.2007 07:30:51 von Lasitha Alawatta
------=_NextPartTM-000-affcea67-4785-4c95-a0d6-1ee6b4a34d5b
Content-Type: multipart/alternative;
boundary="----_=_NextPart_001_01C76F67.EB0E8918"
------_=_NextPart_001_01C76F67.EB0E8918
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Hello,
=0AI have 2 issue, regarding MySQL "Foreign K=
ey".
I have two tables;
Table 01 CUSTOMER
=
=0Acolumn name
characteristic
SID
Pri mary =
Key
Full_Name
Table OR=
DERS
=0Acolumn name
characteristic
Order_I=
D
Primary Key
Order_Date
=0AC=
ustomer_SID
Foreign Key
Amount
=
When I run "ALTER TABLE ORDERS ADD FOREIGN KEY (customer_=
sid) REFERENCES
CUSTOMER(SID);" that sql statement,
=0D=
=0AI inserted 2 records to both tables.
=
Question No. 01.
Then I removed 1 record from CUSTOMER table. Bu=
t It want give any error
message. It should give an error message, bec=
ause in ORDERS table
already have some records relevant to the deleted=
customer record in
CUSTOMER table.
=0AQuestion=
is why it want give any error =3F (I'm using phpMyAdmin)
=
=0AQuestion No. 02.
Is there any tool available for to get=
a Database diagram (like in MS
SQL Server). I found a tool call "DBDe=
signer 4". But it wants show the
DB Diagram.
=0A=
=0AThanks in Advance,
Lasitha.
=
DOTW DISCLAIMER:
This e-mail and any attachmen=
ts are strictly confidential and intended for the addressee only. If you ar=
e not the named addressee you must not disclose, copy or take
any acti=
on in reliance of this transmission and you should notify us as soon as pos=
sible. If you have received it in error, please contact the message sender =
immediately.
This e-mail and any attachments are believed to be free f=
rom viruses but it is your responsibility to carry out all necessary virus =
checks and DOTW accepts no liability
in connection therewith.
=
=0AThis e-mail and all other electronic (including voice) communications fr=
om the sender's company are for informational purposes only. No such commu=
nication is intended
by the sender to constitute either an electronic =
record or an electronic signature or to constitute any agreement by the sen=
der to conduct a transaction by electronic means.
------_=_NextPart_001_01C76F67.EB0E8918
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
hemas-microsoft-com:office:word" xmlns=3D"http://www.w3.org/TR/REC-html40">=0D=
=0A
charset=3Dus-ascii">
11 (filtered medium)">
ng=3DEN-US link=3Dblue vlink=3Dpurple>
=0D=
=0A
nt-size:10.0pt;
font-family:Arial'>Hello,
=0D=
=0A
nt-size:10.0pt;
font-family:Arial'>
=0D=
=0A
nt-size:10.0pt;
font-family:Arial'>I have 2 issue, regarding MySQL =
220;Foreign Key”.
MsoNormal>
font-family:Arial'>I have two tables;
=0D=
=0A
ly:Arial'>Table
01
alic'>CUSTOMER
=
=
>
family:Verdana'>column name
|
=
l>
-family:Verdana'>characteristic
| =0D=
=0A
=0D=
=0A
-size:9.0pt;
font-family:Verdana'>SID =0D=
=0A |
class=3DMsoNormal>
0pt;
font-family:Verdana'>Primary Key=0D=
=0A |
|
75pt .75pt'>
n style=3D'font-size:9.0pt;
font-family:Verdana'>Full_Name
>
|
5pt .75pt'>
style=3D'font-size:9.0pt;
font-family:Verdana'>
span>
=3DMsoNormal>
=0Afont-family:Arial'>
nt size=3D2 face=3DArial>
>Table
ORDERS=
NormalTable border=3D1 cellpadding=3D0>
|
ing:.75pt .75pt .75pt .75pt'>
ce=3DVerdana>co=
lumn name
|
ding:.75pt .75pt .75pt .75pt'>
ace=3DVerdana>c=
haracteristic
<=
tr>
MsoNormal>
font-family:Verdana'>Order_ID
| =0D=
=0A
rmal>
ont-family:Verdana'>Primary Key
| =0D=
=0A
=0D=
=0A
-size:9.0pt;
font-family:Verdana'>Order_Date
>
|
=0D=
=0A
-size:9.0pt;
font-family:Verdana'>
p>
|
|
5pt .75pt .75pt'>
>Customer_SID
:p>
|
75pt .75pt .75pt'>
dana>Foreign Ke=
y
<=
td style=3D'padding:.75pt .75pt .75pt .75pt'>
<=
font size=3D1 face=3DVerdana>
mily:Verdana'>Amount
|
tyle=3D'padding:.75pt .75pt .75pt .75pt'>
size=3D1 face=3DVerdana>
:Verdana'>
=
style=3D'font-size:10.0pt;
font-family:Arial'> =
an style=3D'font-size:10.0pt;
font-family:Arial'>When I run “ALT=
ER TABLE ORDERS ADD FOREIGN KEY
(customer_sid) REFERENCES CUSTOMER(SID=
);” that sql statement,
lass=3DMsoNormal>
t;
font-family:Arial'>
<=
p class=3DMsoNormal>
0pt;
font-family:Arial'>I inserted 2 records to both tables. <=
/o:p>
ace=3DArial><=
span style=3D'text-decoration:none'>
p>
le=3D'font-size:10.0pt;
font-family:Arial'>Question No. 01.=
Arial>Then I remov=
ed 1 record from CUSTOMER table. But It want
give any error message. I=
t should give an error message, because in ORDERS
table already have s=
ome records relevant to the deleted customer record in CUSTOMER
table.=
2 face=3DArial>
p>
=3D2 face=3DArial>=
Question is why it want give any error =3F (I’m using phpMyAdmin)
>
ce=3DArial>&n=
bsp;
2 face=3DArial>Que=
stion No. 02.
mal>
family:Arial'>Is there any tool available for to get a Database diagram
=
(like in MS SQL Server). I found a tool call “DBDesigner 4”. Bu=
t it
wants show the DB Diagram.
=
0.0pt;
font-family:Arial'>
=0D=
=0A
e:10.0pt;
font-family:Arial'>
=0D=
=0A
e:10.0pt;
font-family:Arial'>Thanks in Advance,
t>
yle=3D'font-size:10.0pt;
font-family:Arial'>Lasitha.=
an style=3D'font-size:10.0pt;
font-family:Arial'>
an>
=
<=
/span>
Re: MySQL Foreign Key Issue
am 26.03.2007 14:15:48 von Luchino - Samel
------=_Part_255810_20103171.1174911348831
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
*Question No. 01*
there are some kind of foreign key in database
Some of them want give any error when you delete from the table where the
foreign key is in, this cause the remove is "recursive" and it delete also
the ORDER linked to the CUSTOMER (CASCADE foreign key).
In other kind of foreign key (I don't remember the name) you cannot delete
CUSTOMER if there is some ORDER linked to them.
hope this will help
c-ya
2007/3/26, Lasitha Alawatta :
>
> Hello,
>
>
>
> I have 2 issue, regarding MySQL "Foreign Key".
>
> I have two tables;
>
> Table 01 *CUSTOMER*
>
> column name
>
> characteristic
>
> SID
>
> Primary Key
>
> Full_Name
>
>
>
>
>
> Table *ORDERS*
>
> column name
>
> characteristic
>
> Order_ID
>
> Primary Key
>
> Order_Date
>
>
>
> Customer_SID
>
> Foreign Key
>
> Amount
>
>
>
>
>
> When I run "ALTER TABLE ORDERS ADD FOREIGN KEY (customer_sid) REFERENCES
> CUSTOMER(SID);" that sql statement,
>
>
>
> I inserted 2 records to both tables.
>
> * *
>
> *Question No. 01.*
>
> Then I removed 1 record from CUSTOMER table. But It want give any error
> message. It should give an error message, because in ORDERS table already
> have some records relevant to the deleted customer record in CUSTOMER table.
>
>
>
> Question is why it want give any error ? (I'm using phpMyAdmin)
>
>
>
> *Question No. 02.*
>
> Is there any tool available for to get a Database diagram (like in MS SQL
> Server). I found a tool call "DBDesigner 4". But it wants show the DB
> Diagram.
>
>
>
>
>
> Thanks in Advance,
>
> Lasitha.
>
>
>
>
>
>
>
> DOTW DISCLAIMER:
>
>
> This e-mail and any attachments are strictly confidential and intended for the addressee only. If you are not the named addressee you must not disclose, copy or take
>
> any action in reliance of this transmission and you should notify us as soon as possible. If you have received it in error, please contact the message sender immediately.
>
> This e-mail and any attachments are believed to be free from viruses but it is your responsibility to carry out all necessary virus checks and DOTW accepts no liability
> in connection therewith.
>
>
> This e-mail and all other electronic (including voice) communications from the sender's company are for informational purposes only. No such communication is intended
>
> by the sender to constitute either an electronic record or an electronic signature or to constitute any agreement by the sender to conduct a transaction by electronic means.
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
--
Samel alias Luca
"Close the world,txen eht nepo!"
"You will never break my mind!"
LinuxUser:410006 eversor:316704 vindicare:316705
Wii owner: 7579-9856-9598-5214
------=_Part_255810_20103171.1174911348831--
Re: MySQL Foreign Key Issue
am 26.03.2007 17:07:00 von Roberto Mansfield
Are you using InnoDB for you tables?
Lasitha Alawatta wrote:
> Hello,
>
>
>
> I have 2 issue, regarding MySQL Foreign Key.
>
> I have two tables;
>
> Table 01 */CUSTOMER/*
>
> column name
>
>
>
> characteristic
>
> SID
>
>
>
> Primary Key
>
> Full_Name
>
>
>
>
>
>
>
> Table */ORDERS/*
>
> column name
>
>
>
> characteristic
>
> Order_ID
>
>
>
> Primary Key
>
> Order_Date
>
>
>
>
>
> Customer_SID
>
>
>
> Foreign Key
>
> Amount
>
>
>
>
>
>
>
> When I run ALTER TABLE ORDERS ADD FOREIGN KEY (customer_sid) REFERENCES
> CUSTOMER(SID); that sql statement,
>
>
>
> I inserted 2 records to both tables.
>
> _ _
>
> _Question No. 01._
>
> Then I removed 1 record from CUSTOMER table. But It want give any error
> message. It should give an error message, because in ORDERS table
> already have some records relevant to the deleted customer record in
> CUSTOMER table.
>
>
>
> Question is why it want give any error ? (Im using phpMyAdmin)
>
>
>
> _Question No. 02._
>
> Is there any tool available for to get a Database diagram (like in MS
> SQL Server). I found a tool call DBDesigner 4. But it wants show the
> DB Diagram.
>
>
>
>
>
> Thanks in Advance,
>
> Lasitha.
>
>
>
>
>
>
>
>
> DOTW DISCLAIMER:
>
> This e-mail and any attachments are strictly confidential and intended for the addressee only. If you are not the named addressee you must not disclose, copy or take
> any action in reliance of this transmission and you should notify us as soon as possible. If you have received it in error, please contact the message sender immediately.
> This e-mail and any attachments are believed to be free from viruses but it is your responsibility to carry out all necessary virus checks and DOTW accepts no liability
> in connection therewith.
>
> This e-mail and all other electronic (including voice) communications from the sender's company are for informational purposes only. No such communication is intended
> by the sender to constitute either an electronic record or an electronic signature or to constitute any agreement by the sender to conduct a transaction by electronic means.
>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: MySQL Foreign Key Issue
am 27.03.2007 11:24:34 von Oskar
Luchino - Samel wrote:
> *Question No. 01*
> there are some kind of foreign key in database
> Some of them want give any error when you delete from the table where the
> foreign key is in, this cause the remove is "recursive" and it delete
> also
> the ORDER linked to the CUSTOMER (CASCADE foreign key).
> In other kind of foreign key (I don't remember the name) you cannot
> delete
> CUSTOMER if there is some ORDER linked to them.
>
restrict
> hope this will help
>
> c-ya
>
>
>
> 2007/3/26, Lasitha Alawatta :
>
>>
>> Hello,
>>
>>
>>
>> I have 2 issue, regarding MySQL "Foreign Key".
>>
>> I have two tables;
>>
>> Table 01 *CUSTOMER*
>>
>> column name
>>
>> characteristic
>>
>> SID
>>
>> Primary Key
>>
>> Full_Name
>>
>>
>>
>>
>>
>> Table *ORDERS*
>>
>> column name
>>
>> characteristic
>>
>> Order_ID
>>
>> Primary Key
>>
>> Order_Date
>>
>>
>>
>> Customer_SID
>>
>> Foreign Key
>>
>> Amount
>>
>>
>>
>>
>>
>> When I run "ALTER TABLE ORDERS ADD FOREIGN KEY (customer_sid) REFERENCES
>> CUSTOMER(SID);" that sql statement,
>>
>>
>>
>> I inserted 2 records to both tables.
>>
>> * *
>>
>> *Question No. 01.*
>>
>> Then I removed 1 record from CUSTOMER table. But It want give any error
>> message. It should give an error message, because in ORDERS table
>> already
>> have some records relevant to the deleted customer record in CUSTOMER
>> table.
>>
you have restrict constraint on Customer_SID in table orders. You have 2
options:
1. delete from orders where Customer_SID=foo
delete from customer where SID=foo
2. read
http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-co nstraints.html
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php