CODEWITHSUNDEEP
sqlsql-serverdatabase
Stephen Davlantes
Stephen Davlantes

Reputation: 163

Cannot define PRIMARY KEY Constraint on nullable column when column is already NOT NULL

I have a table called Cases that has information about interviews such as IV_Date, IV_Length, Interviewer, etc. I also have a field within Cases called Case_Code (a varchar) which is a six-character (three letters + three numbers) identifier; e.g. "ABC123" or "ZZZ999." There is a foreign key on ContactID (an int), which points to a Contact table. I have created a computed column which is the PRIMARY KEY of this Cases table, called CaseID. CaseID is simply a concatenation of Case_Code and ContactID. So, ContactID "25" working on case "ZZZ999" is given a CaseID of "ZZZ99925". Neither Case_Code nor ContactID accepts nulls, so CaseID obviously does not. When setting up CaseID I created it as NOT NULL, PRIMARY KEY, and used the formula "[Case_Code] + CONVERT([varchar], [contactID], 0)". I thought everything was working fine but when I try to rearrange any fields in the SQL Studio table design view, I get thrown this error:

'Cases (dbo)' table
- Unable to create index 'PK_Cases_1'.  
Cannot define PRIMARY KEY constraint on nullable column in table 'Cases'.
Could not create constraint. See previous errors.'

I do not understand why I'm getting this error, since all NOT NULL columns in my table contain data. Any help or ideas would be greatly appreciated. Thanks!

Edit with code:

CREATE TABLE [dbo].[Cases](
    [ContactID] [int] NOT NULL,
    [Case_Code] [varchar](16) NOT NULL,
    [Assigned_To] [varchar](100) NULL,
    [LEK_Interviewer] [varchar](255) NULL,
    [Case_Notes] [varchar](max) NULL,
    [IV_Status] [varchar](100) NULL,
    [IV_Quality] [numeric](18, 0) NULL,
    [IV_Date] [date] NULL,
    [IV_Length] [varchar](50) NULL,
    [Address] [varchar](100) NULL,
    [City] [varchar](100) NULL,
    [State] [varchar](50) NULL,
    [Zip] [varchar](25) NULL,
    [Country] [varchar](50) NULL,
    [Total_Honorarium] [money] NULL,
    [Currency] [varchar](20) NULL,
    [Last_Update] [varchar](50) NULL,
    [CaseID]  AS ([Case_Code]+CONVERT([varchar],[contactID],0)) PERSISTED NOT NULL,
    CONSTRAINT [PK_Cases_1] PRIMARY KEY CLUSTERED 
    (
        [CaseID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,        ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]

Upvotes: 14

Views: 78120

Answers (5)

modelDBA
modelDBA

Reputation: 176

This appears to be a bug in the SQL Server Management Studio. The easiest approach is to make your changes using the designer. Then generate the change script using:

Right-click > Generate Change Script...

Copy the generated SQL or save it to a file. Then, add "NOT NULL" to the computed column in the CREATE TABLE statement and run the SQL file

Upvotes: -1

amitkumar
amitkumar

Reputation: 176

Use this query 

Alter table tablename
add column datatype identity(1,1)

Upvotes: 0

user565869
user565869

Reputation:

I bumped into this, too. You can create a PK on a calculated field, as long as it's marked NOT NULL, but the SQL Server table design interface does not handle this situation; it seems to mark all calculated fields as NULLable when modifying a table.

First, I strongly recommend that you always create and modify your tables with scripts, not the GUI. Doing so gives you a record of what you have and what changes you've made; you can run them to recreate your database as of any point in time. As HLGEM mentioned, you can also keep these scripts in source control. Having these scripts makes it easier to do things which are difficult to do with the GUI, and as you've discovered here, they make it possible to do things which are impossible in the GUI.

Within the table designer, delete your PK. You should then be able to save the table, effecting the reordering of fields you wanted to achieve. Next, you need to drop and recreate the calculated field, and finally you can recreate your PK. E.g.:

ALTER TABLE Cases DROP COLUMN CaseID
ALTER TABLE Cases ADD CaseID AS (Case_Code + CAST(ContactID AS CHAR(6))) PERSISTED NOT NULL
ALTER TABLE Cases ADD CONSTRAINT CPK_Cases PRIMARY KEY CLUSTERED (CaseID)

If you have any indices which reference the calculated field (other than the PK, which we've already dropped), you'll need to delete them before you can drop the calculated field. Be sure to re-create them after, of course.

Finally, while Dean did not support his assertion that calculated fields are bad for PKs, he is correct that it would be simpler to put your PK on { Case_Code, ContactID }. You could then stop persisting CaseID (if you even need it all) and save yourself a little disk space.

Upvotes: 3

Omkar Hendre
Omkar Hendre

Reputation: 435

A primary key cannot be applied on a column with a null constraint (nullable). When a table is created, by default every column is nullable.

So first off all we have to remove the null constraint and apply a NOT NULL constraint on the column. To do this execute the following query

ALTER TABLE tbl_name alter column column_name int NOT NULL

After that you can apply a primary key on that same column. To do this execute the following query

ALTER TABLE tbl_name ADD PRIMARY KEY (column_name)

I hope this will help you

Upvotes: 31

dean
dean

Reputation: 10098

Don't use computed column as a primary key, very bad idea. In your case, just define the PK on both CaseCode and ContactID columns. In the designer, select both columns, right-click and choose "set prinary key" from the menu.

Upvotes: -1

Related Questions