[Qgis-user] QGis 1.7.4 possible bug with PostgreSQL/Postgis manual integer primary key?
Lee Hachadoorian
Lee.Hachadoorian+L at gmail.com
Tue May 15 09:51:45 PDT 2012
On Tue, May 15, 2012 at 8:31 AM, Bernhard Ströbl
<bernhard.stroebl at jena.de> wrote:
>
>
> Am 15.05.2012 14:16, schrieb F T:
>
>> I think the QGis code should not have this specific behaviour and should
>> follow the standards.
>
>
> Is there a standard for this? From the db's point of view it is always the
> responsibility of the user to take care for proper primary keys. He can
> achieve this by a) using a sequence or b) using a software taking care of it
> (like QGIS) or c) error-prone manual entering.
The use of an arbitrary key is commonplace, but not part of the SQL
standard or required for normal form. Many table designs use
meaningful primary keys, defined from a unique key within the data. To
say that "A key is just a technical field used to identify a dataset.
It should not contain any information." is contentious. There is
specifically *not* a standard in SQL. I think the 'standard" Fabrice
is referring to is this, that keys are not prohibited from being
meaningful, and that the application should not just discard data that
it allows you to enter and which is not in violation of your table
design.
>> In my case, the values of the key come from an other application and I
>> have to type these values to link the geometrical objects to the
>> database informations.
>
>
> So this is a foreign key. I would recommend to use a dedicated field for
> this.
It is not unreasonable to want a foreign key in a one-to-one
relationship to match the primary key of the related table.
QGIS is not alone among GIS applications in requiring the use of a
unique integer key in source data, and of course, it can't tell the
difference between a key which is meaningless or meaningful in terms
of data design and how the data is used elsewhere. It is a little odd
that QGIS allows you to edit the field at all, since applications
which use surrogate keys (arbitrary, meaningless integer values)
usually also don't allow those keys to be edited. It's even more odd
that QGIS allows you to edit the field but then discards a value which
does not violate database rules.
BTW, I found I could *edit* the primary key field of a table with a
key field typed as serial, but when I tried to add a new feature, any
value entered manually generated a primary key violation. The column
value in QGIS defaulted to nextval('sequence_name'::regclass),
matching the column default in the table definition, but also
generated a primary key violation. When I cleared the field
completely, the row was added with the next available value from the
sequence.
--Lee
>
> Bernhard
>>
>>
>> I found a workaround : if I change the field type of the key from
>> integer to bigint then everything works as attended...
>> QGis doesnt tries anymore to give a value when we don't need it.
>> But this is not very logical isn't it?
>>
>> Fabrice
>>
>>
>> 2012/5/15 Bernhard Ströbl <bernhard.stroebl at jena.de
>> <mailto:bernhard.stroebl at jena.de>>
>>
>>
>> Hi Fabrice,
>>
>> I think this is the intended behaviour. If you leave it to the user
>> to enter the primary key then the db might complain about non-unique
>> keys. On the other hand why would you _want_ to assign a certain
>> key? A key is just a technical field used to identify a dataset. It
>> should not contain any information.
>> On my db I have three tables (point, line, polygon) that insert into
>> the same table representing a n:m relation. I use a sequence on the
>> db and a trigger to assign the next sequence value on INSERT. Apart
>> from that I have no need to interfere with my pk values and I am
>> glad, that QGIS takes care of that.
>>
>> Bernhard
>>
>> Am 15.05.2012 13:07, schrieb F T:
>>
>>
>> Hi all,
>>
>> We are not able to set explicitly the value of an integer
>> primary key field.
>>
>> It is possible to set this value with pgAdmin.
>> But when the feature is created with QGis, it takes the next max
>> value
>> of the field and not the spécified value...
>>
>> To reproduce this strange behaviour :
>>
>> -- create a table in the schema public with an integer primary key
>> CREATE TABLE the_table
>> (
>> the_key integer NOT NULL,
>> the_obs character varying(150),
>> geom geometry,
>> CONSTRAINT the_table_pkey PRIMARY KEY (the_key),
>> CONSTRAINT enforce_dims_geom CHECK (st_ndims(geom) = 2),
>> CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) =
>> 'MULTILINESTRING'::text OR geom IS NULL),
>> CONSTRAINT enforce_srid_geom CHECK (st_srid(geom) = 2154)
>> )
>> WITH (
>> OIDS=TRUE
>> );
>> -- spatialize the table
>> INSERT INTO geometry_columns(f_table___catalog, f_table_schema,
>>
>> f_table_name, f_geometry_column, coord_dimension, srid, "type")
>> VALUES('', 'public', 'the_table', 'geom', 2, 2154,
>> 'MULTILINESTRING');
>>
>> Then in QGIS :
>> - add this table to a project
>> - create 1 line with the_key=100 and save the edition -> open the
>> attribute table and you see the_key=0
>> - create 1 line with the_key=102 and save the edition -> open the
>> attribute table and you see the_key=1
>>
>> If I modify the value of the_key in pgAdmin, by example put the
>> value
>> 1000 instead of 1, then the next line created with QGis will
>> have the
>> value 1001.
>>
>> Any help is welcome !
>> Thanks
>>
>> Fabrice
>>
>>
>>
>>
>>
>> _________________________________________________
>> Qgis-user mailing list
>> Qgis-user at lists.osgeo.org <mailto:Qgis-user at lists.osgeo.org>
>> http://lists.osgeo.org/__mailman/listinfo/qgis-user
>>
>> <http://lists.osgeo.org/mailman/listinfo/qgis-user>
>>
>>
>>
>>
>> ________ Information from NOD32 ________
>> This message was checked by NOD32 Antivirus System for Linux Mail
>> Server.
>> http://www.nod32.com
>>
>>
>>
>>
>> _______________________________________________
>> Qgis-user mailing list
>> Qgis-user at lists.osgeo.org
>> http://lists.osgeo.org/mailman/listinfo/qgis-user
>>
>>
>> ________ Information from NOD32 ________
>> This message was checked by NOD32 Antivirus System for Linux Mail Server.
>> http://www.nod32.com
>
>
> --
> Bernhard Ströbl
> Anwendungsbetreuer GIS
>
> Kommunale Immobilien Jena
> Am Anger 26
> 07743 Jena
>
> Tel.: 03641 49- 5190
> E-Mail: bernhard.stroebl at jena.de
> Internet: www.kij.de
>
>
>
> Kommunale Immobilien Jena
> Eigenbetrieb der Stadt Jena
> Werkleiter: Thomas Dirkes
>
>
>
> ________ Information from NOD32 ________
> This message was checked by NOD32 Antivirus System for Linux Mail Server.
> http://www.nod32.com
> _______________________________________________
> Qgis-user mailing list
> Qgis-user at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/qgis-user
More information about the Qgis-user
mailing list