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.
<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)
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.
---
Rhino
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
