March 16, 2009 at 3:49 pm
Why is the following not parsing?
ALTER TABLE [dbo].[strategy_card_definition]
ALTER COLUMN [create_date] SET DEFAULT getdate()
March 16, 2009 at 3:56 pm
this should actually be an add column datetime with a defaulte setting of getdate().
Also, how do I specify no check constraint?
March 16, 2009 at 11:00 pm
You cannot use ALTER COLUMN to set the Defaults. The below query should work.
ALTER TABLE [dbo].[strategy_card_definition]
ADD CONSTRAINT DF_Date DEFAULT(GETDATE()) FOR [create_date]
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
March 16, 2009 at 11:31 pm
AshaRRichardson2nd (3/16/2009)
this should actually be an add column datetime with a defaulte setting of getdate().Also, how do I specify no check constraint?
If you want to add a new column with a default constraint (as I understood from your second message), you can use this code:
create table MyTable (MyInt int)
alter table MyTable add MyDate datetime
constraint DF_MyTable_MyDate default(getdate())
If you want to add a default constraint to a column that already exists, you can do it the way that Adiga wrote.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 17, 2009 at 11:31 am
The customer has changed his mind. He wants all existing rows in the table populated with the default value once the column gets added. So I was doing something like this. It seems to work, but how do I get the NOT NULL in there?
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'type_of_fraud' AND COLUMN_NAME = 'matchfile_autoload')
BEGIN
ALTER TABLE [dbo].[type_of_fraud]
ADD [matchfile_autoload] char(01) NULL
END
--Step #5
begin TRAN
update dbo.type_of_fraud
set matchfile_autoload = 'N'
Commit TRAN
--Step #6
ALTER TABLE [dbo].[type_of_fraud]
ADD CONSTRAINT DF_No_matchfile_autoload DEFAULT('N') FOR [matchfile_autoload]
GO
Thanks, everyone!
March 17, 2009 at 12:07 pm
As Adi said, you can add both column as well as constraint in a single ALTER statement:
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'type_of_fraud' AND COLUMN_NAME = 'matchfile_autoload')
BEGIN
ALTER TABLE [dbo].[type_of_fraud]
ADD [matchfile_autoload] char(01) NOT NULL
CONSTRAINT DF_No_matchfile_autoload DEFAULT('N')
END
--Ramesh
March 17, 2009 at 1:00 pm
Thank you! Worked perfectly.:-)
October 22, 2023 at 1:18 pm
SQL SERVER
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply