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]>; <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] >