problem in setting default value while creating

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

Transact-SQL (2005)

problem in setting default value while creating

Author

Topic

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2008-03-14 : 03:35:08

Hi All.,

here i try to set default value 0 while creating table., But in my code its not working.,any one please help me to know how to set a default value while creating table.,

here is the sample code.,

CREATE TABLE MODULE_ROLE
(
MODULE_ROLE_ID INT IDENTITY(1,1) NOT NULL,
ADD_FLAG BIT default 0,
UPDATE_FLAG BIT default 0,
BIT default 0,
VIEW_FLAG BIT default 0,
INPUT_USER INT
)
insert into MODULE_ROLE values (null,null,1,null,1)

select * from module_role

----------------------------------------------------------------------
MODULE_ROLE_ID ADD_FLAG UPDATE_FLAG DELETE_FLAG VIEW_FLAG INPUT_USER
-------------- -------- ----------- ----------- --------- -----------
1 NULL NULL 1 NULL 1

BUT I NEED 0 FOR NULL VALUES..,

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-03-14 : 04:16:40

the default value works when you did not supply value to the column.

in your case,

insert into MODULE_ROLE values (null,null,1,null,1)

you are supplying NULL value to ADD_FLAG, UPDATE_FLAG and VIEW_FLAG

You can do it this way


insert into MODULE_ROLE values (default,default,1,default,1)

OR just specify the column lists with value


insert into MODULE (BIT , INPUT_USER) values (1, 1)

Anyway you should not omit the column lists in the INSERT INTO statement. The existing insert statement will not work when a new column is added to the table

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources