> > CREATE TABLE Salespersons
[...]
> > rank INTEGER NOT NULL DEFAULT 1 CHECK (rank IN (1,2,3)),
> > salary DECIMAL(7, 2) NOT NULL DEFAULT 1000.00 CHECK (salary >=1000.00));
As someone else pointed out, the syntax requires a comma in front of
CHECK:
CREATE TABLE Salespersons (
rank INTEGER NOT NULL DEFAULT 1,
CHECK (rank IN (1,2,3)),
salary DECIMAL(7, 2) NOT NULL DEFAULT 1000.00,
CHECK (salary >=1000.00));
);
But by remarkable coincidence, there also happens to be a bug in the
parser where the fixed version of your example doesn't work either.
I don't know why the existing sql_yacc.yy doesn't work on it, but it
also appears that sql_yacc.yy doesn't strictly comply with this:
> create_definition:
> col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT]
> [PRIMARY KEY] [reference_definition]
[...]
> or [CONSTRAINT symbol] FOREIGN KEY index_name (index_col_name,...)
> [reference_definition]
> or CHECK (expr)
insofar as it allows an optional [CONSTRAINT symbol] before the CHECK
(not just before FOREIGN KEY). Removing that option with this patch:
==================================================================
*** sql/sql_yacc.yy.orig Wed Feb 7 20:31:26 2001
--- sql/sql_yacc.yy Wed Feb 7 20:27:10 2001
***************
*** 792,798 ****
{
Lex->col_list.empty(); /* Alloced by sql_alloc */
}
! | opt_constraint CHECK_SYM '(' expr ')'
{
Lex->col_list.empty(); /* Alloced by sql_alloc */
}
--- 792,798 ----
{
Lex->col_list.empty(); /* Alloced by sql_alloc */
}
! | CHECK_SYM '(' expr ')'
{
Lex->col_list.empty(); /* Alloced by sql_alloc */
}
==================================================================
fixes the problem you're having, in addition to bringing the grammer
in line with the manual.
*HOWEVER*, the "FOREIGN KEY" line appears to still be broken; that
opt_constraint bit is screwing things up, and I didn't succeed in
figuring out how.
--
Pete Harlan
[EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
