List:General Discussion« Previous MessageNext Message »
From:Rhino Date:January 30 2006 3:39pm
Subject:Re: Help Understanding Document Syntax
View as plain text  
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" <spurcell@stripped>
To: <mysql@stripped>
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

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