DEFAULT Constraint Definition

Question

Post reply

DEFAULT Constraint Definition

Lowell

SSC Guru

Points: 323518
More actions
July 15, 2009 at 9:05 am

#134265

OK i'm feeling extra dumb today... I want to to run a CREATE TABLE statement which features a named default constraint. an inline definition, which generates a name automatically is no problem.
I can't seem to do it in a single statement/operation...only with a seperate ALTER TABLE ADD CONSTRAINT.
what am i doing wrong?
here's the working example:
CREATE TABLE [dbo].[MYEXAMPLE] (
[EXAMPLEID] INT IDENTITY(1,1) NOT NULL,
[EXAMPLEFLAG] CHAR(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
--[EXAMPLEFLAG] CHAR(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL DEFAULT 'N', --works if included, no problem..but it's not named!
CONSTRAINT [PK__MYEXAMPLE__EXAMPLEID] PRIMARY KEY CLUSTERED (EXAMPLEID),
CONSTRAINT [CK__MYEXAMPLE__EXAMPLEFLAG] CHECK ([EXAMPLEFLAG]='N' OR [EXAMPLEFLAG]='Y') )
ALTER TABLE [MYEXAMPLE] ADD CONSTRAINT [DF__MYEXAMPLE__EXAMPLEFLAG] DEFAULT ('N') FOR EXAMPLEFLAG WITH VALUES
and here's what I'm trying to do, but it fails:
DROP TABLE MYEXAMPLE
CREATE TABLE [dbo].[MYEXAMPLE] (
[EXAMPLEID] INT IDENTITY(1,1) NOT NULL,
[EXAMPLEFLAG] CHAR(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK__MYEXAMPLE__EXAMPLEID] PRIMARY KEY CLUSTERED (EXAMPLEID),
CONSTRAINT [CK__MYEXAMPLE__EXAMPLEFLAG] CHECK ([EXAMPLEFLAG]='N' OR [EXAMPLEFLAG]='Y'),
CONSTRAINT [DF__MYEXAMPLE__EXAMPLEFLAG] DEFAULT ('N') FOR (EXAMPLEFLAG) )
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

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

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

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 1

DROP TABLE MYEXAMPLE

go

CREATE TABLE [dbo].[MYEXAMPLE] (

[EXAMPLEID] INT IDENTITY(1,1) NOT NULL,

[EXAMPLEFLAG] CHAR(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

CONSTRAINT [DF__MYEXAMPLE__EXAMPLEFLAG] DEFAULT ('N'),

CONSTRAINT [PK__MYEXAMPLE__EXAMPLEID] PRIMARY KEY CLUSTERED (EXAMPLEID),

CONSTRAINT [CK__MYEXAMPLE__EXAMPLEFLAG] CHECK ([EXAMPLEFLAG]='N' OR [EXAMPLEFLAG]='Y')

)

Why not make the EXAMPLEFLAG column NOT NULL if you are going to have a default on it?

Lowell SSC Guru Points: 323518 More actions · Answer 2

it was just an example so i could get the named default constraint in there somewhere ...i didn't really think it through;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Robert Klimes SSCoach Points: 17865 More actions · Answer 3

This should work for you.

DROP TABLE MYEXAMPLE

CREATE TABLE [dbo].[MYEXAMPLE] (

[EXAMPLEID] INT IDENTITY(1,1) NOT NULL,

[EXAMPLEFLAG] CHAR(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF__MYEXAMPLE__EXAMPLEFLAG] DEFAULT ('N'),

CONSTRAINT [PK__MYEXAMPLE__EXAMPLEID] PRIMARY KEY CLUSTERED (EXAMPLEID),

CONSTRAINT [CK__MYEXAMPLE__EXAMPLEFLAG] CHECK ([EXAMPLEFLAG]='N' OR [EXAMPLEFLAG]='Y'))

Bob
-----------------------------------------------------------------------------
How to post to get the best help[/url]

Lowell SSC Guru Points: 323518 More actions · Answer 4

Thanks Michael and Robert;

I was stuck thinking I could append the default at the end, like i've done with PK/UQ/FK and check constraints.... instead of still part of the column definition.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!