Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Default Constraint Not Reflected on Table Expand / Collapse
Author
Message
Posted Monday, October 1, 2012 1:18 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 3:47 AM
Points: 199, Visits: 512
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
GETDATE()

. But while checking the query this Constraint not getting reflected on my table.
Every time it shows NULL.

Can any one help on this?
Post #1366338
Posted Monday, October 1, 2012 2:54 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 27, 2014 8:03 AM
Points: 317, Visits: 1,079
Can 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.
Post #1366402
Posted Monday, October 1, 2012 3:32 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 3:47 AM
Points: 199, Visits: 512
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
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]

Post #1366422
Posted Monday, October 1, 2012 3:44 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 27, 2014 8:03 AM
Points: 317, Visits: 1,079
I can't see ImportDate there?
Post #1366439
Posted Monday, October 1, 2012 3:49 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 3:47 AM
Points: 199, Visits: 512
laurie-789651 (10/1/2012)
I can't see ImportDate there?


You could see Gen_DATE has the import date. Please let me know if any.

Thanks.
Post #1366444
Posted Monday, October 1, 2012 3:55 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 27, 2014 8:03 AM
Points: 317, Visits: 1,079
It looks OK to me:

You couldn't get NULL in Gen_DATE when it's defined as NOT NULL.


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF 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)
*/

Post #1366451
Posted Monday, October 1, 2012 3:58 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 11:32 PM
Points: 3,420, Visits: 5,351
sqlusers (10/1/2012)
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
GETDATE()

. But while checking the query this Constraint not getting reflected on my table.
Every time it shows NULL.

Can any one help on this?


Could it be that you are looking at records that were created before you added the DEFAULT constraint?



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1366452
Posted Monday, October 1, 2012 4:04 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 27, 2014 8:03 AM
Points: 317, Visits: 1,079
But you couldn't apply a NOT NULL constraint to a column containing nulls, could you?
Post #1366456
Posted Monday, October 1, 2012 4:30 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 27, 2014 8:03 AM
Points: 317, Visits: 1,079
dwain.c (10/1/2012)
sqlusers (10/1/2012)
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
GETDATE()

. But while checking the query this Constraint not getting reflected on my table.
Every time it shows NULL.

Can any one help on this?


Could it be that you are looking at records that were created before you added the DEFAULT constraint?


Looks like you may be right there...
Post #1366468
Posted Monday, October 1, 2012 4:33 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 11:32 PM
Points: 3,420, Visits: 5,351
dwain.c (10/1/2012)
sqlusers (10/1/2012)
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
GETDATE()

. But while checking the query this Constraint not getting reflected on my table.
Every time it shows NULL.

Can any one help on this?


Could it be that you are looking at records that were created before you added the DEFAULT constraint?


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.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1366470
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse