-1

I have a table in database (Azure Data Studio)and while writing the create table script I forgot to add the primary key constraint. In order to add primary key constraint I have to alter the table and before that the default NULL has to be removed from that particular column tow which I am going to mark as primary key.

My create table scripts looks like this: Crete Table dbo.employee( employeeID varchar(120) NULL, employeeName varchar(50) NULL, employeePhone Integer NULL )

So I need to create a primary key constraint on employeeID table in Azure Data Studio so I was using the general script like:

ALTER TABLE [dbo].[employee] ADD PRIMARY KEY CLUSTERED ( [employeeID ] ASC )WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] GO

The above statement is giving me expected error because the **employeeID **is set to NULL as default.The error I am getting while alter the column is this:[enter image description here][enter image description here][1] Can anyone help me to resolve this issue and add the primary key on that column.

Nezko1
  • 15
  • 4
  • Does this answer your question? [Altering a column: null to not null](https://stackoverflow.com/questions/689746/altering-a-column-null-to-not-null) – Thom A Jun 07 '23 at 11:19
  • Is the table empty? Does `employeeID` contain alphabetic characters, or only digits? Consider `INTEGER` as the data type for `employeeID` if you only have digits. It is faster by orders of magnitude. – marcothesane Jun 07 '23 at 11:29
  • The table is not empty, it is n varchar datatype column – Nezko1 Jun 07 '23 at 11:35
  • ALTER TABLE dbo.employee ALTER COLUMN employeeID varchar(120) NOT NULL; ALTER TABLE dbo.employee ADD CONSTRAINT PK_employee PRIMARY KEY CLUSTERED (employeeID); – Aswin Jun 07 '23 at 11:36
  • This script is also not working, and giving me error as below: cannot define primary key constraint on nullable column in table 'dbo.employee' – Nezko1 Jun 07 '23 at 11:40
  • Share the error – Aswin Jun 07 '23 at 11:40
  • I added the screenshot in the question itself now, you can check once! – Nezko1 Jun 07 '23 at 11:44
  • [Please do not upload images of code/data/errors when asking a question.](//meta.stackoverflow.com/q/285551) – Thom A Jun 07 '23 at 11:51
  • Try to execute the query one by one. First execute `ALTER TABLE dbo.employee ALTER COLUMN employeeID varchar(120) NOT NULL; ` Then execute `ALTER TABLE dbo.employee ADD CONSTRAINT PK_employee PRIMARY KEY CLUSTERED (employeeID); ` – Aswin Jun 07 '23 at 11:59

1 Answers1

0

A primary key cannot have a null value. It is used to enforce data integrity and ensure that each row in the table is unique. In SQL Server, you can define a primary key column as NOT NULL to ensure that it does not allow null values. If you try to insert a row with a null value in the primary key column, you will get an error.

ALTER TABLE dbo.employee ALTER COLUMN employeeID varchar(120) NOT NULL;

Then try to add the primary key constraint statement employeeID column using the ALTER TABLE statement.

Reference: MS document on Primary Key constraint

Aswin
  • 4,090
  • 2
  • 4
  • 16