CREATE TABLE #tmp (id1 INT, id2 INT DEFAULT 0);

INSERT INTO #tmp (id1) VALUES (1); -- works
INSERT INTO #tmp VALUES (2); -- does not work

DROP TABLE #tmp

CREATE TABLE #table (col1 INT, col2 INT DEFAULT 0);

INSERT INTO #table VALUES( 1, DEFAULT);
INSERT INTO #table VALUES( 2, 123 ); 
INSERT INTO #table(col1) VALUES(3); 

SELECT * FROM #table
DROP TABLE #table

OUTPUT:

col1        col2
----------- -----------
1           0
2           123
3           0

quote:

---

Originally posted by ~AsEdu~

Can someone please tell me , what is the use of DEFAULT constraint in sql?
i know that it is used to insert a default value into a column.
But i have to give a value when inserting values into the columns even though i give a default value when creating that particular columns.


pls help..

---

quote:

---

Originally posted by sunitabeck

Your understanding of default value is correct; Google for it if you are interested in all the gory details.

Whether you have to give a value or not depends on what your insert statement looks like. For example, if you don't explicitly list the columns in the insert part of the insert statement, you will need to provide values for even columns that have default values - see this example:

CREATE TABLE #tmp (id1 INT, id2 INT DEFAULT 0);

INSERT INTO #tmp (id1) VALUES (1); -- works
INSERT INTO #tmp VALUES (2); -- does not work

DROP TABLE #tmp

---

quote:

---

Originally posted by bandi

Hi,

CREATE TABLE #table (col1 INT, col2 INT DEFAULT 0);

INSERT INTO #table VALUES( 1, DEFAULT);
INSERT INTO #table VALUES( 2, 123 ); 
INSERT INTO #table(col1) VALUES(3); 

SELECT * FROM #table
DROP TABLE #table

OUTPUT:

col1        col2
----------- -----------
1           0
2           123
3           0

---