July 18, 2012 at 11:33 am
Hi,
I have a table where I am trying to add a column create_dt (datetime) and the defalut constraint that I want to specify is getdate().
But I don't want the already existing rows to take the getdate() for create_dt as they were created long time back.
If I specify the column as Nullable, I am not able to define a default constraint.
If I specify the column as Not null, I will have to insert some sort of date into existing rows which I dont want to do.
Is there a way I can define the constraint to act based on the ids. Say ids > 200 should have default constraint getdate() for create_dt and ids < 200 should have null values for create_dt.
July 18, 2012 at 12:04 pm
Can you use a dummy date such as 1900-1-1 for the entries which don't have a date?
Why does your column need to be NOT NULLable?
just a thought but you could use a trigger to ensure that all new rows have a date. (The default constraint will only work when the column is not supplied in the insert statement)
July 18, 2012 at 12:08 pm
@david-2 Betteridge
I dont want to insert any dummy values for the create_dt column.
The reason I want the column not null is for all the new rows I want the column to have value and I need to specify default constraint.
Trigger would work but a bunch of my stored procs that does inserts into the table without specifying the column list would break if there is no default value specified for the column create_dt.
July 18, 2012 at 12:14 pm
thought you might say that!
you could have a default constraint to cover your existing stored procedures which don't supply the column.
and then a trigger to prevent someone doing either insert statement where they supply NULL for the date or an update statement where they set the date to null.
something like..
drop table test
go
create table test
(
idint,
create_dtdatetime null default getdate()
)
go
CREATE TRIGGER nullDate_Trigger ON dbo.Test AFTER INSERT, UPDATE AS
BEGIN
IF EXISTS (SELECT 'X' FROM inserted WHERE create_dt IS NULL)
BEGIN
RAISERROR('The create_dt cannot be null',16,1)
ROLLBACK
END
END
GO
insert into test (id) select 1
go
insert into test (id, create_dt) select 2, null
go
select * from test
go
update test set create_dt = null
go
select * from test
go
July 18, 2012 at 12:17 pm
or better still
create table test
(
idint,
create_dtdatetime null default getdate(),
constraint ch check (id < 200 or create_dt is not null)
)
go
July 18, 2012 at 12:22 pm
nvm misread the original question.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 18, 2012 at 12:26 pm
yes forget the trigger idea that was daft. I think the check constraint meets the requirement however.
Rows with an ID < 200 can have a null created date
Rows with an ID >200 can;t have a null created date.
The default constraint stops the existing stored procs from breaking.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply