Trying to create a Composite Primary Key in MSSQL with only a part of 2 columns

Viewed 34

I'm new to SQL and am following a course, however it does not cover the "create table" part. It only covers statements etc. What I would like to have is, my primary key (cust_id) to be generated with the "first_name" and the first 3 letters of "last_name". I.E. I want "John Smith" to become "custid"; JOHNSMI.

I have below code which works (without composite).

CREATE TABLE NL_client (
    custid INT PRIMARY KEY IDENTITY (10000, 1),
    userid VARCHAR (50) NOT NULL,
    first_name VARCHAR (50) NOT NULL,
    last_name VARCHAR (50) NOT NULL,
    birthday DATE
);

And I found below code (last line added)

CREATE TABLE SAMPLE_TABLE (
    custid INT,
    userid VARCHAR (50) NOT NULL,
    first_name VARCHAR (50) NOT NULL,
    last_name VARCHAR (50) NOT NULL,
    PRIMARY KEY (first_name, last_name),
);

However, when trying to execte the second query as displayed above it does not create a Primary key. Or when I execure the below queries;

custid INT PRIMARY KEY (first_name, last_name),

Either above in the query or at the end, it does not make a primary key.

Furthermore, I have no idea, nor was I able to find (perhaps I searched wrongly, surely I'm not the first with this "problem") how to select only the first 3 letters of "last_name" to be used as a part of the "custid". Perhaps this is not possible and I should use "custerid" as an INT Primary key and use "userid" as a composite. But it would surely help me in the future to be able to use the Primary Key as a reference in Python.

Many thanks in advance for your help and let me learn to understand why it doesn't work!

0 Answers
Related