ADD Constraint - set default 0

Question

Post reply

ADD Constraint - set default 0

Viewing 12 posts - 16 through 26 (of 26 total)

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

Paul White SSC Guru Points: 150467 More actions · Answer 1

Ian Scarlett (9/9/2009)
Right, I am now confused:unsure:

Sorry. Let me illustrate my thinking:

CREATE TABLE dbo.Test

(

col1 INTEGER NOT NULL CONSTRAINT [PK_Column_Constraint!] PRIMARY KEY CLUSTERED,

col2 INTEGER NOT NULL DEFAULT 0,

col3 INTEGER NOT NULL CHECK (col3 > 0),

CONSTRAINT [Table_Constraint 1!] CHECK (col2 > col3),

CONSTRAINT [Table_Constraint 2!] FOREIGN KEY (col2) REFERENCES dbo.ParentTable (PK_fish)

);

So the constraints on col1, col2, and col3 (including the NOT NULLs) are column constraints.

The table constraints are at the bottom, after the final column's comma...

I accept that this may be just me, but in the code example I reversed, the constraint that came first looks like a column constraint. The second one comes after a comma so could be a table constraint. The error message calls it a table constraint too.

Is that better? I did try!

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 2

Paul White (9/9/2009)Is that better? I did try!

Yup. I think!

The comma denotes the end of the column definition, and the beginning of any table constraints, so

alter table dbo.myTable ADD fred int null CONSTRAINT DFx DEFAULT 0 with values, constraint TBx check(fred < id)Is a column with constraint, followed by a table level constraint, but

alter table dbo.myTable ADD fred int null constraint TBx check(fred < id), CONSTRAINT DFx DEFAULT 0 with valuesIs trying to put a table constraint into the column definition, and put the column constraint after the comma, where the table constraint goes.

Adding the column constraint outside the column defintion has to use the slightly different syntax

alter table #temp ADD fred int null, CONSTRAINT DFx DEFAULT (0) for fred with values

Or is there something far more deep and meaningful in the question, that has soehow eluded me?

Santhoshkumar KB SSCertifiable Points: 5066 More actions · Answer 3

KB (9/9/2009)
ALTER TABLE dbo.myTable ADD CONSTRAINT DF_myTable_Q DEFAULT(0) FOR Quota

currently the Quota column is "int NULL"

how to change it to "int NOT NULL" ?

-

KB

Thanks,
Santhosh

Human Knowledge Belongs To The World !!

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 4

KB (9/9/2009)
KB (9/9/2009)
ALTER TABLE dbo.myTable ADD CONSTRAINT DF_myTable_Q DEFAULT(0) FOR Quota
currently the Quota column is "int NULL"
how to change it to "int NOT NULL" ?
-
KB

alter table dbo.myTable alter column Quota int not null

But this only works after you have removed existing null values

Paul White SSC Guru Points: 150467 More actions · Answer 5

KB (9/9/2009)
currently the Quota column is "int NULL"
how to change it to "int NOT NULL" ?

ALTER TABLE dbo.TableName ALTER COLUMN Quota INTEGER NOT NULL;

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 6

Ian Scarlett (9/9/2009)
Adding the column constraint outside the column defintion has to use the slightly different syntax alter table #temp ADD fred int null, CONSTRAINT DFx DEFAULT (0) for fred with values

As soon as you put that comma in (after the INT NULL) the DFx constraint is no longer a column constraint - it's a table constraint.

That's why you need the FOR FRED part - a table default constraint must reference the column.

If you omit the FOR FRED bit (like a column constraint can) you get the "Incorrect syntax for definition of the 'TABLE' constraint." error.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Santhoshkumar KB SSCertifiable Points: 5066 More actions · Answer 7

alter table dbo.myTable alter column Quota int not null
But this only works after you have removed existing null values

Thanks,

it worked.

-

KB

Thanks,
Santhosh

Human Knowledge Belongs To The World !!

Paul White SSC Guru Points: 150467 More actions · Answer 8

KB (9/9/2009)
Thanks,it worked.

Awesome. Sorry about the interruptions - but Ian and I are having a fascinating discussion, hope you didn't mind too much :blush:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 9

Paul White (9/9/2009)
As soon as you put that comma in (after the INT NULL) the DFx constraint is no longer a column constraint - it's a table constraint.

Ahhh... which is where your original question stems from. Although it is technically a table constraint because of where in the statement it is added, the metadata shows it as a "DEFAULT on column fred".

I think I finally see the point you were making.:-P

I too have learned something new today.

Paul White SSC Guru Points: 150467 More actions · Answer 10

Ian,

Cool. Thanks for the exchange - great stuff. And it's 3am. Time for me to hand over to the next time-zone... 🙂

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Santhoshkumar KB SSCertifiable Points: 5066 More actions · Answer 11

Awesome. Sorry about the interruptions - but Ian and I are having a fascinating discussion, hope you didn't mind too much :blush:

Thats ok...

I am also learning from your discussions 🙂

-

KB

Thanks,
Santhosh

Human Knowledge Belongs To The World !!

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 12

Paul White (9/9/2009)
Ian,
Cool. Thanks for the exchange - great stuff. And it's 3am. Time for me to hand over to the next time-zone... 🙂

Holy S**t, you're in NZ. I wish I was that alert at 3am.

Enjoyed it too.