0

ID column set to primary key, auto increment.

I want to have a second column Project number, is it possible to set something like this in SQL ? Or how should I do this ?

ID: 1 projectnumber:Project001

ID: 2 projectnumber:Project002

.

.

.

ID: n projectnumber: Project00n

yaang
  • 3
  • 2
  • You don't even need to have the column, just format this when you pull the data out and when you display it in the application.. All you're doing is duplicating string `Project` and adding a number next to the title - there's very, very little point in wasting space like that. – Mjh Jul 24 '17 at 11:45
  • Sounds like a calculated column: See https://stackoverflow.com/questions/5222044/column-calculated-from-another-column – aschoerk Jul 24 '17 at 11:47
  • MySQL 5.7 you can use a generated column (GENERATED ALWAYS) – Alex K. Jul 24 '17 at 11:48

1 Answers1

0

you could do that in 2 ways:

Either using a trigger or do that when retrieving the record from the database:

trigger: after insert

CREATE TRIGGER `yourtable_after_insert` AFTER INSERT ON `yourtable` FOR EACH ROW BEGIN
    UPDATE yourtable
     SET projectnumber = CONCAT('project', NEW.id) 
     WHERE id = NEW.id;
END;

or just do that CONCAT thing in your select query or even better in the logic of php. Consider the possibility you want to translate your application. You would store duplicate information as well...

as pointed out below: NEW.id will not work. So use LAST_INSERT_ID() instead:

CREATE TRIGGER `yourtable_after_insert` AFTER INSERT ON `yourtable` 
FOR EACH ROW BEGIN
    UPDATE yourtable
     SET projectnumber = CONCAT('project', LAST_INSERT_ID()) 
     WHERE id = LAST_INSERT_ID();
END;

but still: it would be duplicating content

Ivo P
  • 1,722
  • 1
  • 7
  • 18