Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

A recent definition I've read about Default Constraints:

DEFAULT is specified for a column to assign a column a specific value if no value is specified when a row is added.

I believe I've seen code written without the DEFAULT constraint specified and a NULL value was assigned to the field.

If I add a column to a table, do I have specify the DEFAULT keyword for the default value to be assigned? Or will NULL be assigned since I didn't delineate DEFAULT?

share|improve this question
    
Which DBMS are you using? Postgres? Oracle? –  a_horse_with_no_name Apr 1 at 13:25
    
Apologies, excuse my ignorance, I was thinking this was a standard among all modern DBMS'. The example I saw related to MS SQL Server. –  Sean Perkins Apr 1 at 13:38
1  
It it more or less standard, but still different DBMS behave differently. –  a_horse_with_no_name Apr 1 at 13:38

1 Answer 1

up vote 4 down vote accepted

If your column definition does not have default constraint and it is nullable, the nullable field will have null mark, when you add a row even when you specify DEFAULT keyword for your insert value. For example for the following table definition

 CREATE TABLE Test(
    ColA  [nvarchar](50) NOT NULL,
    COLB [nvarchar](50) NULL)

The first three insert statements will insert NULL mark for ColB. Only the last one will have ColB value assigned.

 insert into Test(ColA, ColB) values ('a', DEFAULT);
 insert into Test(ColA, ColB) values ('b', NULL);
 insert into Test(ColA) values ('c');
 insert into Test(ColA, ColB) values ('d', 'e');

Whereas for the following table definition with DEFAULT constraint.

CREATE TABLE Test(
    ColA  [nvarchar](50) NOT NULL,
    COLB [nvarchar](50) NULL DEFAULT 'x')

Only the second statement below will insert NULL mark for ColB. The first and the third statements will assign 'x' as the DEFAULT value.

 insert into Test(ColA, ColB) values ('a', DEFAULT);
 insert into Test(ColA, ColB) values ('b', NULL);
 insert into Test(ColA) values ('c');
 insert into Test(ColA, ColB) values ('d', 'e');
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.