Unable to create Foreign Key references to Primary Key with Auto Increment and Prefix

Question

I'm quite new to SQL. I have a problem here, I cannot create a foreign key references to the primary key which is auto increment with prefix (S0001,S0002 and so on).

This is my SQL query

CREATE TABLE Staff
(
   No int NOT NULL IDENTITY (1,1),
   Staff_ID AS 'S' + RIGHT('000' + CAST(No as varchar(10)), 3) PERSISTED,

   CONSTRAINT PK_Staff PRIMARY KEY CLUSTERED (Staff_ID),

   Staff_Name varchar(30),
   Staff_Username varchar(30),
   Staff_Password varchar(30),
   Staff_Email varchar(30),
);

and

CREATE TABLE Manager
(
   No int NOT NULL IDENTITY (1,1),
   Staff_ID AS 'S' + RIGHT('000' + CAST(No as varchar(10)),3),

   CONSTRAINT FK_Staff_ID FOREIGN KEY (Staff_ID) REFERENCES Staff(Staff_ID) ON DELETE CASCADE,

   Manager_Name varchar (30),
   Manager_Email varchar (30),
);

As you can see, I have created 2 tables Staff and Manager, I wanted to create a foreign key in Manager referencing the Staff.Staff_ID. But end up it returns me with this error message:

Msg 1764, Level 16, State 1, Line 1
Computed Column 'Staff_ID' in table 'Manager' is invalid for use in 'FOREIGN KEY CONSTRAINT' because it is not persisted.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

I had already tried adding PERSISTED into the Manager query and it able to create a Manager table but when I manually key in the value, it returns me an error message.

CREATE TABLE Manager
(
    No int NOT NULL IDENTITY (1,1),
    Staff_ID AS 'S' + RIGHT('000' + CAST(No as varchar(10)),3) PERSISTED,

    CONSTRAINT FK_Staff_ID FOREIGN KEY (Staff_ID) REFERENCES Staff(Staff_ID) ON DELETE CASCADE,

    Manager_Name varchar (30),
    Manager_Email varchar (30),
);

Manually inserting value

INSERT INTO Manager (Staff_ID,Manager_Name,Manager_Email)
VALUES ('S002','Kelvin','[email protected]')

and error message came out.

Msg 271, Level 16, State 1, Line 1
The column "Staff_ID" cannot be modified because it is either a computed column or is the result of a UNION operator.

Any way to resolve this issue?

Answer 1

You can't insert a value for the calculated field. Insert without it to use the next identity:

INSERT INTO Manager (Manager_Name, Manager_Email)
VALUES ('Kelvin', '[email protected]')

If you want to specify the field, you would specify the value that it's based on, i.e. the identity field. You can use the identity_insert setting to override the identity:

set identity_insert Manager on

INSERT INTO Manager (No, Manager_Name, Manager_Email)
VALUES (2, 'Kelvin', '[email protected]')

set identity_insert Manager off