MySQL ODBC Connector - Help Understanding Document Syntax

Author

Help Understanding Document Syntax

Scott Purcell

2006-01-30, 9:25 am

------ _=_NextPart_001_01C6
25A6.9EAE0A3F
Content-Type: text/plain;
charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable

Hello,

=20

I have created some tables a while back, and of course, and I am
learning, I have found problems with duplicate entries and other
problems.

So upon a fresh read of the 5.1 docs, I am trying to understand the word
"symbol" after the constraint.

=20

I would like to be able to somehow combine two columns, and make them
unique? Or distinct?. I do not want the same two columns to ever occur
again. If someone tries to insert, just ignore and continue. So I will
use a MyISAM table type. But in order to understand how this is done,
could use an understanding of the "symbol" behind constraint.

=20

=20

=20

create_definition:
column_definition
| [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
| KEY [index_name] [index_type] (index_col_name,...)
| INDEX [index_name] [index_type] (index_col_name,...)
| [CONSTRAINT [symbol]] UNIQUE [INDEX]
[index_name] [index_type] (index_col_name,...)
| FULLTEXT [INDEX] [index_name] (index_col_name,...)
[WITH PARSER parser_name]
| SPATIAL [INDEX] [index_name] (index_col_name,...)
| [CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name,...) & #91;reference_defini
tion]
| CHECK (expr)

=20

------ _=_NextPart_001_01C6
25A6.9EAE0A3F--

Rhino

2006-01-30, 11:24 am

The 'symbol' you are referring to, in the foreign key clause of the CREATE
TABLE statement, is simply an opportunity for you to choose a name for the
foreign key of the table; if you don't choose a name, MySQL will generate a
default name for you.

Therefore, if you do this:

CREATE TABLE Foo
...
constraint (bar) foreign key(workdept) references Sample.department on
delete cascade
...

the foreign key you defined on the column workdept has the name 'bar'.

If you defined the table this way:

CREATE TABLE Foo
...
constraint foreign key(workdept) references Sample.department on delete
cascade
...

the name of the foreign key would be generated by MySQL.

If memory serves, the foreign key name can be used to drop the foreign key
in an ALTER TABLE statement and perhaps a few other places. The name of the
foreign key does not help you with what you appear to want to do.

I _think_ you are saying that you want the combination of values in two of
the columns of your table to be unique so that no two rows of the same table
can have that same combination of values in those two columns. I know how to
do this in DB2, my main database, so I looked up the syntax to do the same
thing in MySQL and came up with this small example:

====================
====================
====================
=
use tmp;

create table Purcell01
(empno smallint not null,
fname char(10) not null,
lname char(10) not null,
primary key(empno)
constraint(uk) unique index ukix btree (fname, lname)) Type=INNODB;

insert into Purcell01 values
(1, 'Fred', 'Flintstone'),
(2, 'Barney', 'Rubble'),
(3, 'Fred', 'Slate'),
(4, 'Wilma', 'Flintstone'),
(5, 'Fred', 'Flintstone');

select * from Purcell01;

====================
====================
====================
=

If I'm reading the manual correctly, this should force the _COMBINATION_ of
fname and lname to be different in each row of the table. Then, when you do
the inserts, all but the last one should work. It's perfectly okay for other
rows to have Flintstone in the lname column and it's perfectly okay for
other rows to have Fred in the fname column but only one row in the column
can have the COMBINATION of 'Fred' 'Flintstone' in the lname and fname
columns. I _think_ that is what you want to do.

Unfortunately, I get a syntax error when I try this in my copy of MySQL,
which is only 4.0.15. I'm guessing that the UNIQUE clause isn't recognized
in MySQL 4.0.15 and that the statement will work in 5.1.x but there may be
some problem with my syntax. I can't find an explicit example of a
multicolumn unique constraint in the manual so maybe someone else reading
this thread can identify any errors in the syntax if this doesn't work for
you.

The other thing you wanted was for a bad row, like the last row in my
Inserts, to simply be ignored if it violates the unique constraint. In DB2,
that isn't an option: the insert simply fails due to the violation of the
uniqueness. However, it _appears_ that MySQL has a different policy.
Apparently, you can add an "IGNORE" clause to an INSERT or UPDATE statement
to make it ignore a uniqueness violation. As I read the article on the
INSERT statement, you would want an INSERT to look like this if you wanted a
row that violated uniqueness to be ignored:

INSERT IGNORE INTO PURCELL01 VALUES(5, 'Fred', 'Flintstone');

The UPDATE statement appears to be the same idea;

UPDATE IGNORE
set fname = 'Fred', lname = 'Flintstone'
where empno = 4;

---
Rhino

