Why am I getting an error when I try to add a Primary Key?-CodePudding

I am using SQL Server and SSMS.

I have a table called PersonsTable:

   PersonID  FirstName   Surname  Age
    1         Hansen      Ola      30
    2         Svendson    Tove     23
    3         Pettersen   Kari     20

I am trying to set a primary key like so:

ALTER TABLE PersonsTable
ADD PRIMARY KEY (PersonID);

But it gives me the following error:

Msg 8111, Level 16, State 1, Line 1
Cannot define PRIMARY KEY constraint on nullable column in table 'PersonsTable'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint or index. See previous errors.

CodePudding user response：

You can't create a primary key because that column is set to allow null values. Modify the column to not allow nulls.

Via query, it should look something like:

alter table PersonsTable alter column PersonID INT not null

CodePudding user response：

This is a case where reading the error message carefully helps you. In your PersonsTable definition put NOT NULL on the PersonID column.

And, you don't need to create a primary key to build a relationship between tables. If you want to enforce the relationship, use foreign keys. It's hard to be more specific because you didn't show your table definitions.