Add a bit column - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Add a bit column

Author

Topic

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2005-09-29 : 09:29:28

If have TableX.

How do I add a bit Column to this table with a default of 0 and NOT NULL as constraint?

Does it have to be done in three?

ALTER TABLE TableX ADD Column Active

ALTER TABLE TableX ADD CONSTRAINT DF_TableX_Active DEFAULT 0 FOR Active

ALTER TABLE TableX ADD CONSTRAINT DF_TableX_ActiveNN NOT NULL FOR Active

?

Henri
~~~~
The envious praises me unknowingly

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-29 : 09:38:07

Try this

alter table TableX add bitcol Active not null default 0

Madhivanan

Failing to plan is Planning to fail

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2005-09-29 : 09:43:35

It was slightly different, but close enough!!

alter table TableX add Active bit not null default 0

Thx! Somehow I got stuck using COLUMN at the wrong place and such. Very helpful, thx!

Henri
~~~~
The envious praises me unknowingly

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-29 : 09:44:40

When you alter column then you need to use Alter Column Column_Name
See more in BOL

Madhivanan

Failing to plan is Planning to fail

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2005-09-29 : 09:51:50

One quicky then:

How do I get all the contraints off a specific column (Say NOT NULL bitcolumn Active)?

in other words: If I want to use query analyzer to delete column active, how do I do that?

__
If think I found an answer here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro2k/html/sql00a11.asp

Henri
~~~~
The envious praises me unknowingly

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-29 : 09:59:14

>>How do I get all the contraints off a specific column (Say NOT NULL bitcolumn Active)?

select table_name, Column_name from information_Schema.columns
where data_type='bit' and Is_nullable='No'

>>If I want to use query analyzer to delete column active, how do I do that?

If you mean drop column then

Alter table tableX drop column Active

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-09-29 : 10:06:48

henrikop: have you got Enterprise Manager? The Design Table there is a bit more "visual"

There is a button that looks like a scroll which will script your changes - you can then abandon the changes and use the script instead (e.g. if you want to script it for Dev, then reuse on QA and subsequently Production).

Enterprise Manager will generate scripts that will drop FKs, sort out the columns, reapply the constraints & indexes, and then the FKs, and then any triggers etc. - so that are a tad easier than writing them by hand!

Kristen

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2005-09-29 : 11:16:25

Madhivanan : I know the "Alter table tableX drop column Active" but because of the contraint, if have to delete the constraint first.

Kristen: I love enterprise manager, but I'm building MS Access for ASP.NET

.

I have a virtual database that will maintain a structure of the database. With profiler I c what enterprise manager does with changing tables (it renames the target table, contructs a whole new table and puts all the data in that table... it's a lot of work

).

I just got some things that don't work smooth.

But thanks anyway, I'm a big step further... it's a pity though that all developers have to reinvent the wheel (we all did, didn't we?).

Henri
~~~~
The envious praises me unknowingly

Kristen
Test

22859 Posts

Posted - 2005-09-29 : 12:19:32

"I'm building MS Access"

Ah ... the sum total of my knowledge just ran out then!

"reinvent the wheel"

That's a heck of a posh phrase for "Google and then Cut&Paste" - I'm gonna keep that one!

Kristen

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2005-09-29 : 13:53:54

Ok.

.

I could write an epistel about how ineffective the development community is. I don't know how to solve it, but to quote a dead man: "I have a dream!"

In that dream community's are reaching a next level. Make the perfect model together how to coop with time-zones, localization, best practices in doing HRM, CRM, solutions for the pitfalls every developer is falling in. Best practices for O/R mapping, designing multi-tier applications and a communication model for EAI.

Ok, I'm raving. Just a bit frustrated with making a dynamic application where I can build whatever datasystem somebody comes up with.

Henri
~~~~
The envious praises me unknowingly

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources