Re: Quotes in Foreign Key Constraint

Jan Langhans writes:
> Hallo,
>
> I'm trying to port our Oracle-App to MySQL 4.0.13-nt on Windows 2000 FP 3 (Connection thru MyODBC 3.51).
> I've set the default-table-type to InnoDB and the ansi-Option is enabled to allow the quoting of column names.
> When I try to quote the names of the referenced column and/or the referencing column in a foreign key constraint the create statement will fail as demonstrated by the
> following example:
>
> CREATE TABLE ta ("ca" INTEGER NOT NULL, INDEX ("ca"))
> OK
>
> CREATE TABLE tb ("rca" INTEGER, "cb" INTEGER, INDEX ("rca"), FOREIGN KEY ("rca") REFERENCES ta("ca") ON DELETE CASCADE)
>
> Fails with
> [MySQL][ODBC 3.51 Driver][mysqld-4.0.13-nt]Can't create table '.\typotext\tb.frm' (errno: 150)
>
> If I remove the quotes in the constraint like in
> CREATE TABLE tb ("rca" INTEGER, "cb" INTEGER, INDEX ("rca"), FOREIGN KEY (rca) REFERENCES ta(ca) ON DELETE CASCADE)
> the table is created without error.
>
> This looks like a bug to me, witch I haven't found documented anywhere.
>
> Bye,
> Jan
>

Hi!

You are right, at present quotes are not supported, but in future they
might be supported.

We yet have to decide which quotes to support, single , double or back-ticks.

--

Regards,

--
For technical support contracts, go to https://order.mysql.com/?ref=msmi
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB
/_/ /_/\_, /___/\___\_\___/ Fulltime Developer and Support Coordinator
<___/ www.mysql.com Larnaca, Cyprus


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Quotes in Foreign Key Constraint

Hallo,

I'm trying to port our Oracle-App to MySQL 4.0.13-nt on Windows 2000 FP 3 (Connection thru MyODBC 3.51).
I've set the default-table-type to InnoDB and the ansi-Option is enabled to allow the quoting of column names.
When I try to quote the names of the referenced column and/or the referencing column in a foreign key constraint the create statement will fail as demonstrated by the
following example:

CREATE TABLE ta ("ca" INTEGER NOT NULL, INDEX ("ca"))
OK

CREATE TABLE tb ("rca" INTEGER, "cb" INTEGER, INDEX ("rca"), FOREIGN KEY ("rca") REFERENCES ta("ca") ON DELETE CASCADE)

Fails with
[MySQL][ODBC 3.51 Driver][mysqld-4.0.13-nt]Can't create table '.\typotext\tb.frm' (errno: 150)

If I remove the quotes in the constraint like in
CREATE TABLE tb ("rca" INTEGER, "cb" INTEGER, INDEX ("rca"), FOREIGN KEY (rca) REFERENCES ta(ca) ON DELETE CASCADE)
the table is created without error.

This looks like a bug to me, witch I haven't found documented anywhere.

Bye,
Jan





--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: Quotes in Foreign Key Constraint

Jan,

