Default Constraint

Question

Default Constraint

Arno Kwetters

Hall of Fame

Points: 3726
More actions
May 21, 2015 at 6:57 am

#398488

I make a table with Default values and that works fine :
CREATE TABLE [TestDefault](
[ID] [int] NOT NULL,
[aInt] [int] NOT NULL DEFAULT ((0)),
[aTime] [datetime] NOT NULL DEFAULT (getdate()),
[aBit] [bit] NULL DEFAULT ((0)),
CONSTRAINT [PK_Logging.NotificationLog] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
But when I use "Script Table as" / Create To / New query Window then I get this :
USE [DB]
GO
/****** Object: Table [dbo].[TestDefault] Script Date: 21-5-2015 14:54:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TestDefault](
[ID] [int] NOT NULL,
[aInt] [int] NOT NULL,
[aTime] [datetime] NOT NULL,
[aBit] [bit] NULL,
CONSTRAINT [PK_Logging.NotificationLog] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TestDefault] ADD DEFAULT ((0)) FOR [aInt]
GO
ALTER TABLE [dbo].[TestDefault] ADD DEFAULT (getdate()) FOR [aTime]
GO
ALTER TABLE [dbo].[TestDefault] ADD DEFAULT ((0)) FOR [aBit]
GO
How do I get my original Defaults on the line of the ColumnNames ?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

You can edit the script if you want them that way. SSMS scripts constraints separately to the table. Doesn't make much of a difference.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Arno Kwetters Hall of Fame Points: 3726 More actions · Answer 2

Thanks for your fast answer.

But I want to have the Default on the same line as the column because I have very much tables and columns.

It is easy to see them in one View.

The strange thing is, I have (by coincidence) a table which generates the script I want.

But I can not see any difference in properties or so in that table and column definition.

Arno Kwetters Hall of Fame Points: 3726 More actions · Answer 3

I am testing and looking around.

It is very strange I have very much tables and almost all tables generates the 'wrong'script.

But I have a few tables which are doing what I want !

And I found a difference :

SET ANSI_PADDING ON

GO

Does that make sense ?

Arno Kwetters Hall of Fame Points: 3726 More actions · Answer 4

Eureka !!

I found it myself.

If there is data in the table then the 'right' script will appear.

If there is no data in the table then the 'wrong' script be generated.

Thanks for looking with me.