InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 19:13:32 GMT20http://www.sqlservercentral.com/Forums/Topic1366338-338-1.aspx[quote][b]dwain.c (10/1/2012)[/b][hr][quote][b]dwain.c (10/1/2012)[/b][hr][quote][b]sqlusers (10/1/2012)[/b][hr]Hi Guys,I have created a Default Constraint for my table. But this is not reflected on my table.EX: Column ImportDate has a Datetime Datatype with NOT NULL Constraint.I have created a Default Constranit for this as [code="sql"]GETDATE()[/code]. But while checking the query this Constraint not getting reflected on my table.Every time it shows NULL.Can any one help on this?[/quote]Could it be that you are looking at records that were created before you added the DEFAULT constraint?[/quote]That is true. I was suggesting that maybe the DEFAULT constraint was added after some INSERTs were made, and that wouldn't of course affect data already saved. I didn't notice that the column the OP was referring to was NOT NULL.[/quote]I still reckon you're right though.Note to OP: If you add a default constraint to an existing column [i]that allows nulls[/i], SQL Server does not backfill the values to existing rows - you have to do that yourself!:-)Mon, 01 Oct 2012 04:37:39 GMTlaurie-789651http://www.sqlservercentral.com/Forums/Topic1366338-338-1.aspx[quote][b]dwain.c (10/1/2012)[/b][hr][quote][b]sqlusers (10/1/2012)[/b][hr]Hi Guys,I have created a Default Constraint for my table. But this is not reflected on my table.EX: Column ImportDate has a Datetime Datatype with NOT NULL Constraint.I have created a Default Constranit for this as [code="sql"]GETDATE()[/code]. But while checking the query this Constraint not getting reflected on my table.Every time it shows NULL.Can any one help on this?[/quote]Could it be that you are looking at records that were created before you added the DEFAULT constraint?[/quote]That is true. I was suggesting that maybe the DEFAULT constraint was added after some INSERTs were made, and that wouldn't of course affect data already saved. I didn't notice that the column the OP was referring to was NOT NULL.Mon, 01 Oct 2012 04:33:34 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1366338-338-1.aspx[quote][b]dwain.c (10/1/2012)[/b][hr][quote][b]sqlusers (10/1/2012)[/b][hr]Hi Guys,I have created a Default Constraint for my table. But this is not reflected on my table.EX: Column ImportDate has a Datetime Datatype with NOT NULL Constraint.I have created a Default Constranit for this as [code="sql"]GETDATE()[/code]. But while checking the query this Constraint not getting reflected on my table.Every time it shows NULL.Can any one help on this?[/quote]Could it be that you are looking at records that were created before you added the DEFAULT constraint?[/quote]Looks like you may be right there...Mon, 01 Oct 2012 04:30:24 GMTlaurie-789651http://www.sqlservercentral.com/Forums/Topic1366338-338-1.aspxBut you couldn't apply a NOT NULL constraint to a column containing nulls, could you?Mon, 01 Oct 2012 04:04:34 GMTlaurie-789651http://www.sqlservercentral.com/Forums/Topic1366338-338-1.aspx[quote][b]sqlusers (10/1/2012)[/b][hr]Hi Guys,I have created a Default Constraint for my table. But this is not reflected on my table.EX: Column ImportDate has a Datetime Datatype with NOT NULL Constraint.I have created a Default Constranit for this as [code="sql"]GETDATE()[/code]. But while checking the query this Constraint not getting reflected on my table.Every time it shows NULL.Can any one help on this?[/quote]Could it be that you are looking at records that were created before you added the DEFAULT constraint?Mon, 01 Oct 2012 03:58:12 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1366338-338-1.aspxIt looks OK to me:You couldn't get NULL in Gen_DATE when it's defined as NOT NULL.[code="sql"]SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOIF OBJECT_ID('dbo.tblGen') IS NOT NULL DROP TABLE dbo.tblGen;CREATE TABLE [dbo].[tblGen]( [PROCESS_ID] [bigint] NOT NULL, [Gen_FLAG] [bit] NOT NULL CONSTRAINT [DF_FLAG] DEFAULT ((0)), [Gen_DATE] [datetime] NOT NULL CONSTRAINT [DF_DATE] DEFAULT (getdate())) ON [PRIMARY]insert into dbo.tblGen ( PROCESS_ID ) values ( 6703 ) ;select * from dbo.tblGen;/*PROCESS_ID Gen_FLAG Gen_DATE-------------------- -------- -----------------------6703 0 2012-10-01 10:52:51.937(1 row(s) affected)*/[/code]Mon, 01 Oct 2012 03:55:55 GMTlaurie-789651http://www.sqlservercentral.com/Forums/Topic1366338-338-1.aspx[quote][b]laurie-789651 (10/1/2012)[/b][hr]I can't see ImportDate there?[/quote]You could see Gen_DATE has the import date. Please let me know if any.Thanks.Mon, 01 Oct 2012 03:49:08 GMTsqlusershttp://www.sqlservercentral.com/Forums/Topic1366338-338-1.aspxI can't see ImportDate there?Mon, 01 Oct 2012 03:44:34 GMTlaurie-789651http://www.sqlservercentral.com/Forums/Topic1366338-338-1.aspx[code="sql"]SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[tblGen]( [PROCESS_ID] [bigint] NOT NULL, [Gen_FLAG] [bit] NOT NULL CONSTRAINT [DF_FLAG] DEFAULT ((0)), [Gen_DATE] [datetime] NOT NULL CONSTRAINT [DF_DATE] DEFAULT (getdate())) ON [PRIMARY][/code]Mon, 01 Oct 2012 03:32:11 GMTsqlusershttp://www.sqlservercentral.com/Forums/Topic1366338-338-1.aspxCan you post the definition of the table? This will make it clear.Select the table, 'Script table as' > 'Create to' > 'new query editor window' & post the SQL.Mon, 01 Oct 2012 02:54:47 GMTlaurie-789651http://www.sqlservercentral.com/Forums/Topic1366338-338-1.aspxHi Guys,I have created a Default Constraint for my table. But this is not reflected on my table.EX: Column ImportDate has a Datetime Datatype with NOT NULL Constraint.I have created a Default Constranit for this as [code="sql"]GETDATE()[/code]. But while checking the query this Constraint not getting reflected on my table.Every time it shows NULL.Can any one help on this?Mon, 01 Oct 2012 01:18:34 GMTsqlusers