1

I have connected with my database through two programs: MySql Workbench and Sequel Pro (It's a ClearDB MySQL database hosted on heroku). I have created a simple table:

CREATE TABLE Country( 
    CountryID int AUTO_INCREMENT NOT NULL, 
    Name varchar(50), 
    PRIMARY KEY(CountryID)
);

and then I added a few entries:

INSERT INTO Country(Name) VALUES ("Poland");
INSERT INTO Country(Name) VALUES ("Germany");
INSERT INTO Country(Name) VALUES ("Sweden");
INSERT INTO Country(Name) VALUES ("France");

the problem is when I do SELECT * FROM city, I get the results as below, where the PRIMARY KEY is being auto incremented in alphabetical/lexicographical order:

1, Poland
21, Germany
31, Sweden
41, France

And I want the PRIMARY KEY to be 1, 2, 3, 4 etc.

I can't find any settings for that, nor any posts that would suggest why is this happening. Anyone has got any clue?

Nguyễn Văn Phong
  • 13,506
  • 17
  • 39
  • 56
Dominika
  • 187
  • 3
  • 13
  • The primary key is being incremented by 10 each time, instead of 1. But since the only job of an auto-number primary key is to guarantee row uniqueness, what's the difference? – Honeyboy Wilson Jan 24 '20 at 22:20
  • I am using this as a reference for foreign key in another table, and it's easier to me keep the track of the numbers if I go one by one. On my local version od mysql primary keys are being created with numerical order. – Dominika Jan 24 '20 at 22:30

1 Answers1

2

I don't understand what you mean by this:

the problem is when I search for the contents in this table they are being created with PRIMARY KEY in alphabetical/lexicographical order

But based on your sample data, the keys are being assigned in the order that the data was entered, which is exactly what one would expect. As to why there are jumps of ten between key values, it's intentional on the part of ClearDB:

When I use auto_increment keys (or sequences) in my database, they increment by 10 with varying offsets. Why?

ClearDB uses circular replication to provide master-master MySQL support. As such, certain things such as auto_increment keys (or sequences) must be configured in order for one master not to use the same key as the other, in all cases. We do this by configuring MySQL to skip certain keys, and by enforcing MySQL to use a specific offset for each key used. The reason why we use a value of 10 instead of 2 is for future development.

Community
  • 1
  • 1
Eric Brandt
  • 7,886
  • 3
  • 18
  • 35
  • Sorry, I wrote it in an unclear way. What I mean is that PRIMARY KEY is being incremented in alphabetical order instead of numerical, so instead of getting them PRIMARY KEY values 1, 2, 3, 4, 5 I get 1, 21, 31, 41 etc. – Dominika Jan 24 '20 at 21:54
  • And is there a way to force ClearDB to auto increment in numerical order? – Dominika Jan 24 '20 at 21:56
  • I guess I have to ask what are you actually trying to accomplish with this? Primary key values have no significance. You could use a GUID which is barely human readable, but perfectly valid as a primary key. – Eric Brandt Jan 24 '20 at 22:06
  • I'm relatively new to SQL, so maybe my logic is not yet logical, but I am creating further tables, which have as foreign key the primary key of the first table, and it's just easier for me to assign those numbers when they are increased as 1,2,3,4,5 etc. :) On my local mysql which I run in terminal (Mac) everything is created in the numerical order, that's why I was surprised here it wasn't :) – Dominika Jan 24 '20 at 22:24
  • I understand now. Thank you! :) Easier for the initial set up? Yes, you are correct. But the database you’re using isn’t going to be your friend for that step. You will adapt, I’m sure! – Eric Brandt Jan 25 '20 at 02:00