How to set a unique primary key for an entire table in the same database

Question

How to set a unique primary key for the entire database table? for example, I do not want to repeat the primary key of another table.

table A: ---------- id | name ---------- 1 | aaa 3 | bbb 5 | ccc table B: ------------- id | surname ------------- 7 | ddd 2 | eee 9 | fff table C: ------------- id | nickname ------------- 4 | ggg 6 | hhh 8 | iii 

all id are primary key and auto_increment . All data is entered dynamically. I am using MYSQL in PHPMYADMIN .

Answer 1

You can add a new table to your schema called ID_Table , which will have only one numeric column called current_id with a default value of 0, when adding a new row to any other schema table that you should call, select ID_Table, returning ID_Table.current_id + 1 as the new id value. Then you need to update ID_Table

 Update ID_Tableset ID_Table.current_id = ID_Table.current_id + 1 

the GetNewId function can be implemented by locking ID_Table
ID_Table Update
return newid

something like this (I used Oracle syntax)

 create table ID_Table( current_id number ); Insert into ID_Table values(0); CREATE OR REPLACE Function GetNewId RETURN number is new_id ID_Table.current_id%type; row_count number; begin select nvl(ID_Table.current_id, 0) + 1 INTO new_id FROM ID_Table for update; update ID_Table set ID_Table.Current_Id = new_id; commit; RETURN new_id; end GetNewId; 

Answer 2

Use the same sequence as the identifier generator for each row inserted, regardless of the table. Assuming you are using a database that allows you to name a sequence as an identifier generator for a field.

It looks like it will do what you want in MySQL: http://devzone.zend.com/1786/mysql-sequence-generator/

Answer 3

You can get the maximum ID from all three tables, and then add it to your insert request. But you must remove the auto_increment attribute.

 INSERT INTO TableA SELECT MAX(ID)+1, 'jjj' FROM (SELECT MAX(ID) AS ID FROM TableA UNION SELECT MAX(ID) AS ID FROM TableB UNION SELECT MAX(ID) AS ID FROM TableC ) A; 

See this SQLFiddle

Answer 4

Look at using sequence. I am not sure which database you are using. Postgresql and Oracle have a sequence with which you can share between tables.