add a new column to a table with conditions

Question

add a new column to a table with conditions

pooya1072

SSC Veteran

Points: 256
More actions
May 28, 2013 at 9:15 am

#293757

hi friends
i want to add a column to a table with conditions below :
1- it has a default value=0
2- this column can get null value
3- no constraint should be created
is it possible ?
thanks

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

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

pooya1072 (5/28/2013)
hi friends
i want to add a column to a table with conditions below :
1- it has a default value=0
2- this column can get null value
3- no constraint should be created
is it possible ?
thanks

This looks a lot like homework to me. What have you tried?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lowell SSC Guru Points: 323518 More actions · Answer 2

pooya1072 (5/28/2013)
hi friends
i want to add a column to a table with conditions below :
1- it has a default value=0
2- this column can get null value
3- no constraint should be created
is it possible ?
thanks

EDIT: code example snipped to avoid doing homework.

technically, a default value is a default constraint.; even the NULL/NOT NULL definition is a constraint, so you'd need to clarify what you mean by "no constraint should be created"; do you mean creating a trigger to try and put the same logical constraint in place?

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!

pooya1072 SSC Veteran Points: 256 More actions · Answer 3

sorry if it seems a homework but it doesn't

anyway ... i get my answer ... thanks

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

So to answer your question. NO this isn't possible. #1 and #3 make this impossible. If you have a default, you have a constraint. Give a shot and check it out.

CREATE TABLE SomeTable

(

ID int NOT NULL,

MyInt int NULL default 0

)

There is no constraint defined right? Run this, then expand the constraints in SSMS.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lowell SSC Guru Points: 323518 More actions · Answer 5

and noting that DEFAULT(0) is just shorthand for creating a constraint might make this more obvious, as well:

ALTER TABLE SomeTable

Add MyColumn int --data type

null --null or not null

default(0) --default value: actually equivalent to

--CONSTRAINT [DF__SomeTable__MyCol__53CEE5E3] DEFAULT (0))

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!