0

I'm hoping that my question doesn't get flagged as a duplicate of this one, because albeit essentially the same question I'm looking to accomplish this using SQLite3.

[SCENARIO]

I have 3 tables of a database created as follows:

CREATE TABLE INVENTORY (
    Item_SKU INTEGER PRIMARY KEY AUTOINCREMENT,
    Item_Title TEXT,
    Item_Price REAL);
CREATE TABLE CUSTOMERS (
    Customer_ID INTEGER PRIMARY KEY AUTOINCREMENT,
    C_First_Name TEXT,
    C_Last_Name TEXT);
CREATE TABLE ORDERS (
    Invoice_ID INTEGER PRIMARY KEY AUTOINCREMENT,
    Customer_ID INTEGER,
    Item_SKU INTEGER,
    FOREIGN KEY(Customer_ID) REFERENCES CUSTOMERS(Customer_ID),
    FOREIGN KEY(Item_SKU) REFERENCES INVENTORY(Item_SKU));

What I'd like to accomplish is to use a custom prefix for all of my primary keys such that Item_SKU will automatically populate something like INVEN001 for the first row, and autoincrement to INVEN002 then INVEN003 etc.

As well I'd like to have my Customer_ID autoincrement from CUST001, CUST002, etc.

Then finally within the ORDERS table, I'd like to concatenate the foreign keys and use the outcome as the Invoice_ID primary key value, so that the result would be something like CUST001INVEN001, CUST002INVEN002, etc.

Is this feasible? Or am I just so far outside the realm of SQL that this doesn't even make sense or is necessary at all? Any advice is much appreciated.

trjv
  • 21
  • 4
  • 1
    Only an `INTEGER PRIMARY KEY` column/`rowid` is autogenerated when a row is inserted without an explicit value (you don't need and often [don't want to use](https://www.sqlite.org/autoinc.html) the badly named `AUTOINCREMENT` keyword). Writing something in your user interface code that translates between numbers and something more human friendly is always an option, of course. – Shawn Dec 10 '18 at 16:45

1 Answers1

0

Why don't you do this programatically? Create a new column and put a value in it that concatenates the prefix with the value of the PRIMARY KEY? So when the entry in each table is made, you add that value at the same time?

Woody
  • 41
  • 7