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
...
the foreign key you defined on the column workdept has the name 'bar'.
If you defined the table this way:
CREATE TABLE Foo
...
constraint foreign key(workdept) references Sample.department on delete
cascade
...
the name of the foreign key would be generated by MySQL.
If memory serves, the foreign key name can be used to drop the foreign key
in an ALTER TABLE statement and perhaps a few other places. The name of the
foreign key does not help you with what you appear to want to do.
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;
insert into Purcell01 values
(1, 'Fred', 'Flintstone'),
(2, 'Barney', 'Rubble'),
(3, 'Fred', 'Slate'),
(4, 'Wilma', 'Flintstone'),
(5, 'Fred', 'Flintstone');
select * from Purcell01;
=============================================================
If I'm reading the manual correctly, this should force the _COMBINATION_ of
fname and lname to be different in each row of the table. Then, when you do
the inserts, all but the last one should work. It's perfectly okay for other
rows to have Flintstone in the lname column and it's perfectly okay for
other rows to have Fred in the fname column but only one row in the column
can have the COMBINATION of 'Fred' 'Flintstone' in the lname and fname
columns. I _think_ that is what you want to do.
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.
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;
---
Rhino
----- Original Message -----
From: "Scott Purcell" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Monday, January 30, 2006 9:08 AM
Subject: Help Understanding Document Syntax
Hello,
I have created some tables a while back, and of course, and I am
learning, I have found problems with duplicate entries and other
problems.
So upon a fresh read of the 5.1 docs, I am trying to understand the word
"symbol" after the constraint.
I would like to be able to somehow combine two columns, and make them
unique? Or distinct?. I do not want the same two columns to ever occur
again. If someone tries to insert, just ignore and continue. So I will
use a MyISAM table type. But in order to understand how this is done,
could use an understanding of the "symbol" behind constraint.
create_definition:
column_definition
| [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
| KEY [index_name] [index_type] (index_col_name,...)
| INDEX [index_name] [index_type] (index_col_name,...)
| [CONSTRAINT [symbol]] UNIQUE [INDEX]
[index_name] [index_type] (index_col_name,...)
| FULLTEXT [INDEX] [index_name] (index_col_name,...)
[WITH PARSER parser_name]
| SPATIAL [INDEX] [index_name] (index_col_name,...)
| [CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name,...) [reference_definition]
| CHECK (expr)
--------------------------------------------------------------------------------
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.23/243 - Release Date: 27/01/2006
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.23/243 - Release Date: 27/01/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
