CODEWITHSUNDEEP
sqlsql-server
Newbie
Newbie

Reputation: 27

SQL Server Add a Bit Column with 2 default values, and it should default to one value

I have to add a new column Isactive with (data type bit, not null) ANd Populate the column with: 1 = active 0 = not active (default value). Thanks in Advance.

I tried this by searching old question : "

alter table tblname
    add (STATUS VARCHAR(15) default '0',
         constraint conSTATUS check (STATUS in ('1', '0')))

" but how to set 1 = active & 0 = not active.

Upvotes: 0

Views: 3866

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

Add a computed column. This is not a constraint thing:

alter table NCT_UserRegistration
    add isactive as (case when status = 'Active' then 1 else 0 end);

bit is not really a SQL data type (although some databases do support it. An int should be fine for your purposes.

Upvotes: 2

VBoka
VBoka

Reputation: 9083

I believe you wanted to know how will you add values true and false to the table. I will start from a demo sample of your table(just for example): 

create table tblname(id int, name varchar(20))

I will insert one row:

insert into tblname values (1, 'Marc')

This select query select * from tblname will result:

| ID | name |
| 1  | Marc |

Then we alter the table and add a BIT column called Isactive (with the constraint from your question) 

alter table tblname add 
   Isactive  bit default 0 not null, 
   constraint conSTATUS check (Isactive in ('1', '0'));

Because the default value is 0 then the row with ID = 1 and name = 'Marc' will have a new value of the column Isactive set to 0. But when you run this query select * from tblname again it will give you this results:

| ID | name | isactive |
| 1  | Marc | False    |

If you then insert another row in your table like this:

insert into tblname values (2, 'Ann', 1);

with this query select * from tblname your results will be:

| ID | name | isactive |
| 1  | Marc | False    |
| 1  | Ann  | True     |

I hope this helps.

Here is a DEMO

P.S. More interesting thins about BIT type in SQLServer you can find here: https://www.sqlservertutorial.net/sql-server-basics/sql-server-bit/

Upvotes: 1

forpas
forpas

Reputation: 164089

but how to set 1 = active & 0 = not active

You don't set anything.
STATUS is a flag column and it can be translated in the presentation layer as you wish.
When you query the table use a CASE expression like this:

select
  case STATUS 
    when 0 then 'not active'
    when 1 then 'active'
  end
from tblname

Upvotes: 1

Related Questions