Default keyword

In this blog, we’ll discuss about Default keyword.

Default keyword is different then Default constraint and below examples will help us to understand it clearly. Like Default Constraint , Default keyword does not create any constraint in the table or with column, thus we are free to change the datatype,modify or drop the table / column any time without any restriction.

–Example1: If the column is NULL-able, then using the “DEFAULT” keyword equals to  NULL in insert statement
create table test
(
Id int not null,
name varchar(50)
)

–Below statements will insert NULL in the name column for both the rows
insert into test values(1,Default),(2,NULL)

select * from test
/*
Id name
1 NULL
2 NULL
*/

–Example2: Insert statement for table with single IDENTITY column.
–We know that IDENTITY is an auto entry field, so we are not able to enter any explicit value for IDENTITY column until & unless it is SET TO Insert ON.
–Also we know that DEFAULT constraint is not applicable for column with IDENTITY property. Here again the “DEFAULT” will work as keyword instead of constraint.

create table test
(
Id int not null IDENTITY(1,1)
)

–It’ll insert value 1 in ID column and keep on increment it by 1 every time we run the same insert query
insert into test DEFAULT values

–OR
insert into test values (DEFAULT)

select * from test
–Output
/*
Id
1
*/

–Example3: Default constraint can be combine with Sequence database object. (Sequence type object was introduced with SQL 2012)
— Here create a SEQUENCE object first. For Detail discussion on SEQUENCE please visit the blog with Sequence object.
CREATE SEQUENCE Counter AS INTEGER
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 100
CYCLE

GO

CREATE TABLE test
(
Sno int DEFAULT NEXT VALUE FOR Counter,
name varchar(50)
)

insert into test values(default,’ABC’),(default,’XYZ’)
select * from test
–Output
/*
Sno name
1 ABC
2 XYZ
*/

Few examples might not work properly in MS SQL version less than 2008 R2. Please share your valuable comments.

Default Constraint

In this blog, we’ll discuss about DEFAULT constraint.

Default constraint can be defined in 2 ways : Database level & Table level

Database level definition of the Default constraint

  1. It is older version of defining the constraint in Database level.
  2. It storage location is Database=>Progammability=>Defaults.
  3. It provides a way of code re-usability.
  4. A single constraint can be attached with multiple tables and columns in the table.
  5. It cannot be altered after defining. It need to be drop and re-create with the new definition.
  6. To apply the constraint, it need to be bind with the column.
  7. To drop the constraint, it need to unbind from all the column of the table.
  8. As per Microsoft, it is not a recommended practice to use it, as it’ll be removed from the future version of MS SQL Server.Click here for MSDN link for details.

Example

–Define the DEFAULT constraint
CREATE DEFAULT Default_Gender AS ‘Male’

create table test
(
Id int,
Gender varchar(10)
)

–Bind the DEFAULT constraint
EXEC sp_bindefault ‘Default_Gender’, ‘test.Gender’

insert into test values(1,default)
Select * from test

/* Output
Id Gender
1 Male
*/

–UnBind the DEFAULT constraint or remove the constraint from the column
EXEC sp_unbindefault ‘test.Gender’

–Delete the DEFAULT constraint
Drop DEFAULT Default_Gender

Table level definition of the Default constraint

  1. It is defined in the table level.
  2. It storage location is Database=>Table=>Constraint.
  3. DEFAULT constraints are applied to default values in column of a table which will take when an INSERT statement doesn’t explicitly assign a particular value or mention the word DEFAULT.
  4. DEFAULT constraint can be applied with table definition or in existing table.
  5. DEFAULT constraint never restrict the NULL value/ any other value, until the column is defined with NOT NULL constraint/ any other constraint.
  6. The default value must be compatible with the data type of the column to which the DEFAULT definition applies. For example, the default value for an int column must be an integer number, it cannot be a character string.
  7. Adding a new column with DEFAULT constraint to an existing table never fill the existing rows of the table with the specified Default value(By default). The DEFAULT constraint is applicable to all the future inserts after its declaration. Need to add the ‘with values‘ property with DEFAULT constraint to automatically insert the Default value in all the existing rows.
  8. Adding the DEFAULT constraint with ‘with values‘ property to existing column of a table never insert the Default values to existing rows. Nether it gives any error. It simple add the DEFAULT constraint and will be applicable for future data inserts.
  9. At any time to change the value of the DEFAULT constraint, we first need to drop the existing DEFAULT constraint and then add a DEFAULT constraint with new value.
  10. Any need to change the column’s datatype or need to drop the column (Column which is having the Default constraint) entirely will require first DROP of the DEFAULT constraint on the column prior to your action.

