Further with my monolog... Seems I need to implement this im my table
definition:
UNIQUE KEY `country` USING BTREE (`country`,`name`,`region`)
How do I do it in web2py?
On Monday, June 10, 2013 1:24:20 PM UTC+2, lesssugar wrote:
>
> Yeah, the question is: how 'country' can be a UNIQUE KEY if countries have
> hundreds of cities. In the table, hundreds of cities will belong to the
> same country, so the no wonder the contry code (which is two letters) will
> not be unique. I'm confused.
>
> On Monday, June 10, 2013 12:59:26 PM UTC+2, lesssugar wrote:
>>
>> OK, I'm starting to think it's a wrongly defined table. The table I would
>> like to import has following structure:
>>
>> CREATE TABLE `cities` (
>> `ID` int(8) unsigned NOT NULL auto_increment,
>> `country` char(2) NOT NULL,
>> `region` char(3) NOT NULL,
>> `url` varchar(50) NOT NULL,
>> `name` varchar(50) NOT NULL,
>> `latitude` double NOT NULL,
>> `longitude` double NOT NULL,
>> PRIMARY KEY (`ID`),
>> UNIQUE KEY `country` USING BTREE (`country`,`name`,`region`)
>> ) ENGINE=InnoDB AUTO_INCREMENT=207637 DEFAULT CHARSET=utf8;
>>
>> My 'city' table definition looks like this:
>>
>> db.define_table('city',
>> Field('id', length=8),
>> Field('country', length=2, unique=True),
>> Field('region', length=3),
>> Field('url', length=50),
>> Field('name', length=50),
>> Field('latitude', 'double'),
>> Field('longitude', 'double'),
>> format=lambda r: r.name
>> )
>>
>> Does someone see an error here?
>>
>> On Monday, June 10, 2013 12:35:23 AM UTC+2, lesssugar wrote:
>>>
>>> I'm aware this might be rather a postgresql question but maybe someone
>>> knows how to deal with it.
>>>
>>> I defined 'city' table
>>>
>>> db.define_table('city',
>>> Field('id', length=8),
>>> Field('country', length=2, unique=True),
>>> Field('region', length=3),
>>> Field('url', length=50),
>>> Field('name', length=50),
>>> Field('latitude', 'double'),
>>> Field('longitude', 'double'),
>>> format=lambda r: r.name
>>> )
>>>
>>> The table is empty and I'm trying to import data from CSV file using
>>> web2py's appadmin (the CSV provides fixed IDs).
>>>
>>> Every time I import the file I get the following error:
>>>
>>> *duplicate key value violates unique constraint "city_country_key"'*
>>> *
>>> *
>>> I found many similar problems on the net and one of the reasons causing
>>> this error was lack of sync between (in my case) 'city' and 'city_id_seq'.
>>> However the sync seems to be OK, because:
>>>
>>> A. select max(id) from city; -- returns nothing (the table 'city' is
>>> empty as I wrote)
>>> B. select nextval('city_id_seq'); -- returns an integer value
>>>
>>> So the result of B. > the result of A. which would suggest it's not a
>>> sync problem. I've been fighting with this all day. If someone knows what's
>>> going on, please let me know.
>>>
>>>
--
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
