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]>; <mysql@lists.mysql.com>
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]