----- Original Message -----
From: "Scott Purcell" <spurcell@vertisinc.com>
To: <mysql@lists.mysql.com>
Sent: Monday, January 30, 2006 9:08 AM
Subject: Help Understanding Document Syntax

Hello,

I have created some tables a while back, and of course, and I am
learning, I have found problems with duplicate entries and other
problems.

So upon a fresh read of the 5.1 docs, I am trying to understand the word
"symbol" after the constraint.

I would like to be able to somehow combine two columns, and make them
unique? Or distinct?. I do not want the same two columns to ever occur
again. If someone tries to insert, just ignore and continue. So I will
use a MyISAM table type. But in order to understand how this is done,
could use an understanding of the "symbol" behind constraint.

create_definition:
column_definition
| [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
| KEY [index_name] [index_type] (index_col_name,...)
| INDEX [index_name] [index_type] (index_col_name,...)
| [CONSTRAINT [symbol]] UNIQUE [INDEX]
[index_name] [index_type] (index_col_name,...)
| FULLTEXT [INDEX] [index_name] (index_col_name,...)
[WITH PARSER parser_name]
| SPATIAL [INDEX] [index_name] (index_col_name,...)
| [CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name,...) & #91;reference_defini
tion]
| CHECK (expr)

--------------------------------------------------------------------------------

No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.23/243 - Release Date: 27/01/2006

--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.23/243 - Release Date: 27/01/2006

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Michael Stassen

2006-01-31, 3:23 am

Rhino wrote:
>
> The 'symbol' you are referring to, in the foreign key clause of the
> CREATE TABLE statement, is simply an opportunity for you to choose a
> name for the foreign key of the table; if you don't choose a name, MySQL
> will generate a default name for you.
>
> Therefore, if you do this:
>
> CREATE TABLE Foo
> ...
> constraint (bar) foreign key(workdept) references Sample.department
> on delete cascade
> ...

That's not quite right. There should be no parentheses around the symbol, but
you do need parentheses around the referenced column. The syntax is

[CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name,...) & #91;reference_defini
tion]

reference_definition
:
REFERENCES tbl_name & #91;(index_col_name,
...)]

so you should have

CONSTRAINT bar FOREIGN KEY (workdept) REFERENCES Sample (department)
ON DELETE CASCADE

<snip>
> I _think_ you are saying that you want the combination of values in two
> of the columns of your table to be unique so that no two rows of the
> same table can have that same combination of values in those two
> columns. I know how to do this in DB2, my main database, so I looked up
> the syntax to do the same thing in MySQL and came up with this small
> example:
>
> ====================
====================
====================
=
> use tmp;
>
> create table Purcell01
> (empno smallint not null,
> fname char(10) not null,
> lname char(10) not null,
> primary key(empno)
> constraint(uk) unique index ukix btree (fname, lname)) Type=INNODB;

For the record, unique constraints don't require InnoDB.

<snip>
> Unfortunately, I get a syntax error when I try this in my copy of MySQL,
> which is only 4.0.15. I'm guessing that the UNIQUE clause isn't
> recognized in MySQL 4.0.15 and that the statement will work in 5.1.x but
> there may be some problem with my syntax. I can't find an explicit
> example of a multicolumn unique constraint in the manual so maybe
> someone else reading this thread can identify any errors in the syntax
> if this doesn't work for you.

UNIQUE constraints have been in mysql a long time (at least since 3.23, I
believe). You have parentheses in the wrong place again. The syntax is

[CONSTRAINT [symbol]] UNIQUE [INDEX]
[index_name] [index_type] (index_col_name,...)

so the correct definition would be

CONSTRAINT uk UNIQUE INDEX ukix (fname, lname)

or simply

UNIQUE ukix (fname, lname)

> The other thing you wanted was for a bad row, like the last row in my
> Inserts, to simply be ignored if it violates the unique constraint. In
> DB2, that isn't an option: the insert simply fails due to the violation
> of the uniqueness. However, it _appears_ that MySQL has a different
> policy. Apparently, you can add an "IGNORE" clause to an INSERT or
> UPDATE statement to make it ignore a uniqueness violation. As I read the
> article on the INSERT statement, you would want an INSERT to look like
> this if you wanted a row that violated uniqueness to be ignored:
>
> INSERT IGNORE INTO PURCELL01 VALUES(5, 'Fred', 'Flintstone');
>
> The UPDATE statement appears to be the same idea;
>
> UPDATE IGNORE
> set fname = 'Fred', lname = 'Flintstone'
> where empno = 4;

To be clear, attempting to insert a row which violates a unique constraint, or
to update a row in such a way as to violate a unique constraint, will fail in
MySQL. Adding "IGNORE" means it will fail silently, rather than throwing an
error, but it will still fail.

