syntax for adding named default to existing column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

syntax for adding named default to existing column

Author

Topic

Exgliderpilot
Starting Member

14 Posts

Posted - 2011-06-17 : 04:32:26

Hi, applogies - I posted in 2005 - meant to post in 2008

I'm trying to add a named default sequential guid to an exisitng column in an existing table. I've looked up the MS help, pasted the tempate for this and removed the bits i dont need and i've got the following:

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[SetupPage]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE TABLE [dbo].[SetupPage](
[PageName] [varchar](100) NULL,
[PageDescription] [varchar](512) NULL,
[SetupPageGUID] [uniqueidentifier] NOT NULL,
[ApplicationId] [uniqueidentifier] NULL,
[row] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_SetupPage] PRIMARY KEY NONCLUSTERED
(
[SetupPageGUID] ASC
)WITH FILLFACTOR = 98 ON [PRIMARY]
) ON [PRIMARY]

GO

If NOT EXISTS(select 1 from sys.check_constraints where name='DF_SetupPage_SetupPageGUID')
ALTER TABLE [dbo].[SetupPage] ALTER COLUMN SetupPageGUID ADD CONSTRAINT DF_SetupPage_SetupPageGUID DEFAULT (newsequentialid()) FOR [SetupPageGUID]

I get the error: Incorrect syntax near the keyword 'CONSTRAINT'.

the correct synatax would very much be appreciated thanks

Staff bank ageny software http:\\www.ava.co.uk
Blog
http://creamteadiet.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-17 : 07:07:17

If NOT EXISTS(select 1 from sys.check_constraints where name='DF_SetupPage_SetupPageGUID')
ALTER TABLE [dbo].[SetupPage] ~~ALTER COLUMN SetupPageGUID~~ ADD CONSTRAINT DF_SetupPage_SetupPageGUID DEFAULT (newsequentialid()) FOR [SetupPageGUID]

Exgliderpilot
Starting Member

14 Posts

Posted - 2011-06-17 : 10:50:55

OK I finally got there so hopefully this will help a search on this phrase: thanks sunitabeck - cant find the place to mark as answer...

also I was checking the wrong sys.*
If NOT EXISTS(SELECT * from sys.default_constraints where name='DF_SetupPage_SetupPageGUID')
ALTER TABLE [dbo].[SetupPage] WITH NOCHECK ADD CONSTRAINT DF_SetupPage_SetupPageGUID DEFAULT (newsequentialid()) FOR [SetupPageGUID]

Staff bank agency scheduling software http:\\www.ava.co.uk
Blog
http://creamteadiet.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources