Mysql – generate unique primary key for all clients from the local database and NOT the server

distributed-databasesinnodbMySQLprimary-key

I'm using MySQL Innodb database to work as a distributed database among many places and also each place will work offline but at the end all the data will be centralized in one server,
I want to generate a unique primary key for registration table that will be unique for all clients, first I thought about int auto_increment but then I realized that this will cause conflict between each client as all of them will generate a new auto_increment from their local database and not from server so in this case all will start from 1 and increment for each new register

now I'm thinking of using a CHAR or VARCHAR to put a prefix for each registration in client's local database, or is there another way to do so?

Best Answer

If there are no natural way of identifying a registration, you may want to have a look at GUID:

https://en.wikipedia.org/wiki/Globally_unique_identifier

The upside is that there are plenty of libraries dealing with them. The downside is that they occupy space which may lead to higher B-Tree indexes, i.e. additional I/O. More read I/O is also required because GUID's are evenly distributed, when you read a page the likelihood that you get a related row is low which causes additional pages to be read.

Another option is to add another attribute representing the host and use this in combination with your sequence as an identifier.

primary key(host_id, seq_id)

Make sure that no two host uses the same host_id.

Yet a third option is to partition the seq_id for different hosts. I.e. host 1 uses seq_id 1..n, host 2 uses seq_id n+1..2n and so forth. Not an alternative for the heart of fainted ;-)

Related Solutions

Mysql – Auto-increment key and Foreign Key as composite for relational database

You don't need composite keys to enforce your referential integrity in your case. The reason is that you have a pretty straight-forward three tier hierarchy:

PATIENT 
   +
   |
   ^
 TEST 
   +
   |
   ^
SAMPLE

Your SAMPLE table just needs a simple foreign key to your TEST table and your TEST table just needs a simple foreign key to your PATIENT table.

This works because each sample record needs a test record and each test needs a patient. If you delete a patient, their tests must be cascade deleted. If you delete a test its samples must be cascade deleted. Therefore you don't need patient_id in the TEST table. All you need is to declare cascade deletion on your foreign keys, like so...

...
CONSTRAINT 'FK_TEST__PATIENT' FOREIGN KEY ('patient_id')
      REFERENCES 'PATIENT' ('patient_id') ON DELETE CASCADE
...
CONSTRAINT 'FK_SAMPLE__TEST' FOREIGN KEY ('test_id')
      REFERENCES 'TEST' ('test_id') ON DELETE CASCADE
...

Sql-server – Non-sequential Primary Key performance in PostgreSQL 9.3

I have 3 solutions for you:

Direct reference of sequence and using concat

One possible solution is to reference the seqence in insert statement directly and prepend your node-id. A similar question including answer you can find here: https://stackoverflow.com/a/17925601/4206293

Using a UUID

Another possible solution is, if you don't need you node-id in the primary-key field, you can use the uuid-ossp extension which provides the type uuid and the functions to generate uuids: http://www.postgresql.org/docs/9.4/static/uuid-ossp.html

Use a trigger for these solutions

For both solutions: you can use a trigger to set the primary key.

Example:

-- table
CREATE TABLE test(
   id character varying(10) NOT NULL,
  "name" character varying,
   CONSTRAINT idx_pk PRIMARY KEY (id)
);

-- seqence
CREATE SEQUENCE test_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

-- Create Function
CREATE OR REPLACE FUNCTION insert_trigger() 
RETURNS TRIGGER    
AS $$
BEGIN
   NEW.id := '100' || nextval('test_seq')::TEXT;
   RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';

-- add Trigger
CREATE TRIGGER insert_table_trigger
BEFORE INSERT ON pkTable
FOR EACH ROW EXECUTE PROCEDURE insert_trigger();

If you now insert a value in the table

INSERT INTO test (name) VALUES ('test text');

you get your primary key '1001'

Using MINVALUE and MAXVALUE

Another way is you use the MINVALUE and MAXVALUE of SEQUENCE to define an numeric space:

CREATE SEQUENCE node100_seq
  INCREMENT 1
  MINVALUE 100000000000
  MAXVALUE 100999999999
  START 100000000000
  CACHE 1;

CREATE SEQUENCE node200_seq
  INCREMENT 1
  MINVALUE 200000000000
  MAXVALUE 200999999999
  START 200000000000
  CACHE 1;