Logo Questions Linux Laravel Mysql Ubuntu Git Menu

how to set unique primary key for all table in same database

how to set an unique primary key to all the table of database?
for example i don't wanted to repeat any primary key of different 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 the data is entered dynamically.I am using MYSQL in PHPMYADMIN.

like image 369
Harjeet Jadeja Avatar asked Mar 24 '23 00:03

Harjeet Jadeja

1 Answers

You may add a new table to your schema called ID_Table that will have only one numeric column called current_id with default value of 0 ,when adding a new row to any other table of the schema you have to call a select on the ID_Table returning ID_Table.current_id + 1 as new id value. Then updating ID_Table must be done

Update ID_Tableset ID_Table.current_id = ID_Table.current_id + 1 

the GetNewId function could be implemented by locking the ID_Table
Updating ID_Table
returning NewID

something like this (I have 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;
  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;

  RETURN new_id;
end GetNewId;
like image 124
Mohsen Heydari Avatar answered Apr 02 '23 15:04

Mohsen Heydari