This table does not contain a unique column

Databases
1

Hello, I am not so well known with mysql, but here is the thing.

In my database I created a table named: province_cities

After some troubles I managed to import my list of province and city
Structure is: id 1 = “Province”, id 2 = “City”. Both Type text.

But as you can see on the topic title, I am not able to do anything but read it. lol. And maybe sometime the names will change.

I need this table to perform postings by selecting a Province and making a choice of one belonging city.

Any suggestions would help me much.

ps. I am using Joomla 3.2 with SEBLOD.

2

not sure what you’re asking

please do a SHOW CREATE TABLE for your table

the unique key will likely be a composite key consisting of both province and city

3

If you mean Show Created Table, then I attach the image on this post. Hope it helps…

4

Just an update >> From a example I see on internet they had 2 tables. One is for US states (in my case Provincies), and the other was for cities with State Letters (in my case Gemeenten with Provincie Letters).

And what I would like to establish is through Joomla have a selectbox with choices Provincies, and eventually make a choice out of Gemeenten inside that Provincie.
I hope this makes sense. Can anyone tell me if this is the correct way?

First table is called ‘provincies’:

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for provincies
-- ----------------------------
DROP TABLE IF EXISTS `provincies`;
CREATE TABLE `provincies` (
  `provincie` varchar(22) NOT NULL,
  `provincie_code` char(2) NOT NULL,
  PRIMARY KEY (`provincie_code`)
) ENGINE=MyISAM;

-- ----------------------------
-- Records 
-- ----------------------------
INSERT INTO `provincies` VALUES ('Drente', 'DR');

Second table is called ‘gemeenten’:

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for gemeenten
-- ----------------------------
DROP TABLE IF EXISTS `gemeenten`;
CREATE TABLE `gemeenten` (
  `gemeente` varchar(50) NOT NULL,
  `provincie_code` char(2) NOT NULL,
  KEY `idx_provincie_code` (`provincie_code`)
) ENGINE=MyISAM;

-- ----------------------------
-- Records 
-- ----------------------------
INSERT INTO `gemeenten` VALUES ('Aa en Hunze', 'DR');
5

you are definitely on the right track, and those tables will do the job nicely

to complete, you should declare that gemeente is a PRIMARY KEY

also, provincie_code in gemeenten should be a FOREIGN KEY referencing provincie_code in provincies

6

Hello, thanks for thinking with me.

I do have a problem with that. I tried in table Gemeenten to set Gemeente as Primary key, and it says: duplicate entry “Bergen” for key “PRIMARY”.
I think the reason is that I have names which often have the same name inside: Bergen, Haaksbergen, Bergen op Zoom, etc.
And, I don’t have id’s inside that table, should I have those?

Maybe it is easier if I sned the tables?

7

this error message is pretty clear – you have two rows with the exact same value

take a look, you will find them

no!!

sorry, i have no idea what “sned” is

8

Hi thanks again,
ps. ‘sned’ was ment to be ‘send’. lol

I checked the entries and see inside some Provincie(s) it has a Gemeente with the exact same name… bummer.

How can I accomplish this. Do I need to make it just one table?
Users should enter : Gemeente and I need to display: Gemeente | Provincie
After that they need to be able to click Provincie and display a list of ALL inside Provincie
And they need to be able to click Gemeente and display a list of ALL inside Gemeente

9

not really a problem at all

CREATE TABLE gemeenten 
( gemeente VARCHAR(50) NOT NULL
, provincie_code CHAR(2) NOT NULL
, PRIMARY KEY ( gemeente , provincie_code )
, FOREIGN KEY ( provincie_code )
         REFERENCES provincies ( provincie_code )
);

by the way, make your tables innodb instead of myisam, so that the foreign key checking is enabled

10

Very good, Thank you so much!

ps do you have an example on how to test this, I mean, how I can call a Gemeente and also see the Provincie? I am really just starting with db.

11 
SELECT p.provincie
     , g.gemeente
  FROM provincies AS p
INNER
  JOIN gemeenten AS g
    ON g.provincie_code = p.provincie_code 
 WHERE g.gemeente LIKE 'Bergen%'
12

Thanks, really cool.
So in general language it would mean something as:
select provincie and gemeente
from table provincies use provincie
from table gemeenten use gemeente
connect those two by provincie_code
return the provincie and gemeente only if gemeente has the word Bergen inside

I do not know so much of querying a databse, do you have some websites i could go to learn. I really do not want to take all your time…

13

here’s one :slight_smile:

disregard the section on leading commas – the code display is badly broken there