Good morning,
My name is Jason Robards and I'm currently working on a Database front end application using Java. The application is an inventory application and I would like to create the Primary Keys as the Inventory ID number so that every time a new item is inserted an ID number is auto assigned. There will be four tables for the database; Computers, Monitors, Printers, and Servers. I currently have the specs for the tables and will list them below. My question is how would I add an alpha character to the beginning of my sequence. An example would be: C00000001-C99999999 for the computer table. Each table will have its own Primary Key but all will have the same sequence. The distinguish-er will be the letter that proceeds the sequence. So an example of all the sequence numbers will be C00000001-C99999999 for the computers table, M00000001-M99999999 for the monitor table, P00000001-P99999999 for the printer table, and S00000001-S99999999 for the server. Is there a way to write this sequence? Thank you. The code is below. Any help with this is much appreciated.
CREATE TABLE computers
(cominvid NUMBER(8),
commake VARCHAR2(15),
commodel VARCHAR2(15),
comserial VARCHAR2(30) NOT NULL,
comprocessor VARCHAR2(30),
comprocspeed VARCHAR2(10),
comram VARCHAR2(10),
comoperatingsystem VARCHAR2(35) NOT NULL,
comosbit NUMBER(4) NOT NULL,
comhdd VARCHAR2(10),
comlocation VARCHAR2(25),
CONSTRAINT computers_cominvid_pk PRIMARY KEY(cominvid));
CREATE SEQUENCE computers_cominvid_seq
INCREMENT BY 1
START WITH 0000001
MAXVALUE 9999999
NO CACHE
NO CYCLE;
CREATE TABLE monitors
(moninvid NUMBER(8),
monmake VARCHAR2(15),
monmodel VARCHAR2(15),
monserial VARCHAR2(30) NOT NULL,
monlocation VARCHAR2(25),
monsize VARCHAR2(5),
montype VARCHAR2(15),
CONSTRAINT monitors_moninvid_pk PRIMARY KEY(moninvid));
CREATE SEQUENCE monitors_moninvid_seq
INCREMENT BY 1
START WITH 0000001
MAXVALUE 9999999
NO CACHE
NO CYCLE;
CREATE TABLE printers
(printinvid NUMBER(8),
printmake VARCHAR2(15),
printmodel VARCHAR2(15),
printserial VARCHAR2(30) NOT NULL,
printtype VARCHAR2(15),
printcartridge# VARCHAR2(15),
printlocation VARCHAR2(25),
CONSTRAINT printers_printinvid_pk PRIMARY KEY(printinvid));
CREATE SEQUENCE printers_printinvid_seq
INCREMENT BY 1
START WITH 0000001
MAXVALUE 9999999
NO CACHE
NO CYCLE;
CREATE TABLE servers
(servinvid NUMBER(8),
servmake VARCHAR2(15),
servmodel VARCHAR2(15),
servserial VARCHAR2(30) NOT NULL,
servprocessor VARCHAR2(30),
servprocspeed VARCHAR2(10),
servram VARCHAR2(10),
servoperatingsystem VARCHAR2(35) NOT NULL,
servosbit NUMBER(4) NOT NULL,
servhdd VARCHAR2(10),
servhddraidtype VARCHAR2(25),
servhddcapacity VARCHAR2(10),
servlocation VARCHAR2(25),
CONSTRAINT servers_servinvid_pk PRIMARY KEY(servinvid));
CREATE SEQUENCE servers_servinvid_seq
INCREMENT BY 1
START WITH 0000001
MAXVALUE 9999999
NO CACHE
NO CYCLE;