--- In firebird-support@yahoogroups.com, "jacobswell4u"
<jacobswell4u@...> wrote:

>
> --- In firebird-support@yahoogroups.com, Helen Borrie <helebor@>
> wrote:
> >
> > At 11:25 AM 4/06/2008, jacobswell4u wrote:
> > >Hello, I'm a newbie of firebird and I just try to use it but got
> an
> > >error but I don't know why.
> > >
> > >the sql query is:
> > >SQL> create table test(
> > >CON> my_id varchar(255) character set none not null default '',
> > >CON> au_menu varchar(20) character set none not null default '',
> > >CON> au_auth char(1) check(value in ('r','w','d')) not null
> default '',
> > >CON> primary key (mb_id,au_menu)
> > >CON> );
> > >
> > >The error code is:
> > >Statement failed, SQLCODE = -104
> > >Dynamic SQL Error
> > >-SQL error code = -104
> > >-Token unknown - line 2, column 48
> > >-default
> > >
> > >In my thinking is there any rule for default value?
> >
> > Yup - the DEFAULT clause has to come BEFORE the NOT NULL constraint.
> >
> > Also your default on AU_AUTH is not valid, since your CHECK
> constraint requires the character to be either r, w or d.
> >
> > There are some possible design issues here as well....so if this is
> going to be a serious database, rather than just something to play
> with, then ask.
> >
> > ./heLen
> >
>
> I tried the following also but still got an error.
>
> SQL> create table test(
> CON> my_id varchar(255) character set none default '' not null,
> CON> au_menu varchar(20) character set none default '' not null,
> CON> au_auth char(1) check(value in ('r','w','d')) default 'r' not
> null,
> CON> primary key (mb_id,au_menu)
> CON> );
> Statement failed, SQLCODE = -104
> Dynamic SQL Error
> -SQL error code = -104
> -Token unknown - line 4, column 47
> -default
> SQL>
>

There are a number of issues:

* The check needs to go after the not null constraint.
* The keyword value is only for domain definitions. You need to name
the field if you are writing it into the table definition.
* The primary key line is wrong, missing names and misspelling fields.
* On Firebird 1.5 or earlier, your primary key size is too big. You
would do better creating a surrogate primary key.


The following query will get you closer except for the index size problem.


create table test(
my_id varchar(255) character set none default '' not null,
au_menu varchar(20) character set none default '' not null,
au_auth char(1) default 'r' not null check(au_auth in ('r','w','d')),
constraint pk_test primary key (my_id, au_menu)
);


Adam