In SQL we will be having a sequence. But it should be appended to a variable like this
M1,M2,M3,M4....
Any way of doing this ?
+9
A:
Consider having the prefix stored in a separate column in the table, e.g.:
CREATE TABLE mytable (
idprefix VARCHAR2(1) NOT NULL,
id NUMBER NOT NULL,
CONSTRAINT mypk PRIMARY KEY (idprefix, id)
);
In the application, or in a view, you can concatenate the values together. Or, in 11g you can create a virtual column that concatenates them.
I give it 99% odds that someone will say "we want to search for ID 12345 regardless of the prefix" and this design means you can have a nice index lookup instead of a "LIKE '%12345'".
Jeffrey Kemp
2010-02-19 07:52:14
+1 for avoiding a smart key in favour of a composite key.
APC
2010-02-19 08:13:02
+1 to answer and APC comment - intelligent keys will usually be considered a bad decision after you live with the design for a while
dpbradley
2010-02-20 12:35:50
So why not create a function index that avoids the prefix? The prefix might actually be useful. For example:to denote a row prior to a major event like a conversion from a previous system.
David
2010-02-21 23:17:25
@David: you'll note that I did recommend storing the prefix - albeit in a separate column. Nothing wrong with using a function-based index; alternatively, one could put an index on the virtual column.
Jeffrey Kemp
2010-02-22 02:54:33