you have to use backticks ` . In MySQL a string in single or double quotes
is a character string, not an identifier.

Regards,

Heikki

----- Alkuperäinen viesti -----
Lähettäjä: "Sinisa Milivojevic"
Vastaanottaja:
Kopio:
Lähetetty: Tuesday, June 17, 2003 2:46 PM
Aihe: Re: Quotes in Foreign Key Constraint


> Jan Langhans writes:
> > Hallo,
> >
> > I'm trying to port our Oracle-App to MySQL 4.0.13-nt on Windows 2000 FP
3 (Connection thru MyODBC 3.51).
> > I've set the default-table-type to InnoDB and the ansi-Option is enabled
to allow the quoting of column names.
> > When I try to quote the names of the referenced column and/or the
referencing column in a foreign key constraint the create statement will
fail as demonstrated by the
> > following example:
> >
> > CREATE TABLE ta ("ca" INTEGER NOT NULL, INDEX ("ca"))
> > OK
> >
> > CREATE TABLE tb ("rca" INTEGER, "cb" INTEGER, INDEX ("rca"), FOREIGN KEY
("rca") REFERENCES ta("ca") ON DELETE CASCADE)
> >
> > Fails with
> > [MySQL][ODBC 3.51 Driver][mysqld-4.0.13-nt]Can't create table
'.\typotext\tb.frm' (errno: 150)
> >
> > If I remove the quotes in the constraint like in
> > CREATE TABLE tb ("rca" INTEGER, "cb" INTEGER, INDEX ("rca"), FOREIGN KEY
(rca) REFERENCES ta(ca) ON DELETE CASCADE)
> > the table is created without error.
> >
> > This looks like a bug to me, witch I haven't found documented anywhere.
> >
> > Bye,
> > Jan
> >
>
> Hi!
>
> You are right, at present quotes are not supported, but in future they
> might be supported.
>
> We yet have to decide which quotes to support, single , double or
back-ticks.
>
> --
>
> Regards,
>
> --
> For technical support contracts, go to https://order.mysql.com/?ref=msmi
> __ ___ ___ ____ __
> / |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
> / /|_/ / // /\ \/ /_/ / /__ MySQL AB
> /_/ /_/\_, /___/\___\_\___/ Fulltime Developer and Support Coordinator
> <___/ www.mysql.com Larnaca, Cyprus


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: Quotes in Foreign Key Constraint

Jan,

I had totally forgotten that MySQL has that --ansi option. Sorry, but it
will take a while to fix the foreign key quote character in the --ansi mode
:). I have to get multiple tablespaces ready by September 15th, 2003.

Regards,

Heikki

----- Alkuperäinen viesti -----
Lähettäjä: "Jan Langhans"
Vastaanottaja: "Heikki Tuuri" ;
Kopio:
Lähetetty: Tuesday, June 17, 2003 9:13 PM
Aihe: Re: Quotes in Foreign Key Constraint


Hallo Heikki,

thanks for your hint, witch fixes my Problem.

Since I run MySQL whith the --ansi-Option, and the documentation states in
chapter
1.8.2 Running MySQL in ANSI Mode:

`"' will be an identifier quote character (like the MySQL Server ``' quote
character) and
not a string quote character. You can still use ``' to quote identifers in
this mode

I still feel that MySQL should accept the double quotes in the foreign key
constraint in
ANSI Mode (double quotes as quote character are OK everywhere else I used
them).
Please correct me, if I'm wrong about it.


Thanks
Jan


On Tue, 17 Jun 2003 17:40:56 +0300, Heikki Tuuri wrote:

>Jan,
>
>you have to use backticks ` . In MySQL a string in single or double quotes
>is a character string, not an identifier.
>
>Regards,
>
>Heikki
>
>----- Alkuperäinen viesti -----
>Lähettäjä: "Sinisa Milivojevic"
>Vastaanottaja:
>Kopio:
>Lähetetty: Tuesday, June 17, 2003 2:46 PM
>Aihe: Re: Quotes in Foreign Key Constraint
>
>
>> Jan Langhans writes:
>> > Hallo,
>> >
>> > I'm trying to port our Oracle-App to MySQL 4.0.13-nt on Windows 2000 FP
>3 (Connection thru MyODBC 3.51).
>> > I've set the default-table-type to InnoDB and the ansi-Option is
enabled
>to allow the quoting of column names.
>> > When I try to quote the names of the referenced column and/or the
>referencing column in a foreign key constraint the create statement will
>fail as demonstrated by the
>> > following example:
>> >
>> > CREATE TABLE ta ("ca" INTEGER NOT NULL, INDEX ("ca"))
>> > OK
>> >
>> > CREATE TABLE tb ("rca" INTEGER, "cb" INTEGER, INDEX ("rca"), FOREIGN
KEY
>("rca") REFERENCES ta("ca") ON DELETE CASCADE)
>> >
>> > Fails with
>> > [MySQL][ODBC 3.51 Driver][mysqld-4.0.13-nt]Can't create table
>'.\typotext\tb.frm' (errno: 150)
>> >
>> > If I remove the quotes in the constraint like in
>> > CREATE TABLE tb ("rca" INTEGER, "cb" INTEGER, INDEX ("rca"), FOREIGN
KEY
>(rca) REFERENCES ta(ca) ON DELETE CASCADE)
>> > the table is created without error.
>> >
>> > This looks like a bug to me, witch I haven't found documented anywhere.
>> >
>> > Bye,
>> > Jan
>> >
>>
>> Hi!
>>
>> You are right, at present quotes are not supported, but in future they
>> might be supported.
>>
>> We yet have to decide which quotes to support, single , double or
>back-ticks.
>>
>> --
>>
>> Regards,
>>
>> --
>> For technical support contracts, go to https://order.mysql.com/?ref=msmi
>> __ ___ ___ ____ __
>> / |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
>> / /|_/ / // /\ \/ /_/ / /__ MySQL AB
>> /_/ /_/\_, /___/\___\_\___/ Fulltime Developer and Support Coordinator
>> <___/ www.mysql.com Larnaca, Cyprus
>



--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: Quotes in Foreign Key Constraint

Hallo Heikki,

thanks for your hint, witch fixes my Problem.

Since I run MySQL whith the --ansi-Option, and the documentation states =
in chapter
1.8.2 Running MySQL in ANSI Mode:

`"' will be an identifier quote character (like the MySQL Server ``' q=
uote character) and
not a string quote character. You can still use ``' to quote identifers =
in this mode

