Re: [sqlite] How to refer to a multiple-column primary key (PK) as 1 column (field) name
On 5/07/2009 5:49 AM, James Scott wrote: > I have the following: > > CREATE TABLE [Sections] ( > [Department] varchar NOT NULL COLLATE NOCASE, > [Course] varchar NOT NULL COLLATE NOCASE, > [Section] varchar NOT NULL COLLATE NOCASE, > [Class_Time] timestamp, > [I_Id] varchar COLLATE NOCASE, > [Room] varchar COLLATE NOCASE, > CONSTRAINT [sqlite_autoindex_Sections_1] PRIMARY KEY ([Department], > [Course], [Section])); > > CREATE INDEX [PK_Sections] ON [Sections] ([Department], [Course], [Section]); Ummm, after those two statements, you have TWO indexes on your 3 fields. sqlite> .header on sqlite> select * from sqlite_master where type = 'index'; type|name|tbl_name|rootpage|sql index|sqlite_autoindex_Sections_1|Sections|3| index|PK_Sections|Sections|4|CREATE INDEX [PK_Sections] ON [Sections] ([Department], [Course], [Section]) sqlite> What are you trying to achieve? > In the programming language, I need to refer to the primary key as 1 field. And "the programming language" (why the mystery? which language?) doesn't support concatenation of strings? Or better, e.g. Python's tuple pk = (department, course, section) which can be used as a dictionary key or a set element or a sort key or ... and can be easily picked apart to recover the parts: department, course, section = pk > Does Sqlite allow a 'calculated field', such as concatenation of the 3 > columns in the PK? Of course. SQL has allowed it since the year dot. http://www.sqlite.org/syntaxdiagrams.html#result-column "expr" => expression ... do what you want. HTH, John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to refer to a multiple-column primary key (PK) as 1 column (field) name
On 4 Jul 2009, at 8:49pm, James Scott wrote: > CREATE INDEX [PK_Sections] ON [Sections] ([Department], [Course], > [Section]); > > In the programming language, I need to refer to the primary key as 1 > field. > Does Sqlite allow a 'calculated field', such as concatenation of the 3 > columns in the PK? Or can I create a View to combine the 3? SELECT department||course||section FROM sections or SELECT department||course||section FROM sections ORDER BY department,course,section whichever suits you best. You can use '||' to concatenate anywhere an expression is allowed. As usual, it's not your job to work out which index is best: SQLite does it for you. And because of the way SQLite works it will never actually look at the table to find those values: it needs to use the index anyway and will realise it can take the values directly from the index without having to do the extra lookup to get them from the table. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to refer to a multiple-column primary key (PK) as 1 column (field) name
I have the following: CREATE TABLE [Sections] ( [Department] varchar NOT NULL COLLATE NOCASE, [Course] varchar NOT NULL COLLATE NOCASE, [Section] varchar NOT NULL COLLATE NOCASE, [Class_Time] timestamp, [I_Id] varchar COLLATE NOCASE, [Room] varchar COLLATE NOCASE, CONSTRAINT [sqlite_autoindex_Sections_1] PRIMARY KEY ([Department], [Course], [Section])); CREATE INDEX [PK_Sections] ON [Sections] ([Department], [Course], [Section]); In the programming language, I need to refer to the primary key as 1 field. Does Sqlite allow a 'calculated field', such as concatenation of the 3 columns in the PK? Or can I create a View to combine the 3? Any help is greatly appreciated. Thanks, Bradley ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
