how to start Primary key from a predetermined number?

Navigation
0

Greetings, I'm in the installation of my degree project, it's a web system it should be installed in the cloud, but as the institution for which the project is carried out does not have an internet service and has several offices scattered throughout the city, the system is being installed locally in each dispersed office.

Therefore I need that in each place the primary keys start from a different number to 1 (this is my idea and I'm not sure if it was ok), since in the end all the information will be centralized and I'm more than sure that doing this will give me error or replace the data with ids from another place.

Table employees 

CREATE TABLE 'employers' (
  'id' int(10) UNSIGNED NOT NULL,
  'items' varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  'name' varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  'first_name' varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL, 
  'last_name' varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  'ci' varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  'gender' enum('f','m','o') COLLATE utf8_unicode_ci NOT NULL,
  'title' varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  'birthday' date DEFAULT NULL,
  'street_live' varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  'birthplace' varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  'cel_phone' varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  'home_phone' varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  'office_phone' varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  'email' varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  'cardex_number' varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  'photo' varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'user.png',
  'position' varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  'active' tinyint(1) NOT NULL,
  'unidad_id' int(10) UNSIGNED NOT NULL,
  'created_at' timestamp NULL DEFAULT NULL,
  'updated_at' timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
ALTER TABLE 'employers'
  ADD PRIMARY KEY ('id'),
  ADD KEY 'employers_unidad_id_foreign' ('unidad_id');

What I need

  

Place 1 employee ids start from 1
  Place 2 employee ids start from 5000
  Place 3 the ids of employee start from 10000
  Place 4 employee ids start from 15000

I hope it has been understood, and if someone had another solution more practical I would appreciate it.

    
asked by Shassain 14.09.2017 в 13:54
source

3 answers

1

You can specify at the beginning that your field is primary key, that it is autoincrement and at the end of CREATE TABLE you indicate how much it has to increase.

In the first case you do not indicate anything at the end, in the other cases, for example, for '5000 ': 

CREATE TABLE IF NOT EXISTS  'employers5000_20170914'  
(
    'id' INT AUTO_INCREMENT PRIMARY KEY,
    'name' varchar(100) COLLATE utf8_unicode_ci NOT NULL
) 
    ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
    AUTO_INCREMENT = 5000
;

Let's see a complete test.

Código: Ver demo en Rextester 

CREATE TABLE IF NOT EXISTS  'employers1_20170914'  
(
    'id' INT AUTO_INCREMENT PRIMARY KEY,
    'name' varchar(100) COLLATE utf8_unicode_ci NOT NULL
) 
    ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
;

INSERT INTO 'employers1_20170914' ('name')
    VALUES
    ('Pedro'),
    ('Santiago'),
    ('Juan')
;


CREATE TABLE IF NOT EXISTS  'employers5000_20170914'  
(
    'id' INT AUTO_INCREMENT PRIMARY KEY,
    'name' varchar(100) COLLATE utf8_unicode_ci NOT NULL
) 
    ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
    AUTO_INCREMENT = 5000
; 

INSERT INTO 'employers5000_20170914' ('name')
    VALUES
    ('Pedro-5000'),
    ('Santiago-5000'),
    ('Juan-5000')
;

CREATE TABLE IF NOT EXISTS  'employers10000_20170914'  
(
    'id' INT AUTO_INCREMENT PRIMARY KEY,
    'name' varchar(100) COLLATE utf8_unicode_ci NOT NULL
) 
    ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
    AUTO_INCREMENT = 10000
; 

INSERT INTO 'employers10000_20170914' ('name')
    VALUES
    ('Pedro-10000'),
    ('Santiago-10000'),
    ('Juan-10000')
;



CREATE TABLE IF NOT EXISTS  'employers15000_20170914'  
(
    'id' INT AUTO_INCREMENT PRIMARY KEY,
    'name' varchar(100) COLLATE utf8_unicode_ci NOT NULL
) 
    ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
    AUTO_INCREMENT = 15000
; 

INSERT INTO 'employers15000_20170914' ('name')
    VALUES
    ('Pedro-15000'),
    ('Santiago-15000'),
    ('Juan-15000')
;

SELECT * FROM 'employers1_20170914';
SELECT * FROM 'employers5000_20170914';
SELECT * FROM 'employers10000_20170914';
SELECT * FROM 'employers15000_20170914';

Resultado:

Starting at 1 

id      name
1       Pedro
2       Santiago
3       Juan

Starting at 5000 

id      name
5000    Pedro-5000
5001    Santiago-5000
5002    Juan-5000

Starting at 10000 

id      name
10000   Pedro-10000
10001   Santiago-10000
10002   Juan-10000

Starting at 15000 

id  name
15000   Pedro-15000
15001   Santiago-15000
15002   Juan-15000
    
answered by 14.09.2017 / 16:05
source
3

Dear,

In the property IDENTITY of the field id you say from what number you want to start assigning and the increase of it.

For Place 1 it would be:

id int(10) IDENTITY(1,1) UNSIGNED NOT NULL

For Place 2:

id int(10) IDENTITY(5000,1) UNSIGNED NOT NULL

For Place 3:

id int(10) IDENTITY(10000,1) UNSIGNED NOT NULL

And so on.

    
answered by 14.09.2017 в 14:58
2
'id' int(10) IDENTITY (5000, 1) UNSIGNED NOT NULL,

In this case you tell it to start from the id number 5000 and that each record is incremented by 1.

The same for the rest: 

'id' int(10) IDENTITY (10000, 1) UNSIGNED NOT NULL,
'id' int(10) IDENTITY (15000, 1) UNSIGNED NOT NULL,
    
answered by 14.09.2017 в 13:59
CSS does not select the child [closed] QUERY SQL - Separate String