title: Unable to create Foreign Key references to Primary Key with Auto Increment and Prefix
body: <p>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 (<code>S0001,S0002</code> and so on).</p>
  
  <p>This is my SQL query</p>
  
  <pre><code>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),
  );
  </code></pre>
  
  <p>and</p>
  
  <pre><code>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),
  );
  </code></pre>
  
  <p>As you can see, I have created 2 tables <code>Staff</code> and <code>Manager</code>, I wanted to create a foreign key in <code>Manager</code> referencing the <code>Staff.Staff_ID</code>. But end up it returns me with this error message:</p>
  
  <blockquote>
    <p>Msg 1764, Level 16, State 1, Line 1<br>
    Computed Column 'Staff_ID' in table 'Manager' is invalid for use in 'FOREIGN KEY CONSTRAINT' because it is not persisted.<br>
    Msg 1750, Level 16, State 0, Line 1<br>
    Could not create constraint. See previous errors.</p>
  </blockquote>
  
  <p>I had already tried adding <code>PERSISTED</code> into the <code>Manager</code> query and it able to create a <code>Manager</code> table but when I manually key in the value, it returns me an error message.</p>
  
  <pre><code>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),
  );
  </code></pre>
  
  <p>Manually inserting value</p>
  
  <pre><code>INSERT INTO Manager (Staff_ID,Manager_Name,Manager_Email)
  VALUES ('S002','Kelvin','scammerforever@gmail.com')
  </code></pre>
  
  <p>and error message came out.</p>
  
  <blockquote>
    <p>Msg 271, Level 16, State 1, Line 1<br>
    The column "Staff_ID" cannot be modified because it is either a computed column or is the result of a UNION operator.</p>
  </blockquote>
  
  <p>Any way to resolve this issue?</p>