List:General Discussion« Previous MessageNext Message »
From:Rhino Date:January 31 2006 2:49pm
Subject:Re: Help Understanding Document Syntax
View as plain text  
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

Thread
Help Understanding Document SyntaxScott Purcell30 Jan
  • Re: Help Understanding Document SyntaxRhino30 Jan
    • Re: Help Understanding Document SyntaxMichael Stassen31 Jan
  • Re: Help Understanding Document SyntaxRhino31 Jan
    • Re: Help Understanding Document SyntaxSGreen31 Jan
    • Re: Help Understanding Document SyntaxMichael Stassen31 Jan