How can we create customize primary key having value with some character like EMP0001, EMP0002 in rails

Question

How can we create customize primary key having value with some character like EMP0001, EMP0002 in rails?

Do you want it to be auto incremented every time ? – Shyam Bhimani Jun 24 '16 at 06:00 — Shyam Bhimani, Jun 24 '16 at 06:00

score 0 · Accepted Answer · answered Jun 24 '16 at 06:31

You can use Sequence and default value for your column

postgres=# CREATE SEQUENCE 'xyz';

CREATE SEQUENCE 
postgres=# SELECT setval('xyz', 0001);

setval
-------
0001
postgres=# CREATE TABLE Employee(employee_id text PRIMARY KEY 

                                          CHECK (employee_id ~ '^EMP[0-9]+$')
                                          DEFAULT 'EMP' || nextval('xyz'),
Employee_Name text);

CREATE TABEL
postgres=# insert into Employee(Employee_Name) values('Peter');
INSERT 0 1

postgres=# insert into Employee(Employee_Name) values('Tom');
INSERT 0 1

postgres=# select * FROM Employee;

employee_id   |  Employee_Name
-----------+-------------
EMP0001    |  Peter
EMP0002    |  Tom

How we can achieve the same with rails, You have written the postgresql script, Can you please explain the same thing with rails migration. — Bharat soni, Jun 24 '16 at 06:38
for that you can refer here http://stackoverflow.com/questions/1200568/using-rails-how-can-i-set-my-primary-key-to-not-be-an-integer-typed-column @Bharatsoni — Shyam Bhimani, Jun 24 '16 at 06:56

Bharat soni · Answer 2 · 2016-06-24T06:02:49.793

As I know that you can't create the primary keys in the mysql like as you required.

Because you have a combination of the number and string in primary key, and that type of facility I didn't see in any database.

I have one alternate solution to complete your requirement as below steps...

You just create one field which holds the actual primary key like "EMP0001" as string and set it's default value "".
Now create a new filed that name you can take whatever you required, and set that as primary key true and auto, So that the number will automatically increased every time same as the 'ID' field.
Now create a callback, set it after create and combine the string with the point 2 number field and save into the point 1 field.

Example:

def set_key
 "EMP000#{point-2-field-value}"
end

That's it, Now you can give that field to any model as foreign_key for others.

How can we create customize primary key having value with some character like EMP0001, EMP0002 in rails

2 Answers2