Support us .Net Basics C# SQL ASP.NET Aarvi MVC Slides C# Programs Subscribe Download

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

20 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
    3. First we have to drop existing default constraint

      Delete
    4. I'M FACING THIS ISSUE:
      Msg 1752, Level 16, State 0, Line 1
      Column 'GenderId' in table 'tblPerson' is invalid for creating a default constraint.
      Msg 1750, Level 16, State 0, Line 1
      Could not create constraint. See previous errors.

      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
    Replies
    1. At the start when tblGender is created it only had two values 1 and 2 for male and females. In this video the tblGender was changed and one more column with ID = 3 is added saying unknown. So when you are trying to give a default value of 3, it will be checked with the Primary key of tblGender which don't have any 3 . This may be the error ig

      Delete
  5. IF we are adding multiple records to the table, without providing a value for GenderID, will it be adding 1 for all the fields? If so, since GenderID is a primary key, it has to be a unique value, so how does this work?

    ReplyDelete
    Replies
    1. Gender ID is primary key in tblGender table, where as value you are inserting in tblPerson table.

      -- Rajeev Ranjan Tiwary

      Delete
  6. Sir can we use more than one default constraints for a single table
    either they work correctly or not

    ReplyDelete
    Replies
    1. No,Restrictions .Depending on your requirement what u want u can..

      Delete
  7. HI Vekant ,

    Please upload Videos on SSIS,SSRS,

    Thanks

    ReplyDelete
  8. HI Vekant ,

    Please upload Videos on SSIS,SSRS,

    Thanks

    ReplyDelete
  9. how to alter column to set primary key in existing table

    ReplyDelete
    Replies
    1. alter table tableName
      add primary key (columnName)

      Make sure you don't have any duplicate data in the column before adding the constraint to it. Hope this helps.

      Delete
    2. ALTER TABLE tableName
      ADD CONSTRAINT {Constraint_Name} PRIMARY KEY (Primary_Key_Column_Name)

      Delete
  10. Hi Venkat,

    Could you please give me an example for this

    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 }

    ReplyDelete

It would be great if you can help share these free resources