CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlssms
Jonathan Porter
Jonathan Porter

Reputation: 1556

How to create a surrogate key column in existing table?

I would like to create a new column called PurchaseOrderID in an existing table using SSMS. It combines LineNumber and PONUMBER to create a surrogate key and then I would enter into table design mode and assign it a PK there.

Creating new column:

ALTER TABLE FactPurchaseOrders
ADD PurchaseOrderID VARCHAR(64);

Populating with values:

UPDATE FactPurchaseOrders
SET PurchaseOrderID = (CONVERT(VARCHAR(64), LineNumber) + CONVERT(VARCHAR(64), PONUMBER))
WHERE 1=1;

Currently with this I am unable to assign this column a PK and I believe because it is nullable. I have also tried creating it in design mode first and the same problem occurs.

Error when trying to save PK

Upvotes: 3

Views: 7257

Answers (1)

Matt
Matt

Reputation: 1431

That is most certainly the case. After you have run your update, simply alter the columns you will use in your PK to NOT NULL. Since the columns will have values now it can be set as NOT NULL and then it will allow the assignment of PK. Also make sure there isn't already another PK on the table already. There can be only 1!

  1. Update Records
  2. Alter columns to not null
  3. Create PK on fields

Upvotes: 2

Related Questions