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
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
is it possible to change the default value for exisintg default constraint if yes please explain..
ReplyDeleteYes, we can by altering the constraint.
DeleteEx:ALTER TABLE tblPerson
ADD CONSTRAINT DF_tblPerson_GenderId
DEFAULT 3 FOR GenderId
To do that we have to drop the existing constraint and create a new one
DeleteFirst we have to drop existing default constraint
DeleteI'M FACING THIS ISSUE:
DeleteMsg 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.
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 ?
ReplyDeleteThe 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 ..
DeleteUpdate tbl_Sold1 Set
ReplyDeleteTotal = Quentity * price
Hi Venkat,
ReplyDeleteIs 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
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
DeleteIF 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?
ReplyDeleteGender ID is primary key in tblGender table, where as value you are inserting in tblPerson table.
Delete-- Rajeev Ranjan Tiwary
Sir can we use more than one default constraints for a single table
ReplyDeleteeither they work correctly or not
No,Restrictions .Depending on your requirement what u want u can..
DeleteHI Vekant ,
ReplyDeletePlease upload Videos on SSIS,SSRS,
Thanks
HI Vekant ,
ReplyDeletePlease upload Videos on SSIS,SSRS,
Thanks
how to alter column to set primary key in existing table
ReplyDeletealter table tableName
Deleteadd primary key (columnName)
Make sure you don't have any duplicate data in the column before adding the constraint to it. Hope this helps.
ALTER TABLE tableName
DeleteADD CONSTRAINT {Constraint_Name} PRIMARY KEY (Primary_Key_Column_Name)
Hi Venkat,
ReplyDeleteCould 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 }