Hi Lee,
Am 15.05.2012 18:51, schrieb Lee Hachadoorian:
On Tue, May 15, 2012 at 8:31 AM, Bernhard Ströbl
<[email protected]> 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.
I agree with the last sentence (see below). To explain my opinion: My
users often try to pack a lot of information in the key field and I
always advise them to not do so because if the information changes they
have to change the key, too (normally they also want the information
redundantly in a couple of fields anyways, so the table is not
normalized then). From that experience _I_ would always advice to not
put any information in the key. Thus the key can stay the same for the
life cycle of a dataset.
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.
ok, but it does not harm to add another field, either, because as I
understood, the data referred to are not stored in the same database but
"come from an other application" hence my recommendation.
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.
I agree, so if the user enters a value it should be passed to the
database and not discarded. If it violates any database rules postgresql
complains anyways. So this could be a bug then.
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.
not confirmed here (PostgreSQL 8.4, QGIS 1.7.4)
primary key is integer with a nextval('sequence') default
adding features the key field defaults to nextval('sequence')
case 1) no change => nextval applied
case 2) change value => nextval applied (no error, though)
expected behaviour for case 2) would be to not apply the default
shall we file a ticket for this?
Bernhard
--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<[email protected]
<mailto:[email protected]>>
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
________ Information from NOD32 ________
This message was checked by NOD32 Antivirus System for Linux Mail Server.
http://www.nod32.com
_______________________________________________
Qgis-user mailing list
[email protected]
http://lists.osgeo.org/mailman/listinfo/qgis-user
