SQL Server not null column update with unique constraint

Question

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?

http://stackoverflow.com/questions/6305225/how-to-add-not-null-constraint-to-existing-column-in-mysql5-1 — Nagaraj S, Dec 30 '13 at 10:06
Employee_Code can be any arbitrary value. It is either taken as a user input from the UI or is pushed from another system. — Dunxton, Dec 30 '13 at 10:34

score 3 · Answer 1 · answered Dec 30 '13 at 09:58

3

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.

answered Dec 30 '13 at 09:58

Craig Moore

1,093
1
6
15

Spikeh · Answer 2 · 2013-12-30T10:49:09.907

1

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.

edited Dec 30 '13 at 10:49

answered Dec 30 '13 at 10:14

Spikeh

3,540
4
24
49

Why two steps? You can just do `ALTER TABLE MyTable ADD Employee_Code varchar(10) NOT NULL DEFAULT LEFT(CONVERT(VARCHAR(36), NEWID()), 10)` – Martin Smith Dec 30 '13 at 10:33
Because separating concerns still applies to SQL. If someone forgets to remove the DEFAULT constraint after the fact, it's quite possible that a record could be inserted without specifying the Employee_Code column (as your default will return a unique result most of the time). Ideally, you should create the DEFAULT constraint separately too, give it a name, and drop it afterwards. – Spikeh Dec 30 '13 at 10:39
Bunkum. Just drop the constraint when done if no longer required. It is ridiculous to talk about "separating concerns" for a couple of lines in an adhoc SQL admin task. – Martin Smith Dec 30 '13 at 10:41
Actually TBH doesn't really matter. I was originally thinking not doing it in one operation means all rows get updated twice. But this is probably not the case for a default of an empty string on a new column. Suspect that will be metadata only. – Martin Smith Dec 30 '13 at 10:45
I think "ridiculous" is a bit of a ridiculous thing to say about being vigilant. I have updated my example to remove the unnecessary reliance on a DEFAULT constraint. – Spikeh Dec 30 '13 at 10:50
That still scans the table twice. Once to update the values then again to validate the `NOT NULL` constraint. – Martin Smith Dec 30 '13 at 10:55
The OP did not ask about efficiency. Of course it scans the table twice, you need to add two pretty important constraints to it. The only other way to feasibly do this is to create a secondary table with all new columns and constraints applied, copy the original data to it (and generate the new data), delete the original table, then rename the secondary table. This method could potentially involve a lot more effort, though (and possibly downtime), as the original table may have additional constraints that need to be verified on other columns. – Spikeh Dec 30 '13 at 11:13

score 0 · Answer 3 · answered Dec 30 '13 at 10:02

0

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

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

answered Dec 30 '13 at 10:02

Vignesh Kumar A

27,863
13
63
115

score 0 · Answer 4 · answered Dec 30 '13 at 10:03

Add this column as not-nullable and with default value, let's say 0.
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).

score 0 · Answer 5 · edited Jan 17 '19 at 23:58

0

Kindly Try the below Query to add the new Column

Alter table Employee add  Employee_Code int

edited Jan 17 '19 at 23:58

Nerdroid

13,398
5
58
69

answered Dec 30 '13 at 10:11

Ramesh

33
1
8

score 0 · Answer 6 · edited Jan 17 '19 at 23:58

0

To Make the Primary Key Column:-

Alter table Employee  add primary key(Employee_Code)

edited Jan 17 '19 at 23:58

Nerdroid

13,398
5
58
69

answered Dec 30 '13 at 10:15

Ramesh

33
1
8

score 0 · Answer 7 · answered Feb 18 '20 at 20:36

0

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)

answered Feb 18 '20 at 20:36

Gaurav Joshi

861
1
11
17

Akash Veshala · Answer 8 · 2022-01-19T04:27:50.670

0

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;

edited Jan 19 '22 at 04:27

answered Jan 18 '22 at 19:29

Akash Veshala

1
1

Please use code blocks to format your answer :) – pierreavn Jan 18 '22 at 20:11

SQL Server not null column update with unique constraint

8 Answers8