I still feel that MySQL should accept the double quotes in the foreign k=
ey constraint in
ANSI Mode (double quotes as quote character are OK everywhere else I use=
d them).
Please correct me, if I'm wrong about it.


Thanks
Jan


On Tue, 17 Jun 2003 17:40:56 +0300, Heikki Tuuri wrote:

>Jan,
>
>you have to use backticks ` . In MySQL a string in single or double qu=
otes
>is a character string, not an identifier.
>
>Regards,
>
>Heikki
>
>----- Alkuperäinen viesti -----
>Lähettäjä: "Sinisa Milivojevic"
>Vastaanottaja:
>Kopio:
>Lähetetty: Tuesday, June 17, 2003 2:46 PM
>Aihe: Re: Quotes in Foreign Key Constraint
>
>
>> Jan Langhans writes:
>> > Hallo,
>> >
>> > I'm trying to port our Oracle-App to MySQL 4.0.13-nt on Windows 200=
0 FP
>3 (Connection thru MyODBC 3.51).
>> > I've set the default-table-type to InnoDB and the ansi-Option is en=
abled
>to allow the quoting of column names.
>> > When I try to quote the names of the referenced column and/or the
>referencing column in a foreign key constraint the create statement wil=
l
>fail as demonstrated by the
>> > following example:
>> >
>> > CREATE TABLE ta ("ca" INTEGER NOT NULL, INDEX ("ca"))
>> > OK
>> >
>> > CREATE TABLE tb ("rca" INTEGER, "cb" INTEGER, INDEX ("rca"), FOREIG=
N
KEY
>("rca") REFERENCES ta("ca") ON DELETE CASCADE)
>> >
>> > Fails with
>> > [MySQL][ODBC 3.51 Driver][mysqld-4.0.13-nt]Can't create table
>'.\typotext\tb.frm' (errno: 150)
>> >
>> > If I remove the quotes in the constraint like in
>> > CREATE TABLE tb ("rca" INTEGER, "cb" INTEGER, INDEX ("rca"), FOREIG=
N
KEY
>(rca) REFERENCES ta(ca) ON DELETE CASCADE)
>> > the table is created without error.
>> >
>> > This looks like a bug to me, witch I haven't found documented anywh=
ere.
>> >
>> > Bye,
>> > Jan
>> >
>>
>> Hi!
>>
>> You are right, at present quotes are not supported, but in future the=
y
>> might be supported.
>>
>> We yet have to decide which quotes to support, single , double or
>back-ticks.
>>
>> --
>>
>> Regards,
>>
>> --
>> For technical support contracts, go to https://order.mysql.com/?ref=3D=
msmi
>> __ ___ ___ ____ __
>> / |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic om>
>> / /|_/ / // /\ \/ /_/ / /__ MySQL AB
>> /_/ /_/\_, /___/\___\_\___/ Fulltime Developer and Support Coordin=
ator
>> <___/ www.mysql.com Larnaca, Cyprus
>



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