Support us .Net Basics C# SQL ASP.NET ADO.NET MVC Slides C# Programs Subscribe Buy DVD

Default constraint in sql server - Part 4

In Part 3 of this video series, we have seen how to create tables (tblPerson and tblGender) and enforce primary and foreign key constraints. Please watch Part 3, before continuing with this session.







In this video, we will learn adding a Default Constraint. A column default can be specified using Default constraint. The default constraint is used to insert a default value into a column. The default value will be added to all new records, if no other value is specified, including NULL.


Altering an existing column to add a default constraint:
ALTER TABLE { TABLE_NAME }
ADD CONSTRAINT { CONSTRAINT_NAME }
DEFAULT { DEFAULT_VALUE } FOR { EXISTING_COLUMN_NAME }


Adding a new column, with default value, to an existing table:
ALTER TABLE { TABLE_NAME } 
ADD { COLUMN_NAME } { DATA_TYPE } { NULL | NOT NULL } 
CONSTRAINT { CONSTRAINT_NAME } DEFAULT { DEFAULT_VALUE }


The following command will add a default constraint, DF_tblPerson_GenderId.
ALTER TABLE tblPerson
ADD CONSTRAINT DF_tblPerson_GenderId
DEFAULT 1 FOR GenderId


The insert statement below does not provide a value for GenderId column, so the default of 1 will be inserted for this record.
Insert into tblPerson(ID,Name,Email) values(5,'Sam','s@s.com')


On the other hand, the following insert statement will insert NULL, instead of using the default.
Insert into tblPerson(ID,Name,Email,GenderId) values (6,'Dan','d@d.com',NULL)


To drop a constraint
ALTER TABLE { TABLE_NAME } 
DROP CONSTRAINT { CONSTRAINT_NAME }


In the next session, we will learn about cascading referential integrity

7 comments:

  1. is it possible to change the default value for exisintg default constraint if yes please explain..

    ReplyDelete
    Replies
    1. Yes, we can by altering the constraint.
      Ex:ALTER TABLE tblPerson
      ADD CONSTRAINT DF_tblPerson_GenderId
      DEFAULT 3 FOR GenderId

      Delete
    2. To do that we have to drop the existing constraint and create a new one

      Delete
  2. What if we delete the default value 3 from tblGender and rule of referential integrity-set default is set for the foreign key in tblPerson ?

    ReplyDelete
    Replies
    1. The database integrity will be lost . so it doesn't allow u to delete that default value 3 in the tblGender .. the error will be raised ..

      Delete
  3. Update tbl_Sold1 Set
    Total = Quentity * price

    ReplyDelete
  4. Hi Venkat,
    Is der any restriction on insert command after adding default constraint and foreign key constraint

    i get this error "The INSERT statement conflicted with the FOREIGN KEY constraint "FK_tblPerson_GenderID". The conflict occurred in database "Sample", table "dbo.tblGender", column 'ID'." when i try to insert the values after adding default constraint

    ReplyDelete

If you like this website, please share with your friends on facebook and Google+ and recommend us on google using the g+1 button on the top right hand corner.