Merry Christmas and Happy new year to all my visitors. Thank you all for the support and continue the good work of liking the blog on facebook and referring others to visit here for support with some interesting topics. This is my last post of 2013 and l hope to continue contributing interesting topics to the blog come 2014.
The topic l’m discussing today sounds easy and could be a business scenario you can find yourself so to save someone from pulling of their hair, l like to give one approach how you can add a new column to a table that already has data and make the new column the primary key of the table. The trick here is a primary key column is non null column but you can not add a non null column to a table with existing data for obvious reason off course. So what approach will you use to accomplish this task.
Assuming I have a table student with the definition below.
CREATE TABLE [dbo].[Student](
[FirstName] [varchar](50) NULL,
[LastName] [varchar](50) NULL,
[Grade] [int] NULL
) ON [PRIMARY]
Then I insert some students using the query below.
INSERT INTO [2smart4school].[dbo].[Student]
([FirstName]
,[LastName]
,[Grade])
VALUES (‘Vincent’,’Amedekah’,1),
(‘Nelson’,’Mandela’,2),
(‘Bob’,’Marley’,3)
GO
Assuming after sometime, the school decided to create a unique key for all students which can be used to quickly retrieve information about them instead of searching based on names. Also take into consideration the school has already generated the keys for the students so all that is needed is to make sure the key gets added into the Student table, made a primary key to avoid key reuse and avoid adding of students without keys into the student table. This can be a real pain if you have lot of data, students with same first, students with same last name, students with same first and last name because then you have to manually update each record in the database.
So lets get started by adding the new column. Lets call it studentid and assume this key is generated as combination of letters and numbers so it has to be either varchar or nvarchar.
Alter table [2smart4school].[dbo].[Student]
Add StudentID varchar(50) not null
will give you the error below
” Msg 4901, Level 16, State 1, Line 1
ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column ‘StudentID’ cannot be added to non-empty table ‘Student’ because it does not satisfy these conditions.”
So we have to add the column as null first, then update records with their keys and then alter the column as not null and finally add a primary key constraint to the table using the column. That’s the amount of effort and the most tedious will be updating the records one at a time.
So add the column like below
Alter table [2smart4school].[dbo].[Student]
Add StudentID varchar(50) null
Next step is updating the records with their keys. Assume Vincent key is VIN1234, Nelson’s key is NEL2345 and Bob’s key is Bob2356.
Update [2smart4school].[dbo].[Student]
SET StudentID = ‘VIN1234′
WHERE FirstName =’Vincent’
AND LastName = ‘Amedekah’
Update [2smart4school].[dbo].[Student]
SET StudentID = ‘NEL2345′
WHERE FirstName =’Nelson’
AND LastName = ‘Mandela’
Update [2smart4school].[dbo].[Student]
SET StudentID = ‘BOB2356′
WHERE FirstName =’Bob’
AND LastName = ‘Marley’
Now this is how our table looks like
SELECT * FROM [2smart4school].[dbo].[Student]
FirstName LastName Grade StudentID
Vincent Amedekah 1 VIN1234
Nelson Mandela 2 NEL2345
Bob Marley 3 BOB2356
Now that we have all the columns updated, we have to change the Coloumn StudentID as not null because you can not use a null able column as a primary key.
Here is the query to achieve that.
Alter table [2smart4school].[dbo].[Student]
Alter column StudentID varchar(50) not null
Finally we add a primary key constraint to the table using the StudentID column. Here is the query for that.
Alter table [2smart4school].[dbo].[Student]
Add Constraint PK_StudentID Primary Key (StudentID)
That is it, mission accomplished. You can check in SSMS to see the column and its properties or use the query below to get the student table information.
SELECT
c.name ‘Column Name’,
t.Name ‘Data type’,
c.max_length ‘Max Length’,
c.is_nullable,
ISNULL(i.is_primary_key, 0) ‘Primary Key’
FROM
sys.columns c
INNER JOIN
sys.types t ON c.system_type_id = t.system_type_id
LEFT OUTER JOIN
sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN
sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE
c.object_id = OBJECT_ID(‘student’)
Output will show as below.
Column Name Data type Max Length is_nullable Primary Key
Grade int 4 1 0
FirstName varchar 50 1 0
LastName varchar 50 1 0
StudentID varchar 50 0 1
One advice from this discussion, never ever create a table without a primary key, even if there is no defined primary key for the data, use an auto generated identity column as the primary key so you can uniquely identify records in the table using it. In the future, like the scenario above, you can add the new column and then insert values into the new column just specifying the identity column in the where clause of the update statement to easily add the keys. Take for example, if you have records in the original table that has the same values in every field of the table, how would you be able to update only one of the records at a time? In this scenario, if you have an identity column, it will save you and after getting the new column to be used as primary column and get it populated, you can drop the identity column from the table and then set the new column as the primary key for the table.
Once again, merry Christmas and happy new year!!!!
