Re: [sqlite] Is Column UNIQUE? How To

[Dennis Cote]

Firman Wandayandi wrote:

Hi,

Is any possible way to know if a column is UNIQUE without "PRAGMA
index_info('')"? Seems "PRAGMA table_info('')"
doesn't returns the unique flag of column.

Thanks for advice.

Firman,

You should try pragma index_list(''). It returns a list of 
all the indexes (or indices) on the specified table. It provides a flag 
for each unique index as well as the index names. You can then get the 
details about the number, and order of the columns in the index using 
pragma index_info.


Here is a short sample session with the sqlite shell.

   SQLite version 3.3.8
   Enter ".help" for instructions
   sqlite> create table t (a integer primary key, b text unique);
   sqlite> .header on
   sqlite> .mode column
   sqlite> pragma table_info('t');
   cid nametypenotnull dflt_value  pk
   --  --  --  --  --  --
   0   a   integer 0   1
   1   b   text0   0
   sqlite> pragma index_list('t');
   seq name  unique
   --    --
   0   sqlite_autoindex_t_1  1
   sqlite> pragma index_info('sqlite_autoindex_t_1');
   seqno   cid name
   --  --  --
   0   1   b

HTH
Dennis Cote

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Is Column UNIQUE? How To

[Jay Sprenkle]

On 12/16/06, Firman Wandayandi <[EMAIL PROTECTED]> wrote:

Yeah, as I thought before. Well nevermind, I should parse the table schema then.
Thanks


You're welcome.

--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Is Column UNIQUE? How To

[Firman Wandayandi]

On 12/17/06, Jay Sprenkle <[EMAIL PROTECTED]> wrote:

On 12/16/06, Firman Wandayandi <[EMAIL PROTECTED]> wrote:

> I meant, I want retrieve the information of the column or schema as
> much as possible, so I can build a new schema based on it. Then I need
> to know if column is a UNIQUE or not and so on.


select * from sqlite_master;

It tells you about the schema



Yeah, as I thought before. Well nevermind, I should parse the table schema then.
Thanks

--
Firman Wandayandi 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Is Column UNIQUE? How To

[Jay Sprenkle]

On 12/16/06, Firman Wandayandi <[EMAIL PROTECTED]> wrote:


I meant, I want retrieve the information of the column or schema as
much as possible, so I can build a new schema based on it. Then I need
to know if column is a UNIQUE or not and so on.



select * from sqlite_master;

It tells you about the schema







--
--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Is Column UNIQUE? How To

[Firman Wandayandi]

Hi Jay,

On 12/17/06, Jay Sprenkle <[EMAIL PROTECTED]> wrote:

On 12/16/06, Firman Wandayandi <[EMAIL PROTECTED]> wrote:
> Hi,
>
> Is any possible way to know if a column is UNIQUE without "PRAGMA
> index_info('')"? Seems "PRAGMA table_info('')"
> doesn't returns the unique flag of column.
>
> Thanks for advice.

this will show you the count of duplicated values for a column:

select mycol, count(*)
 from mytable
group by mycol



I meant, I want retrieve the information of the column or schema as
much as possible, so I can build a new schema based on it. Then I need
to know if column is a UNIQUE or not and so on. Such as:

mycol INTEGER NOT NULL UNIQUE

There is another idea, that is parse the "sql" column from
"sqlite_master" table. But I'm searching for a right solution.
--
Firman Wandayandi 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Is Column UNIQUE? How To

[Jay Sprenkle]

On 12/16/06, Firman Wandayandi <[EMAIL PROTECTED]> wrote:

Hi,

Is any possible way to know if a column is UNIQUE without "PRAGMA
index_info('')"? Seems "PRAGMA table_info('')"
doesn't returns the unique flag of column.

Thanks for advice.


this will show you the count of duplicated values for a column:

select mycol, count(*)
from mytable
group by mycol



--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Is Column UNIQUE? How To

[Firman Wandayandi]

Hi,

Is any possible way to know if a column is UNIQUE without "PRAGMA
index_info('')"? Seems "PRAGMA table_info('')"
doesn't returns the unique flag of column.

Thanks for advice.
--
Firman Wandayandi 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-