DEFAULT constraint definitions cannot be created on columns defined with the following

  1. Column with IDENTITY or ROWGUIDCOL
  2. Column datatype is timestamp / rowversion
  3. Sparse Column
  4. An existing DEFAULT definition or DEFAULT object.

Examples

–Example1: DEFAULT constraint with table defination
create table test
(
Id int primary key,
name varchar(50) not null,
city varchar(50) not null DEFAULT ‘New Delhi’
)

–Example2: DEFAULT constraint with table defination with constraint name
create table test
(
Id int primary key,
name varchar(50) not null,
city varchar(50) not null constraint Default_test_city DEFAULT ‘New Delhi’
)

–Example3: DEFAULT constraint outside table defination with constraint name
create table test
(
Id int primary key,
name varchar(50) not null,
city varchar(50) not null
)

ALTER TABLE [dbo].[test]
ADD CONSTRAINT [Default_test_city] DEFAULT (‘New Delhi’) FOR city

Insert result for all the above examples[1/2/3] have same result as ‘New Delhi’ assigned value for Id=1 with DEFAULT Keyword 
insert into test values (1,’ABC’,DEFAULT),(2,’XYZ’,’PUNJAB’)
–Example4: DEFAULT constraint with table defination without NOT NULL
create table test
(
Id int primary key,
name varchar(50) not null,
city varchar(50) DEFAULT ‘New Delhi’
)

–Insert result for the above example4 have NULL value assigned value for Id=1 ). As DEFAULT does not restrict NULL value
insert into test values (1,’ABC’,NULL),(2,’XYZ’,’PUNJAB’)

–Example5: Add a new column in existing table with DEFAULT constraint name
create table test
(
Id int primary key,
name varchar(50) not null
)

ALTER TABLE test  ADD city varchar(50) NOT NULL
    CONSTRAINT Default_test_city ‘New Delhi’

–Example6: Default constraint with NULL value.Though this example does not make any practical meaning, but to show that NULL can also be use as Default value, we are showing this example
create table test
(
Id int primary key,
name varchar(50) DEFAULT (NULL)
)

–Example7: Adding a new column in existing table with DEFAULT constraint name & also insert the Default value to the existing rows
alter table test add gender varchar(1) default(‘M’) with values

–Example8: Adding a new NOT NULL column in existing table with DEFAULT constraint name & also insert the Default value to the existing rows
alter table test add gender varchar(1) not null default(‘M’) with values

Note : DEFAULT constraint & DEFAULT keyword are different. To know more about DEFAULT keyword click here.

To display all the DEFAULT constraint information on all the tables in current database (Note : DK=Default key)

SELECT
@@Servername AS ServerName,
DB_NAME() AS DB_Name,
OBJECT_SCHEMA_NAME(dk.parent_object_id) AS SchemaName,
OBJECT_NAME(dk.parent_object_id) AS TableName,
c1.name as ColName,
t.name as DK_ColDataType,
c1.max_length as DK_ColMaxLength,
c1.precision as DK_ColPrecision,
c1.scale as DK_Scale,
(Case c1.is_nullable When 0 Then ‘No’ Else ‘Yes’ End) as DK_ColIsNULL,
dk.Name AS DK_Const_Name ,
OBJECT_DEFINITION(dk.object_id) AS DK_Const_Defination,
dk.create_date AS DK_CreateDate,
dk.modify_date AS DK_ModifyDate
FROM
sys.default_constraints dk
INNER JOIN sys.columns c1                                — find the foreign key column name
ON dk.parent_object_id = c1.object_id
AND dk.parent_column_id = c1.column_id
INNER JOIN sys.types t                                          — Get the column datatypes
ON c1.user_type_id = t.user_type_id
ORDER BY 4,5

