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" <[EMAIL PROTECTED]> 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" <[EMAIL PROTECTED]>
> To: "Rhino" <[EMAIL PROTECTED]>
> Cc: "Scott Purcell" <[EMAIL PROTECTED]>; <[email protected]>
> Sent: Tuesday, January 31, 2006 1:18 AM
> Subject: Re: Help Understanding Document Syntax
>
>
> > 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,...) [reference_definition]
> >
> > reference_definition:
> > REFERENCES tbl_name [(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.
> >
> 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>
> >> 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)
> >
> >
> 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....
>
> >> 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.
> >
> 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/[EMAIL PROTECTED]
>
