I can't save Database Diagrams

I'm trying to save a database diagram but I'm getting an error. How can I fix this?


Cannot insert the value NULL into column 'diagram_id', table

'SOFT.dbo.sysdiagrams'; column does not allow nulls. INSERT fails.

The statement has been terminated.

The 'sp_creatediagram' procedure attempted to return a status of NULL, which is not allowed.

A status of 0 will be returned instead. (.Net SqlClient Data Provider)

Hard to suggest a more specific fix or suggest a cause given that we have no background on how your system got into this state, what you did prior to trying to create the first diagram, if you have successfully created diagrams in the past, or even what version of SQL Server you're using. It sounds like your sysdiagrams table somehow lost the IDENTITY property on the diagram_id column. Do this:

DROP TABLE dbo.sysdiagrams;
GO
CREATE TABLE [dbo].[sysdiagrams]
(
    [name] [sysname] NOT NULL,
    [principal_id] [int] NOT NULL,
    [diagram_id] [int] IDENTITY(1,1) PRIMARY KEY,
    [version] [int] NULL,
    [definition] [varbinary](max) NULL,
    CONSTRAINT [UK_principal_name] UNIQUE ([principal_id],[name])
);

GO
EXEC sys.sp_addextendedproperty 
  @name=N'microsoft_database_tools_support', 
  @value=1 , 
  @level0type=N'SCHEMA',
  @level0name=N'dbo', 
  @level1type=N'TABLE',
  @level1name=N'sysdiagrams';
GO

This will of course lose any diagrams you were previously able to save. If you really need to keep your existing diagrams, you can add the identity property back using the table designer (yuck), just make sure you set the initial seed higher than any existing value in the table and that you have enabled the setting "Prevent saving changes that require table re-creation." Note that you can't do this with ALTER TABLE, since it doesn't support turning on/off the IDENTITY property - feel free to script out the change and see all the gymnastics that happen behind the scenes.

(After performing a search it saddened me to see that many people suggest as a fix to make the default for this column 0. This is great after you create one diagram but falls apart for multiple reasons once you try to create a second one.)

StackExchange Database Administrators Q#128769, answer score: 16