To drop or remove the Default constraint , we need to provide the constraint name. If the constraint name is auto generated then get it GUI from the left side in SSMS under the table => Goto constraint => click on the constraint and drag it over the command window.

ALTER TABLE [dbo].[test] DROP CONSTRAINT [Default_test_city]

Few examples might not work properly in MS SQL version less than 2008 R2. Please share your valuable comments.

Foreign Key Part III / Cascading referential integrity constraint

In this blog, we’ll discuss about the Cascading referential integrity constraint.

When we are creating the Foreign key constraint references to a Primary/Unique key, by default the Cascading referential integrity constraint for the Foreign key is set to “NO ACTION“. [It means we cannot modify or delete the Primary/Unique key once it is references by a Foreign key.]

Cascading referential integrity constraint have 2 rules namely.

  1. ON UPDATE : What happens to the Foreign key column value “ON UPDATE” of the Primary/Unique key value which it is referencing.
  2. ON DELETE : What happens to the Foreign key column value “ON DELETE” of the Primary/Unique key value which it is referencing.

The above two rules can have any one of the below action types

  1. NO ACTION: This is the default behavior.It means Primary/Unique key delete/update statement will be ROLLBACK and no action will be taken or both Foreign & Primary/Unique key. It is applicable by default, so it’ll continue to work even if we didn’t specify it explicitly.
  2. CASCADE : In this case any delete/update query on Primary/Unique key value will be cascading same to all the Foreign keys referring to it.Example on deleting the Primary/Unique key record all the foreign key record referencing it will also be deleted from the table automatically.
  3. SET NULL : In this case any delete/update query on Primary/Unique key value will be set NULL values to all the Foreign keys referring to it.Example on deleting the Primary/Unique key record all the foreign key record referencing it will be set to NULL automatically.
  4. SET DEFAULT : In this case any delete/update query on Primary/Unique key value will be set Default values (of the Foreign key column) to all the Foreign keys referring to it.Example on deleting the Primary/Unique key record all the foreign key record referencing it will be set to Default values (of the Foreign key column) automatically.

Examples

–Table A
create table Dep
(
DID int Primary key,
DepName varchar(50)
)

–Example1A: Table B with foreign key references to Table A with No ACTION (Default)
create table Employee1
(
EmpId int primary key,
DepId int foreign key references Dep(DID)
Name varchar(50),
Gender char(1),
)

–Example1B: Table B with foreign key references to Table A with No ACTION
create table Employee1
(
EmpId int primary key,
DepId int foreign key references Dep(DID) on UPDATE NO ACTION
Name varchar(50),
Gender char(1),
)

–Example2: Table B with foreign key references to Table A with CASCADE
create table Employee1
(
EmpId int primary key,
DepId int foreign key references Dep(DID) on UPDATE CASCADE
Name varchar(50),
Gender char(1),
)

–Example3: Table B with foreign key references to Table A with SET NULL
create table Employee1
(
EmpId int primary key,
DepId int foreign key references Dep(DID) on UPDATE SET NULL
Name varchar(50),
Gender char(1),
)

–Example4: Table B with foreign key references to Table A with SET DEFAULT
create table Employee1
(
EmpId int primary key,
DepId int foreign key references Dep(DID) on UPDATE SET DEFAULT
Name varchar(50),
Gender char(1),
)

For DELETE action just replace the word UPDATE in the foreign key references line with DELETE

Few examples might not work properly in MS SQL version less than 2008 R2. Please share your valuable comments.

Click here to read about the previous part :– Foreign key Part II