4

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)

2
  • Guessing you don't have the proper permissions.
    – Pete Alvin
    Sep 12, 2018 at 14:14
  • 1
    Diagrams are a deprecated feature in SSMS in version 18 (still in preview) so will eventually be done away with - FYI to anyone from the future. Dec 14, 2018 at 20:25

3 Answers 3

16

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.)

1
  • No need to recreate the table and lose all diagrams. Simply alter that column to restore the identity specification. Dump the table first to see your highest identity number (n) in that column and set the identity specification to (n+1,1) Dec 22, 2022 at 16:10
2

I was in a similar situation, unable to save a new diagram even though I had several others. In SSMS I manually added a new row to the sysdiagram table with the name of my new yet unsaved table. Then saved my new 'Diagram_0' with the name I'd put in the table. SSMS responded with the "table of this name already exists, do you want to save" question, answer yes to this, job done or rather problem avoided reasonably simply. Not problem solved I might add, so I'm sure there's a better answer out there, just didn't have time to find it.

1
  • Worked perfectly!
    – dstelow
    Jun 30, 2023 at 3:27
1

I encountered the same issue and fixed it simply by opening dbo.sysdiagrams table in design mode and change the column diagram_id to "Is Identity", following the tip by Aaron.

1
  • Yes! Thank you. I was careful to set the identity spec to 7,1 because a select of the table showed diagrams with identities 2 - 6. Dec 22, 2022 at 16:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.