Yeah, the question is: how 'country' can be a UNIQUE KEY if countries have
hundreds of cities. In the table, hundreds of cities will have the 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 [email protected].
For more options, visit https://groups.google.com/groups/opt_out.
