Saturday, April 3, 2010

Observation : Definining a Column Default

If a DATETIME column allows NULLS and subsequently has a  Default Value (e.g. GETUTCDATE() assigned to it, the default constraint does not function.when new rows enter the table.

The solution is to populate the column with DATETIME values (incrementally if necessary) and then ALTER the table to disallow NULL values on the column.

The default constraint will then function.

Default Columns :http://www.blackwasp.co.uk/SQLDefaultColumns.aspx


Much easier of course, is to define the column and default at the same time e.g.
(If you are the developer/architect of your application that is...)
ALTER TABLE Common.ipAddresses ADD ipEntryDate DATETIME2(0) NOT NULL
CONSTRAINT DateTimeRightNow DEFAULT GETDATE()

No comments: