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@stripped>
To: "Rhino" <rhino1@stripped>
Cc: "Scott Purcell" <spurcell@stripped>; <mysql@stripped>
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
