create table default zero for null value 1

[peac3]

Hi Guys,

How to create table with constraint default 0 if null provided,

This is what i've tried
create table testing (
column1 numeric(18,2) not null default 0,
column2 varchar(10) null
)

It's not working when I inserted null value.

Any idea guys?

Thanks,

 

[bborissov]

Just do not force to insert NULL, either just remove the field from your INSERT statement:

DECLARE @Test TABLE (Fld1 Int NOT NULL DEFAULT 0, Fld2 Int)
INSERT INTO @Test (Fld2) VALUES (1)

SELECT * FROM @Test

either use the DEFAULT keyword:

DECLARE @Test TABLE (Fld1 Int NOT NULL DEFAULT 0, Fld2 Int)
INSERT INTO @Test (Fld1, Fld2) VALUES (DEFAULT, 1)

SELECT * FROM @Test

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[peac3]

sorry bborissov,

what i need to create is NULL default 0
So it wouldn't work if I alter the table

CREATE TABLE testing (Fld1 Int NOT NULL DEFAULT 0, Fld2 Int)

ALTER TABLE testing
ALTER COLUMN Fld1 Int NULL DEFAULT 0

the error code was :
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'DEFAULT'.

any suggestion?

Thank you,

 

[bborissov]

CREATE TABLE testing (Fld1 Int NOT NULL DEFAULT 0, Fld2 Int)
GO
ALTER TABLE testing
ALTER COLUMN Fld1 Int NULL

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.