> ---
> Rhino

Michael

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Rhino

2006-01-31, 9:23 am

First and foremost, thank you very much Michael for correcting my mistakes;
I _was_ a bit sloppy in my reading of the syntax for the statements and that
caused some unnecessary errors in my reply to Scott.

However, your corrections are not _quite_ right even now. See below where I
explain this.

--
Rhino

----- Original Message -----
From: "Michael Stassen" <Michael.Stassen@verizon.net>
To: "Rhino" <rhino1@sympatico.ca>
Cc: "Scott Purcell" <spurcell@vertisinc.com>; <mysql@lists.mysql.com>
Sent: Tuesday, January 31, 2006 1:18 AM
Subject: Re: Help Understanding Document Syntax

> Rhino wrote:
>
> That's not quite right. There should be no parentheses around the symbol,
> but you do need parentheses around the referenced column. The syntax is
>
> [CONSTRAINT [symbol]] FOREIGN KEY
> [index_name] (index_col_name,...) & #91;reference_defini
tion]
>
> reference_definition
:
> REFERENCES tbl_name & #91;(index_col_name,
...)]
>
> so you should have
>
> CONSTRAINT bar FOREIGN KEY (workdept) REFERENCES Sample (department)
> ON DELETE CASCADE
>
> <snip>
>
> For the record, unique constraints don't require InnoDB.
>
Thanks for mentioning that. I didn't know one way or the other whether
unique keys required INNODB; I know that _foreign_ keys are only supported
in INNODB so I pretty much always use INNODB tables for everything I do in
MySQL. It's useful to know that INNODB is not necessary to support unique
keys.

> <snip>
>
> UNIQUE constraints have been in mysql a long time (at least since 3.23, I
> believe). You have parentheses in the wrong place again. The syntax is
>
> [CONSTRAINT [symbol]] UNIQUE [INDEX]
> [index_name] [index_type] (index_col_name,...)
>
> so the correct definition would be
>
> CONSTRAINT uk UNIQUE INDEX ukix (fname, lname)
>
> or simply
>
> UNIQUE ukix (fname, lname)
>
>
Strangely enough, both of those formulations of the UNIQUE clause fail for
me with the same error as the mistaken version I first proposed in my note
to Scott.

This is the current version of my DROP/CREATE:

drop table if exists Purcell01;
create table if not exists Purcell01
(empno smallint not null,
fname char(10) not null,
lname char(10) not null,
primary key(empno)
-- constraint uk unique index ukix (fname, lname)
-- unique ukix (fname, lname)
) Type=INNODB;

If I run it exactly as shown, with both versions of the UNIQUE clause
commented, it works fine. But if I uncomment either version of the UNIQUE
clause, it fails with the same error I mentioned in my previous note. I've
also tried 'unique(fname, lname)' and that also fails on the same error.

Any idea why every formulation of the UNIQUE clause I try fails? If UNIQUE
has been supported since Version 3.x, then I'm out of ideas....

>
> To be clear, attempting to insert a row which violates a unique
> constraint, or to update a row in such a way as to violate a unique
> constraint, will fail in MySQL. Adding "IGNORE" means it will fail
> silently, rather than throwing an error, but it will still fail.
>
Thank you, that _is_ what I meant to say. I certainly didn't mean to imply
that adding 'IGNORE' would make the UPDATE successfully modify a row so that
it violated the UNIQUE constraint but I can see how somehow might read my
paragraph as if that is what I meant. Forgive my clumsy wording.

By the way, I see I also left out one critical thing in my UPDATE statement:
the table name! The first line of the UPDATE should be: "UPDATE IGNORE
PURCELL01", _not_ "UPDATE IGNORE".

I'm afraid I had several balls in the air yesterday and wasn't as accurate
as I normally strive to be; my apologies for any confusion!

Rhino

--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.25/246 - Release Date: 30/01/2006

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

SGreen@unimin.com

2006-01-31, 9:23 am

--=_alternative 00526CC585257107_=
Content-Type: text/plain; charset="US-ASCII"

Could your error be as simple as forgetting to separate ALL of your
indexes/constraints with commas?

CREATE TABLE (
...
PRIMARY KEY (...) ,
UNIQUE(...) ,
INDEX (...),
KEY(...),
FOREIGN KEY (...) REFERENCES othertable (other column)
) ENGINE=INNODB ... ;

It's hard to tell exactly but that's my first impression based on the code
snippets in the previous responses.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

"Rhino" <rhino1@sympatico.ca> wrote on 01/31/2006 09:49:17 AM:

> First and foremost, thank you very much Michael for correcting my
mistakes;
> I _was_ a bit sloppy in my reading of the syntax for the statements and
that
> caused some unnecessary errors in my reply to Scott.
>
> However, your corrections are not _quite_ right even now. See below
where I
> explain this.
>
> --
> Rhino
>
> ----- Original Message -----
> From: "Michael Stassen" <Michael.Stassen@verizon.net>
> To: "Rhino" <rhino1@sympatico.ca>
> Cc: "Scott Purcell" <spurcell@vertisinc.com>; <mysql@lists.mysql.com>
> Sent: Tuesday, January 31, 2006 1:18 AM
> Subject: Re: Help Understanding Document Syntax
>
>
name[color=darkred]
will[color=darkred]
Sample.department on[color=darkred]
symbol,[color=darkre
d]
is[color=darkred]
two[color=darkred]
same[color=darkred]
I[color=darkred]
syntax[color=darkred
]
> Thanks for mentioning that. I didn't know one way or the other whether
> unique keys required INNODB; I know that _foreign_ keys are only
supported
> in INNODB so I pretty much always use INNODB tables for everything I do
in
> MySQL. It's useful to know that INNODB is not necessary to support
unique
> keys.
>
MySQL,[color=darkred
]
but[color=darkred]
someone[color=darkre
d]
this[color=darkred]
3.23, I[color=darkred]
is[color=darkred]
> Strangely enough, both of those formulations of the UNIQUE clause fail
for
> me with the same error as the mistaken version I first proposed in my
note
> to Scott.
>
> This is the current version of my DROP/CREATE:
>
> drop table if exists Purcell01;
> create table if not exists Purcell01
> (empno smallint not null,
> fname char(10) not null,
> lname char(10) not null,
> primary key(empno)
> -- constraint uk unique index ukix (fname, lname)
> -- unique ukix (fname, lname)
> ) Type=INNODB;
>
> If I run it exactly as shown, with both versions of the UNIQUE clause
> commented, it works fine. But if I uncomment either version of the
UNIQUE
> clause, it fails with the same error I mentioned in my previous note.
I've
> also tried 'unique(fname, lname)' and that also fails on the same error.
>
> Any idea why every formulation of the UNIQUE clause I try fails? If
UNIQUE
> has been supported since Version 3.x, then I'm out of ideas....
>
[color=darkred]
In[color=darkred]
violation[color=dark
red]
UPDATE[color=darkred
]
article[color=darkre
d]
if[color=darkred]
> Thank you, that _is_ what I meant to say. I certainly didn't mean to
imply

> that adding 'IGNORE' would make the UPDATE successfully modify a row so
that
> it violated the UNIQUE constraint but I can see how somehow might read
my
> paragraph as if that is what I meant. Forgive my clumsy wording.
>
> By the way, I see I also left out one critical thing in my UPDATE
statement:
> the table name! The first line of the UPDATE should be: "UPDATE IGNORE
> PURCELL01", _not_ "UPDATE IGNORE".
>
> I'm afraid I had several balls in the air yesterday and wasn't as
accurate
> as I normally strive to be; my apologies for any confusion!
>
> Rhino
>
>
>
>
>
> --
> No virus found in this outgoing message.
> Checked by AVG Free Edition.
> Version: 7.1.375 / Virus Database: 267.14.25/246 - Release Date:
30/01/2006
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=sgreen@unimin.com
>

--=_alternative 00526CC585257107_=--

Michael Stassen

2006-01-31, 11:23 am

Rhino wrote:
> First and foremost, thank you very much Michael for correcting my
> mistakes; I _was_ a bit sloppy in my reading of the syntax for the
> statements and that caused some unnecessary errors in my reply to Scott.
>
> However, your corrections are not _quite_ right even now. See below
> where I explain this.

Strange. It was late when I answered, so I actually tested each statement
before posting in an attempt to prevent that.

<snip>

> Strangely enough, both of those formulations of the UNIQUE clause fail
> for me with the same error as the mistaken version I first proposed in
> my note to Scott.
>
> This is the current version of my DROP/CREATE:
>
> drop table if exists Purcell01;
> create table if not exists Purcell01
> (empno smallint not null,
> fname char(10) not null,
> lname char(10) not null,
> primary key(empno)
> -- constraint uk unique index ukix (fname, lname)
> -- unique ukix (fname, lname)
> ) Type=INNODB;
>
> If I run it exactly as shown, with both versions of the UNIQUE clause
> commented, it works fine. But if I uncomment either version of the
> UNIQUE clause, it fails with the same error I mentioned in my previous
> note. I've also tried 'unique(fname, lname)' and that also fails on the
> same error.

Did you add the comma after "primary key(empno)" when uncommenting one of the
unique constraint lines? That's the only thing I can see.

Michael

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Sponsored Links