-
Notifications
You must be signed in to change notification settings - Fork 3.2k
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
EF Core default value for not nullable column not set #7679
Comments
Could you show the table definition (CREATE TABLE) ? |
/****** Object: Table [dbo].[relaties] Script Date: 22/02/2017 14:45:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[relaties](
[code] [varchar](10) NOT NULL,
[naam] [varchar](50) NOT NULL,
[naam2] [varchar](50) NULL,
[adres1] [varchar](67) NULL,
[adres2] [varchar](67) NULL,
[landen_code] [varchar](4) NULL CONSTRAINT [DF_relaties_landen_code] DEFAULT ('BE'),
[gemeente_id] [int] NULL,
[telefoon] [varchar](25) NULL,
[telefax] [varchar](25) NULL,
[gsm] [varchar](25) NULL,
[email] [varchar](60) NULL,
[url] [varchar](80) NULL,
[valuta_code] [varchar](8) NOT NULL CONSTRAINT [DF_relaties_valuta_code] DEFAULT ('EUR'),
[moddate] [datetime] NOT NULL CONSTRAINT [DF_relaties_moddate] DEFAULT (getdate()),
[createdate] [datetime] NOT NULL CONSTRAINT [DF_relaties_createdate] DEFAULT (getdate()),
[taalcodes_taalcode] [varchar](4) NOT NULL CONSTRAINT [DF_relaties_taalcodes_taalcode] DEFAULT ('N'),
[klapro_code] [varchar](2) NULL,
[vertegenw_code] [varchar](10) NULL,
[relatiegrp1_code] [varchar](10) NULL,
[betvoorw_code] [varchar](12) NOT NULL CONSTRAINT [DF_relaties_betvoorw_code] DEFAULT ('CO'),
[btwregimes_btwregime] [varchar](2) NOT NULL CONSTRAINT [DF_relaties_btwregimes_btwregime] DEFAULT ('H'),
[btwnr] [varchar](30) NULL,
[banknr] [varchar](30) NULL,
[landen_code_0] [varchar](4) NULL CONSTRAINT [DF_relaties_landen_code_0] DEFAULT ('BE'),
[landen_code_1] [varchar](4) NULL CONSTRAINT [DF_relaties_landen_code_1] DEFAULT ('BE'),
[createuser] [varchar](8) NOT NULL CONSTRAINT [DF_relaties_createuser] DEFAULT ('%user%'),
[moduser] [varchar](8) NOT NULL CONSTRAINT [DF_relaties_moduser] DEFAULT ('%user%'),
[aansprek_code] [varchar](8) NULL,
[prijscat_code] [varchar](10) NOT NULL CONSTRAINT [DF_relaties_prijscat_code] DEFAULT ('A'),
[relkortgrp_code] [varchar](10) NULL,
[bic] [varchar](11) NULL,
[relaties_code] [varchar](10) NULL,
[intmemo] [varchar](max) NULL,
[verzamelfct] [bit] NOT NULL CONSTRAINT [DF_relaties_verzamelfct] DEFAULT ((1)),
[kredlimiet] [decimal](12, 2) NULL,
[melding] [varchar](max) NULL,
[relatiebron_code] [varchar](10) NULL,
[ctbtwverlaagd_code] [varchar](10) NOT NULL CONSTRAINT [DF_relaties_ctbtwverlaagd_code] DEFAULT ('NVT'),
[vlgdcontact] [datetime] NULL,
[nacecode_nacecode] [varchar](10) NULL,
[relatiebron2_id] [int] NULL,
[relatiegrp2_id] [int] NULL,
[relaties_code_0] [varchar](10) NULL,
[redenopzeg_code] [varchar](10) NULL,
[ctafhandeling_code] [varchar](4) NOT NULL CONSTRAINT [DF_relaties_ctafhandeling_code] DEFAULT ('L'),
[transp_code] [varchar](10) NULL,
[aanmaning] [bit] NOT NULL CONSTRAINT [DF_relaties_aanmaning] DEFAULT ((1)),
[attestbtwverlaagd] [bit] NOT NULL CONSTRAINT [DF_relaties_attestbtwverlaagd] DEFAULT ((0)),
[klakaart] [bit] NOT NULL CONSTRAINT [DF_relaties_klakaart] DEFAULT ((1)),
[oudrelnr] [varchar](40) NULL,
[verzend_id] [int] NULL,
[geblokkeerd] [bit] NOT NULL CONSTRAINT [DF_relaties_geblokkeerd] DEFAULT ((0)),
[ctidentificatie_code] [varchar](1) NOT NULL CONSTRAINT [DF_relaties_ctidentificatie_code] DEFAULT ('B'),
[cttypeafdruk_code] [varchar](2) NOT NULL CONSTRAINT [DF_relaties_cttypeafdruk_code] DEFAULT ('P'),
[datkredcontr] [datetime] NULL,
[rpr] [varchar](15) NULL,
[isoverheid] [bit] NOT NULL CONSTRAINT [DF_relaties_isoverheid] DEFAULT ((0)),
CONSTRAINT [PK_relaties] PRIMARY KEY CLUSTERED
(
[code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[relaties] WITH CHECK ADD CONSTRAINT [FK_relaties_aansprek] FOREIGN KEY([aansprek_code])
REFERENCES [dbo].[aansprek] ([code])
GO
ALTER TABLE [dbo].[relaties] CHECK CONSTRAINT [FK_relaties_aansprek]
GO
ALTER TABLE [dbo].[relaties] WITH CHECK ADD CONSTRAINT [FK_relaties_betvoorw] FOREIGN KEY([betvoorw_code])
REFERENCES [dbo].[betvoorw] ([code])
GO
ALTER TABLE [dbo].[relaties] CHECK CONSTRAINT [FK_relaties_betvoorw]
GO
ALTER TABLE [dbo].[relaties] WITH CHECK ADD CONSTRAINT [FK_relaties_btwregimes] FOREIGN KEY([btwregimes_btwregime])
REFERENCES [dbo].[btwregimes] ([btwregime])
GO
ALTER TABLE [dbo].[relaties] CHECK CONSTRAINT [FK_relaties_btwregimes]
GO
ALTER TABLE [dbo].[relaties] WITH CHECK ADD CONSTRAINT [FK_relaties_ctafhandeling] FOREIGN KEY([ctafhandeling_code])
REFERENCES [dbo].[ctafhandeling] ([code])
GO
ALTER TABLE [dbo].[relaties] CHECK CONSTRAINT [FK_relaties_ctafhandeling]
GO
ALTER TABLE [dbo].[relaties] WITH CHECK ADD CONSTRAINT [FK_relaties_ctbtwverlaagd] FOREIGN KEY([ctbtwverlaagd_code])
REFERENCES [dbo].[ctbtwverlaagd] ([code])
GO
ALTER TABLE [dbo].[relaties] CHECK CONSTRAINT [FK_relaties_ctbtwverlaagd]
GO
ALTER TABLE [dbo].[relaties] WITH CHECK ADD CONSTRAINT [FK_relaties_ctidentificatie] FOREIGN KEY([ctidentificatie_code])
REFERENCES [dbo].[ctidentificatie] ([code])
GO
ALTER TABLE [dbo].[relaties] CHECK CONSTRAINT [FK_relaties_ctidentificatie]
GO
ALTER TABLE [dbo].[relaties] WITH CHECK ADD CONSTRAINT [FK_relaties_cttypeafdruk] FOREIGN KEY([cttypeafdruk_code])
REFERENCES [dbo].[cttypeafdruk] ([code])
GO
ALTER TABLE [dbo].[relaties] CHECK CONSTRAINT [FK_relaties_cttypeafdruk]
GO
ALTER TABLE [dbo].[relaties] WITH CHECK ADD CONSTRAINT [FK_relaties_gemeente] FOREIGN KEY([gemeente_id])
REFERENCES [dbo].[gemeente] ([id])
GO
ALTER TABLE [dbo].[relaties] CHECK CONSTRAINT [FK_relaties_gemeente]
GO
ALTER TABLE [dbo].[relaties] WITH NOCHECK ADD CONSTRAINT [FK_relaties_klapro] FOREIGN KEY([klapro_code])
REFERENCES [dbo].[klapro] ([code])
GO
ALTER TABLE [dbo].[relaties] CHECK CONSTRAINT [FK_relaties_klapro]
GO
ALTER TABLE [dbo].[relaties] WITH CHECK ADD CONSTRAINT [FK_relaties_landen] FOREIGN KEY([landen_code])
REFERENCES [dbo].[landen] ([code])
GO
ALTER TABLE [dbo].[relaties] CHECK CONSTRAINT [FK_relaties_landen]
GO
ALTER TABLE [dbo].[relaties] WITH CHECK ADD CONSTRAINT [FK_relaties_landen_0] FOREIGN KEY([landen_code_0])
REFERENCES [dbo].[landen] ([code])
GO
ALTER TABLE [dbo].[relaties] CHECK CONSTRAINT [FK_relaties_landen_0]
GO
ALTER TABLE [dbo].[relaties] WITH CHECK ADD CONSTRAINT [FK_relaties_landen_1] FOREIGN KEY([landen_code_1])
REFERENCES [dbo].[landen] ([code])
GO
ALTER TABLE [dbo].[relaties] CHECK CONSTRAINT [FK_relaties_landen_1]
GO
ALTER TABLE [dbo].[relaties] WITH CHECK ADD CONSTRAINT [FK_relaties_nacecode] FOREIGN KEY([nacecode_nacecode])
REFERENCES [dbo].[nacecode] ([nacecode])
GO
ALTER TABLE [dbo].[relaties] CHECK CONSTRAINT [FK_relaties_nacecode]
GO
ALTER TABLE [dbo].[relaties] WITH CHECK ADD CONSTRAINT [FK_relaties_prijscat] FOREIGN KEY([prijscat_code])
REFERENCES [dbo].[prijscat] ([code])
GO
ALTER TABLE [dbo].[relaties] CHECK CONSTRAINT [FK_relaties_prijscat]
GO
ALTER TABLE [dbo].[relaties] WITH CHECK ADD CONSTRAINT [FK_relaties_redenopzeg] FOREIGN KEY([redenopzeg_code])
REFERENCES [dbo].[redenopzeg] ([code])
GO
ALTER TABLE [dbo].[relaties] CHECK CONSTRAINT [FK_relaties_redenopzeg]
GO
ALTER TABLE [dbo].[relaties] WITH CHECK ADD CONSTRAINT [FK_relaties_relatiebron] FOREIGN KEY([relatiebron_code])
REFERENCES [dbo].[relatiebron] ([code])
GO
ALTER TABLE [dbo].[relaties] CHECK CONSTRAINT [FK_relaties_relatiebron]
GO
ALTER TABLE [dbo].[relaties] WITH CHECK ADD CONSTRAINT [FK_relaties_relatiebron2] FOREIGN KEY([relatiebron2_id])
REFERENCES [dbo].[relatiebron2] ([id])
GO
ALTER TABLE [dbo].[relaties] CHECK CONSTRAINT [FK_relaties_relatiebron2]
GO
ALTER TABLE [dbo].[relaties] WITH CHECK ADD CONSTRAINT [FK_relaties_relatiegrp1] FOREIGN KEY([relatiegrp1_code])
REFERENCES [dbo].[relatiegrp1] ([code])
GO
ALTER TABLE [dbo].[relaties] CHECK CONSTRAINT [FK_relaties_relatiegrp1]
GO
ALTER TABLE [dbo].[relaties] WITH CHECK ADD CONSTRAINT [FK_relaties_relatiegrp2] FOREIGN KEY([relatiegrp2_id])
REFERENCES [dbo].[relatiegrp2] ([id])
GO
ALTER TABLE [dbo].[relaties] CHECK CONSTRAINT [FK_relaties_relatiegrp2]
GO
ALTER TABLE [dbo].[relaties] WITH CHECK ADD CONSTRAINT [FK_relaties_relaties] FOREIGN KEY([relaties_code])
REFERENCES [dbo].[relaties] ([code])
GO
ALTER TABLE [dbo].[relaties] CHECK CONSTRAINT [FK_relaties_relaties]
GO
ALTER TABLE [dbo].[relaties] WITH CHECK ADD CONSTRAINT [FK_relaties_relaties_0] FOREIGN KEY([relaties_code_0])
REFERENCES [dbo].[relaties] ([code])
GO
ALTER TABLE [dbo].[relaties] CHECK CONSTRAINT [FK_relaties_relaties_0]
GO
ALTER TABLE [dbo].[relaties] WITH CHECK ADD CONSTRAINT [FK_relaties_relkortgrp] FOREIGN KEY([relkortgrp_code])
REFERENCES [dbo].[relkortgrp] ([code])
GO
ALTER TABLE [dbo].[relaties] CHECK CONSTRAINT [FK_relaties_relkortgrp]
GO
ALTER TABLE [dbo].[relaties] WITH CHECK ADD CONSTRAINT [FK_relaties_taalcodes] FOREIGN KEY([taalcodes_taalcode])
REFERENCES [dbo].[taalcodes] ([taalcode])
GO
ALTER TABLE [dbo].[relaties] CHECK CONSTRAINT [FK_relaties_taalcodes]
GO
ALTER TABLE [dbo].[relaties] WITH CHECK ADD CONSTRAINT [FK_relaties_transp] FOREIGN KEY([transp_code])
REFERENCES [dbo].[transp] ([code])
GO
ALTER TABLE [dbo].[relaties] CHECK CONSTRAINT [FK_relaties_transp]
GO
ALTER TABLE [dbo].[relaties] WITH CHECK ADD CONSTRAINT [FK_relaties_valuta] FOREIGN KEY([valuta_code])
REFERENCES [dbo].[valuta] ([code])
GO
ALTER TABLE [dbo].[relaties] CHECK CONSTRAINT [FK_relaties_valuta]
GO
ALTER TABLE [dbo].[relaties] WITH CHECK ADD CONSTRAINT [FK_relaties_vertegenw] FOREIGN KEY([vertegenw_code])
REFERENCES [dbo].[vertegenw] ([code])
GO
ALTER TABLE [dbo].[relaties] CHECK CONSTRAINT [FK_relaties_vertegenw]
GO
ALTER TABLE [dbo].[relaties] WITH CHECK ADD CONSTRAINT [FK_relaties_verzend] FOREIGN KEY([verzend_id])
REFERENCES [dbo].[verzend] ([id])
GO
ALTER TABLE [dbo].[relaties] CHECK CONSTRAINT [FK_relaties_verzend]
GO |
In the create table statement you can see that the column
The INSERT statement won't include the column unless a non-default value is set on the corresponding property. This should allow the row to be created with the default value specified on the table. My only guess is that at the time you see the failure you may be executing the application against a database that is out of date and does not include the default constraint on the column. |
Can you share the full INSERT statement that fails? |
The table definition is the one I'm using now, so it can't be being out of date. SET NOCOUNT ON;
INSERT INTO [relaties] ([code], [aanmaning], [aansprek_code], [adres1], [adres2], [banknr], [bic], [btwnr], [btwregimes_btwregime], [createdate], [createuser], [ctbtwverlaagd_code], [ctidentificatie_code], [datkredcontr], [email], [gemeente_id], [gsm], [intmemo], [klapro_code], [kredlimiet], [landen_code], [landen_code_0], [landen_code_1], [melding], [moddate], [moduser], [naam], [naam2], [nacecode_nacecode], [oudrelnr], [redenopzeg_code], [relatiebron2_id], [relatiebron_code], [relatiegrp1_code], [relatiegrp2_id], [relaties_code], [relaties_code_0], [relkortgrp_code], [rpr], [taalcodes_taalcode], [telefax], [telefoon], [transp_code], [url], [valuta_code], [vertegenw_code], [verzend_id], [vlgdcontact])
VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15, @p16, @p17, @p18, @p19, @p20, @p21, @p22, @p23, @p24, @p25, @p26, @p27, @p28, @p29, @p30, @p31, @p32, @p33, @p34, @p35, @p36, @p37, @p38, @p39, @p40, @p41, @p42, @p43, @p44, @p45, @p46, @p47);
SELECT [attestbtwverlaagd], [betvoorw_code], [ctafhandeling_code], [cttypeafdruk_code], [geblokkeerd], [isoverheid], [klakaart], [prijscat_code], [verzamelfct]
FROM [relaties]
WHERE @@ROWCOUNT = 1 AND [code] = @p0; |
The column betvoorw_code has more or less the same properties the column valuta_code has, but for this column the default value is being set |
I see @AndriySvyryd does this ring any bells? |
Can you share the application code that we can use to reproduce the issue. |
I attached some sample code that shows where the problem is located. If I create a DbContext only for the 'relaties' table (MyDbContext2), the default value for the valuta_code field is inserted. |
Note for triage: the property that is not getting the value generated is an FK property. I don't know if we ever thought about this scenario, but I can't immediately think of any reason why we shouldn't enable it. |
@AndriySvyryd We could consider this for a patch if it is bad regression without a workaround, but as of right now we think it probably doesn't need to be in a patch. |
At the moment we're setting the default value in code, so we have a workaround. |
My models and DbContext are generated with the scaffolding tool.
When I want to save a new object, an exception occurs telling me that the column valuta_code does not allow null values. But a default value for that column has been set in SQL Server: ('EUR').
My model also looks ok:
entity.Property(e => e.ValutaCode)
.IsRequired()
.HasColumnName("valuta_code")
.HasColumnType("varchar(8)")
.HasDefaultValueSql("'EUR'");
The strange thing is, for other columns with the same properties, there is no exception and the default values are getting set.
The exception thrown:
{System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'valuta_code', table 'fakt.dbo.relaties'; column does not allow nulls. INSERT fails.
The statement has been terminated.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action
1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, String executeMethod, IReadOnlyDictionary
2 parameterValues, Boolean closeConnection)at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection)
ClientConnectionId:6e70a009-beda-43e2-8caa-d645e765caa5
Error Number:515,State:2,Class:16}
When I log the query that EF executes, I see that some columns do get a default value, but the column valuta_code does not:
INSERT INTO [relaties]
VALUES (...)
SELECT ... <valuta_code is not included in this list>
FROM [relaties]
WHERE @@rowcount = 1 ...
I'm using EF Core version 1.1.0.
In EF Core version 1.0.1 I did not had this problem.
Anyone else having the same issues?
Further technical details
EF Core version: 1.1.0
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
IDE: (e.g. Visual Studio 2015)
The text was updated successfully, but these errors were encountered: