CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2008-r2sql-server-2012
Dunxton
Dunxton

Reputation: 408

SQL Server not null column update with unique constraint

I have an existing table with 2 columns say [Employee_Id , Salary] with a few records in it.

I want to add a new column say Employee_Code which is

1 Not Null

2 Unique

Since the table already has some rows in it and the column is not nullable, I have to add a default value to it.

Since I am adding a unique constraint, adding a default value will not work.

How do I add a not null column to an existing table, having unique constraint?

Upvotes: 1

Views: 3293

Answers (8)

Akash Veshala
Akash Veshala

Reputation: 1

You cannot add both constraints NOT NULL , UNIQUE at the same time i guess...

So to overcome this we need to initialise the column with NULL and UNIQUE Constraint.

Then 'Populate' it with random 'UNIQUE' values and after that you can use the constarint NOT NULL

ALTER TABLE <table_name> ADD <column_name> <datatype>;

By default NULL values are added to the column

Add UNIQUE constraint

ALTER TABLE <table_name> ADD CONSTRAINT name UNIQUE(<column_name>);

Populate with random unique values and after that

Add NOT NULL constraint

ALTER TABLE <table_name> MODIFY <column_name> <datatype> NOT NULL;

Upvotes: 0

Gaurav Joshi
Gaurav Joshi

Reputation: 1001

You can add IDENTITY column to a table if identity is not an issue for you.

ALTER TABLE tablename ADD columnname INT IDENTITY(1, 1)

Example :

ALTER TABLE Employee ADD empCode INT NOT NULL IDENTITY(1, 1)

Upvotes: 0

Ramesh
Ramesh

Reputation: 43

Kindly Try the below Query to add the new Column

Alter table Employee add  Employee_Code int

Upvotes: 0

Ramesh
Ramesh

Reputation: 43

To Make the Primary Key Column:-

Alter table Employee  add primary key(Employee_Code)

Upvotes: 0

Spikeh
Spikeh

Reputation: 3695

As you rightly pointed out, you can't do this easily. If you want a unique constraint, you will need to make each value unique before you apply the constraint.

Also, it's worth mentioning that you can't use WITH NOCHECK when creating the new constraint, as a unique constraint (by its very nature) requires a unique index. And you can't have a unique index without unique key values!

So, you need to do the following:

  • Create a new table
  • Populate it
  • Add a new NOT NULL column with a default constraint (that can be removed later if necessary)
  • Modify all the new column values to be unique (though this code does not guarantee that the generated identifier will be unique, and it entirely depends on what your data type and requirements are)
  • Apply the unique constraint

And here's some code as an example:

IF OBJECT_ID('MyTable') IS NOT NULL
    DROP TABLE MyTable
GO

CREATE TABLE MyTable (
    Id INT IDENTITY PRIMARY KEY,
    Employee_Id INT NOT NULL
)

INSERT INTO MyTable(Employee_Id)
VALUES(1)
INSERT INTO MyTable(Employee_Id)
VALUES(15)
INSERT INTO MyTable(Employee_Id)
VALUES(156)
INSERT INTO MyTable(Employee_Id)
VALUES(3)
INSERT INTO MyTable(Employee_Id)
VALUES(4)
INSERT INTO MyTable(Employee_Id)
VALUES(13)
INSERT INTO MyTable(Employee_Id)
VALUES(16)
INSERT INTO MyTable(Employee_Id)
VALUES(21)

ALTER TABLE MyTable
    ADD Employee_Code VARCHAR(10)
GO

-- TODO: Update your new columns to be unique 
-- Manually or programmatically. Note that I would not recommend 
-- using the below code - it's just for example purposes
UPDATE MyTable
    SET Employee_Code = LEFT(CONVERT(VARCHAR(36), NEWID()), 10)
GO

-- Create the null constraint
ALTER TABLE MyTable
    ALTER COLUMN Employee_Code VARCHAR(10) NOT NULL
GO

-- Create the Unique constraint / index
ALTER TABLE MyTable
    ADD CONSTRAINT MyTable_Employee_Code_Unique UNIQUE(Employee_Code)
GO

UPDATE

Actually, I worked out a better way to do this without even using DEFAULT values. Updated code above.

Upvotes: 1

AdamL
AdamL

Reputation: 13141

  1. Add this column as not-nullable and with default value, let's say 0.
  2. Create unique filtered index on the column, with filterd on 0

This way 0 value in the column will not break unique constraint. If you don't want any new rows to have 0 value, drop default constraint, and add CHECK constraint <> 0 (with option NOCHECK, so it won't validate old values).

Upvotes: 0

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28403

You just create the column simply and Populate Values then add constraint like this

   ALTER TABLE [Table] ALTER COLUMN [Column] INTEGER NOT NULL

Upvotes: 0

Craig Moore
Craig Moore

Reputation: 1089

The only way to do this is the create the new column as a null column with a unique constraint, then populate the column for all of the rows that have data in them, and then alter it to a not null column.

Upvotes: 3

Related Questions