Re: [sqlite] basic table level stuff

2004-01-21 Thread David M. Cook 
On Wed, Jan 21, 2004 at 03:22:29AM -0500, jim wrote:

> for some reason they are trying to name the constraint.  no idea why.

That determines the name of the index that will be used to implement the
constraint.

> It appears sqlitemanager doesn't use it.

Yup, it looks like sqlite ignores the constraint name and just gives the
index an autogenerated name:

sqlite> create table foo (name text constraint name_dx primary key);
sqlite> .indices foo
(foo autoindex 1)

whereas Postgres does use the name

classical=# create table foo (name text constraint name_dx primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'name_dx' for
table 'foo'
CREATE TABLE

> from this day forth constraints will have no name.

Well, they do have a name, you just can't give them your own name AFAIK.

Dave Cook

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

RE: [sqlite] basic table level stuff

2004-01-21 Thread jim 
>>I am also wondering about the constraint in the column-def like
column-def ::= name [type] [[CONSTRAINT name] column-
>>constraint]* 

I thought about this some more.  since constraint shows up in blue it is
reserved.
for some reason they are trying to name the constraint.  no idea why.
It appears sqlitemanager doesn't use it. 
from this day forth constraints will have no name.
onward... I have to hurry.  i am a low wage earner and I don't want my
boss to have to pay me overtime.
I'll be eligible soon you know.

thanks,
marvin


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

RE: [sqlite] basic table level stuff

2004-01-20 Thread jim 
Hi-

that is a great example and I will definitely use it.
However, the constraint you listed goes at the end.
I am also wondering about the constraint in the column-def like
column-def ::= name [type] [[CONSTRAINT name] column-constraint]* 

CREATE [TEMP | TEMPORARY] TABLE table-name (
column-def [, column-def]*
[, constraint]*
)

I am trying to get this last piece because I want to be able to
delete,modify, and add
tables and columns in my gui program in addition to being able to
execute sql statements 
(which I already have).
There are a dozen of these programs out there now but I wish to make my
own.

thanks for helping me dave,
marvin

-Original Message-
From: David M. Cook [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, January 20, 2004 8:11 PM
To: [EMAIL PROTECTED]
Subject: Re: [sqlite] basic table level stuff


On Tue, Jan 20, 2004 at 05:15:15PM -0500, jim wrote:

> if they already have these constraints at the column level, why do 
> they need them a second time as in ... name [type] [[CONSTRAINT name] 
> column-constraint]*

Because you may want a composite primary key or set of unique columns.
A simple example is

CREATE TABLE person (
   person_id INTEGER PRIMARY KEY
   lastname TEXT,
   firstname TEXT,
   UNIQUE (lastname, firstname)
);

Dave Cook

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]



-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] basic table level stuff

2004-01-20 Thread David M. Cook 
On Tue, Jan 20, 2004 at 05:15:15PM -0500, jim wrote:

> if they already have these constraints at the column level,
> why do they need them a second time as in ...
> name [type] [[CONSTRAINT name] column-constraint]*

Because you may want a composite primary key or set of unique columns.  A
simple example is

CREATE TABLE person (
   person_id INTEGER PRIMARY KEY
   lastname TEXT,
   firstname TEXT,
   UNIQUE (lastname, firstname)
);

Dave Cook

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]