multiple-column primary keys

[Toni Casueps]

The primary key of some of my tables is composed of two fields, and I don't know how to tell that to a Rekall form.
If I set Unique key to Auto in the block properties it seems to choose just one of the fields, which may not be always unique.
I've read the manual to see how to use the Column PreExpression and Column PostExpression but it's not explained too well.
Does someone know how to do this?


(Rekall 2.4.6 for Linux / PostgresSQL 8.0.8 Linux)
_________________________________________________________________
MSN Noticias
http://noticias.msn.es/comunidad.aspx_______________________________________________
Rekall mailing list
Rekall@...
http://www.mailman.a-i-s.co.uk/cgi-bin/mailman/listinfo/rekall

[Julian-27]

Hi, Rekall doesn't support multiple column keys directly afaik. On another
note I have also had issues with the pre/post expression setup. It might of
been fixed though.
A workaround with multiple expression might be useful in populating a row
within postgresql itself, in otherwords we are not using rekall to
retrieve/generate/insert any key values (which is fine). Rekall just loads
the data with the desired row active. This is what I tried to do but it
didn't see to work how I wanted it. Its also where transactions might be
useful (user cancel).
Later on I've found multiple key columns kind of messy to deal with and
normalized them out of necessity, but there are probably a number of
workarounds both within postgresql or some rekall py script.

From the manual:
--------------
Auto
If this option is chosen, Rekall will select what it sees as the best column
to use, either a primary key column or a unqiue column.
Primary Key
In this case Rekall will only allow a primary key column to be used. A second
combobox will appear containing just the primary key column, if any. When the
form is run, Rekall will verify that the column is a primary key.
Unique key
In this case, Rekall allows any unique column to be used. A second combobox
will show all unique columns in the table, and will include the primary key
column if any. When the form is run, Rekall will verify that the column is
indeed unique.
Any single column
Choosing this option will display a second combobox which shows all columns in
the table. Rekall will then assume that the selected column is unique,
whether or not it appears to be so in the database. It will also assume that
an inserted or updated value is not changed by any server database triggers.
Pre-expression
Use an SQL select expression to generate a key value.
Post-expression
Use an SQL select expression to retrieve key value.

The any single column setting is useful if you know that a particular column
will always be unique. However, be warned that Rekall will use the value from
this column when updating or deleting rows in the table; if the column turns
out not to be unique then unexpected results may occur ( For instance,
suppose you tell Rekall that column ClientCode in table ClientTable is
unique, and then in a form you delete a row that has UNKNOWN as the value of
the ClientCode column. To do this, Rekall will execute the SQL query delete
from ClientTable where ClientCode = 'UNKNOWN'. This will delete as many rows
as match, which might not just be the row you intended! ) .
The pre-expression setting allows you to specify an arbitrary SQL select query
which Rekall will use to generate a key value, which is then used in an
insert query. The select should return a single row containing a single
column. This setting is useful if, for instance, you need to retrieve a key
value from a sequence.
The post-expression setting is similar, except that the expression is
evaluated after an insert and should return the key value. This can be used
when inserting through a view where a database rule generates the key. For
instance, using PostgreSQL with the rule defined below (which traps inserts
into the view v_NoPKey_insert to insert into the underlying table NoPKey),
the post-expression would be select currval('NoPKey_Seq').
------------

Jules


On Wed, 20 Feb 2008, Toni Casueps wrote:

> The primary key of some of my tables is composed of two fields, and I don't
> know how to tell that to a Rekall form. If I set Unique key to Auto in the
> block properties it seems to choose just one of the fields, which may not
> be always unique. I've read the manual to see how to use the Column
> PreExpression and Column PostExpression but it's not explained too well.
> Does someone know how to do this?
>
>
> (Rekall 2.4.6 for Linux / PostgresSQL 8.0.8 Linux)
> _________________________________________________________________
> MSN Noticias
> http://noticias.msn.es/comunidad.aspx______________________________________
>_________ Rekall mailing list
> Rekall@...
> http://www.mailman.a-i-s.co.uk/cgi-bin/mailman/listinfo/rekall

_______________________________________________
Rekall mailing list
Rekall@...
http://www.mailman.a-i-s.co.uk/cgi-bin/mailman/listinfo/rekall