CONCAT_WS Three Fields to Create PRIMARY KEY in CREATE TABLE Statement

Question

I'm writing a CREATE TABLE statement for a fairly large table which will be loaded via a .csv file.

I need to create a PRIMARY KEY by concatenating three of the fields together:

• STORE_NBR
• CONTROL_NBR
• LINE_NBR

and append that newly created/named field to the beginning of each record.

Is this possible within the CREATE TABLE statement? Or do I have to use APPENDafter the fact?

And how would I do that?

EDIT ----- 3/21/2016

---

I'm still being told I should be able to concatenate the three fields when creating the table itself, i.e. -

CREATE TABLE Mistints(
        CONCAT(storeNbr,controlNbr,lineNbr) AS pKey,
        storeNbr INT,
        controlNbr INT,
        lineNbr INT,
        salesNbr VARCHAR(15) etc etc

But I'm getting an error when attempting to do that. My supervisor wants that concatenated field appended to the beginning of each record and wants it as the Primary Key. Is there a way to do so within the Create Table statement? Or do I have to do an Update after the fact? Or do so as I load the table (via .csv file)?

Answer 1

It is possible through CREATE TABLE by adding following lines to the end of your table definition:

CREATE TABLE TAB_NAME(
  STORE_NBR [data type]([size]) [column constraint],
  CONTROL_NBR [data type]([size]) [column constraint],
  LINE_NBR [data type]([size]) [column constraint],
  .
  .
  .
  PRIMARY KEY (STORE_NBR, CONTROL_NBR, LINE_NBR)
  );