SQL DEFAULT Constraint

SQL DEFAULT Constraint


The SQL DEFAULT constraint provides a default value to a field when the user did not provide a specific value.

The default value will be added to all newly inserted records, if no other value is specified with SQL INSERT INTO statement by user.



Sql default constraint used for alter table alter column set default value, sql server create table default value, Add Default to Existing Table, Change Value or Check if Exist.

SQL DEFAULT Constraint on CREATE TABLE

The following SQL statement creates a DEFAULT constraint on the "AuthorName" field when the "Books" table is created:

Microsoft SQL Server / Oracle / Microsoft Access / MySql


CREATE TABLE Books
(
BookID INT,
BookName VARCHAR(255),
AuthorName VARCHAR(255) DEFAULT 'N/A',
BookPrice DECIMAL (7, 2) DEFAULT 100.00
);

In the above example, the following SQL statement creates a new table called Books and adds four fields. Here, AuthorName and BookPrice fields has default value.

Here, AuthorName field is set to 'N/A' and BookPrice field is set to '100.00' by default, so in case SQL INSERT INTO statement does not provide a value for these fields, then by default these fileds would be set to their default values.

To insert records on "Books" table, use the following SQL statements:


INSERT INTO Books (BookID, BookName, AuthorName)
VALUES (111, 'Sql Programming','Suresh Babu');
INSERT INTO Books (BookID, BookName, BookPrice)
VALUES (222, 'Sql Hacking', 225);
INSERT INTO Books (BookID, BookName, AuthorName, BookPrice)
VALUES (333, 'Sql Query Performance', 'Siva Kumar' 150);

The SQL statement above would insert three new records into the "Books" table.

In the first record we didn't specify a value for the "BookPrice" field in the INSERT INTO statement, it does get assigned the default value of "100.00".

In the second record we didn't specify a value for the "AuthorName" field in the INSERT INTO statement, it does get assigned the default value of "N/A".

After executing above query, the "Books" table look like this:

BookId BookName AuthorName BookPrice
111 Sql Programming Suresh Babu 100.00
222 Sql Hacking N/A 225
333 Sql Query Performance Siva Kumar 150

SQL DEFAULT Constraint on ALTER TABLE

To create a SQL DEFAULT constraint on the "AuthorName" field when the table is already created, use the following SQL statement:

For MySQL


ALTER TABLE Books
ALTER AuthorName SET DEFAULT 'Not Available';

For Microsoft SQL SERVER / Microsoft ACCESS


ALTER TABLE Books
ALTER COLUMN AuthorName SET DEFAULT 'Not Available';

For Oracle


ALTER TABLE Books
MODIFY AuthorName DEFAULT 'Not Available';

To DROP a DEFAULT Constraint

To drop or remove a DEFAULT constraint, use the following SQL statement:

For MySQL


ALTER TABLE Books
ALTER AuthorName DROP DEFAULT;

For Microsoft SQL SERVER / Microsoft ACCESS / ORACLE


ALTER TABLE Books
ALTER COLUMN AuthorName DROP DEFAULT;


Sql defalut constraint using sql server add default constraint, create table default value, insert dafault values in sql, SQL Default Constraint Rename, Disable, or Drop.

SQL Default Constraint Add Default to Existing Table

You may need to change an existing table in SQL Server on occasion. Use the ALTER TABLE statement to add a DEFAULT constraint to an existing column. Specify the column and the particular requirement you want to apply.

Syntax:

The basic syntax is as follows

ALTER TABLE < tableName >
  ADD CONSTRAINT < constraintName > DEFAULT < default > FOR < columnName >;

Example 1: The empty string is set as the default value for the Department column in the Employee table in the given description. The constraint's name is  DF_ Employee_Department.

ALTER TABLE Employee
ADD CONSTRAINT DF_Employee_Department DEFAULT '' FOR Department;

Example 2: Adding default constraint to an existing table:

Add a DEFAULT constraint to an established employee_details table's dept id column. The query was as follows-

ALTER TABLE employee_details
ALTER COLUMN dept_id SET DEFAULT 1000;

We may add a DEFAULT constraint on dept id to the employee_details table by running the above query.

Example 3: Here’s an example of adding a DEFAULT constraint to an existing column called TaskDescription:

USE Solutions;
ALTER TABLE Tasks  
 ADD CONSTRAINT taskdesc_default  
 DEFAULT 'TBA' FOR TaskDescription;  
GO

We switched to the Solutions database first in this manner, which guarantees that the constraint was created against the correct database.

We then defined a DEFAULT constraint for the TaskDescription column named taskdesc default and set the default value to TBA. This implies that if no value is given for the TaskDescription column when a new row is generated, a default value of TBA will be placed.

Example 4: Check the Constraint:

You can check that the constraint has been created by running the following statement:

USE Solutions;
SELECT * 
FROM sys.default_constraints;

The default restrictions for the Solutions database are listed here. We switched to the right database first once more.

You can always filter down your database to just the constraint you're concerned in if it has a lot of them:

USE Solutions;
SELECT * 
FROM sys.default_constraints
WHERE name = 'taskdesc_default';

SQL Default Constraint Change Value

DEFAULT is defined as a column attribute in the ANSI SQL standard, however Microsoft interpreted it as a constraint type. As a result, the ALTER TABLE...ALTER COLUMN syntax cannot be used to alter a DEFAULT. Instead, drop the DEFAULT constraint like you would other constraint objects (such as FOREIGN KEYS, PRIMARY KEYS, and so on) and re-add it with the new default value.

To set a default value for a table column.

If the option to permit null values is not enabled, the column will keep blank, but the user will be unable to save the row, and NULL will be entered into the column.

There are various ways that you can specify a default value for a column:

  • Enter the number to set a numeric default value.
  • Enter the name of an object or function.
  • Put the value within single quotes for an alphanumeric default.

However, if your DEFAULT restrictions are named, this isn't always the case. If a DEFAULT is declared inline within a table without a name, the SQL Server database engine will construct a unique name for it. This unusual name is a little unappealing and difficult to deal with. This auto-generated name will differ from database to database if you have many databases. As a result, utilising an auto-generated name statically within a static DROP CONSTRAINT declaration is not possible. In these circumstances, the only option to change DEFAULT constraints with auto-generated names is to get the name from SQL Server's system catalogue and perform the DROP with a dynamic SQL query.

Limitations and Restrictions

Before you begin, be aware of the following limitations and restrictions:

You will be requested to unbind the default and replace it with your new default if your entry in the Default Value box substitutes a bound default (which is displayed without parentheses).

Use single quotation marks (') to enter a text string; do not use double quotation marks (") because they are reserved for quoted identifiers.

To set a numerical default, type the number without the quotation marks.

To add an object or function, type the name of the object or function without the quotation marks.

Only constants can be used as default constraints in Azure Synapse Analytics. A default constraint cannot be combined with an expression.

Note: Only constants can be used as default constraints in Azure Synapse Analytics. A default constraint cannot be combined with an expression.

Example 1: Enter the following query in the query window and click Execute :

CREATE TABLE dbo.doc_exz (column_a INT, column_b INT); -- Allows nulls.
GO
INSERT INTO dbo.doc_exz (column_a) VALUES (7);
GO
ALTER TABLE dbo.doc_exz
  ADD CONSTRAINT DF_Doc_Exz_Column_B
  DEFAULT 50 FOR column_b;
GO

Example 2: The instance below creates a new table with a specified constraint, then updates the constraint:

create table dbo.CustomerReport
(
customer_id int,
report_id int,
rundate datetime not null
constraint df_CustomerReport_rundate default getdate()
)
go

exec sp_helpconstraint [dbo.CustomerReport]
go

Let's pretend that now that the table is established, we need to keep our run dates in UTC time rather than local server time. Let's try changing the rundate's default from getdate() to getutcdate():

alter table dbo.CustomerReport alter column rundate default getutcdate()

As you can see, we receive a syntax error. To make the change, we need to DROP the constraint and then re-add it:

alter table dbo.CustomerReport
drop constraint df_CustomerReport_rundate
go
alter table dbo.CustomerReport
add constraint df_CustomerReport_rundate default getutcdate() for rundate
go
exec sp_helpconstraint [dbo.CustomerReport]
go

Example 3: The INFORMATION_SCHEMA views in SQL Server provide views that report on database restrictions, although DEFAULTs are not included. Constraints are defined as a column property in the ANSI-SQL standard, as previously stated. Defects aren't included in the INFORMATION_SCHEMA views since they aren't an ANSI-SQL defined type of constraint. However, the following system table catalogs make it simple to learn about a column's DEFAULT restriction:

With SQL Server 2005 auto-generating the constraint name, the illustration recreates our CustomerReport table. The DEFAULT is then modified, with a user-friendly and, more crucially, consistent name.

Let's start by re-creating the example table and looking at the constraint name that is created.

drop table dbo.CustomerReport
go
create table dbo.CustomerReport
(
customer_id int,
report_id int,
rundate datetime not null default getdate()
)
go

exec sp_helpconstraint [dbo.CustomerReport]
go

Now, let's redefine the DEFAULT to use the UTC date:

declare @default_name varchar(256)
select @default_name = [name] from sys.default_constraints
where parent_object_id = object_id('CustomerReport')
and col_name(parent_object_id, parent_column_id) = 'rundate'

exec('alter table dbo.CustomerReport drop constraint ' + @default_name)
go

alter table dbo.CustomerReport
add constraint df_CustomerReport_rundate default getutcdate() for rundate
go

exec sp_helpconstraint [dbo.CustomerReport]
go

The SQL Server 2000 specific query to get the same column default name would look like this

select object_name(cdefault) as 'DefaultName' from syscolumns
where [id] = object_id('CustomerReport')
and [name] = 'rundate'
go

Because DEFAULTs are implemented as constraints, any need to modify a column's datatype or drop the column completely will necessitate a DROP of any DEFAULT constraint on the column before proceeding.

When working with database objects, I advocate using INFORMATION_SCHEMA views wherever possible and avoiding reading system tables if possible. My explanation is that SQL Server system catalogues can (and have) been modified between releases. Furthermore, some of these catalogues may be completely deleted from SQL Server. Some of the well-known SQL Server 2000 system catalogue tables, such as sysobjects and sysindexes, are still available as backward compatible views in SQL Server 2005, however they may be deprecated by the time SQL Server 2008 is launched. Reading the system catalogues may be necessary in some circumstances. Make sure you document where you're reading system tables directly in these circumstances. I also propose naming ALL database objects, regardless of whether they are PRIMARY KEY, UNIQUE, CHECK, DEFAULT, or whatever else. Explicitly identifying all database items reduces the need to read system catalogues if you need to update these objects in the future, and it also improves the documentation of your database.

Example 4:

CREATE TABLE dbo.doc_exz (
   column_a INT,
   column_b INT DEFAULT 50);
Named CONSTRAINT (T-SQL)

CREATE TABLE dbo.doc_exz (
column_a INT,
column_b INT CONSTRAINT DF_Doc_Exz_Column_B DEFAULT 50);

SQL Default Constraint Check if Exist

Example 1: You want to verify if a default constraint exists before attempting to drop it and its parent column in DDL SQL updates that are being rolled out and rolled back.

The majority of schema tests may be performed using SQL Server's built-in information schema views. To check the presence of columns, for instance, you can use a query like this to query the information schema view for columns:

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'yourtablename' AND COLUMN_NAME = 'yourcolumnname')

However, there is nothing in any of the information schema views that checks for a named default constraint. You'll need these for that:

IF EXISTS(SELECT * FROM SYS.OBJECTS WHERE TYPE_DESC =  'DEFAULT_CONSTRAINT' AND NAME = 'yourconstraintname')

or:

IF EXISTS(SELECT * FROM SYS.OBJECTS WHERE TYPE = 'D' AND NAME = 'yourconstraintname')

Example 2:

ALTER TABLE dbo.ChannelPlayerSkins
DROP CONSTRAINT FK_ChannelPlayerSkins_Channels

However, the constraint appears to have a different name in some of the other databases we utilise. How can I see if the name FK_ ChannelPlayer_Skins Channels has any constraints?

try below code,

SELECT   * 
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS 
WHERE CONSTRAINT_NAME ='FK_ChannelPlayerSkins_Channels'

-- EDIT --

When I originally answered this question, I was thinking "Foreign Key" because the original question asked about finding "FK_ChannelPlayerSkins_Channels". Since then many people have commented on finding other "constraints" here are some other queries for that:

--Returns one row for each CHECK, UNIQUE, PRIMARY KEY, and/or FOREIGN KEY

SELECT * 
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_NAME='XYZ'

--Returns one row for each FOREIGN KEY constrain

SELECT * 
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS 
WHERE CONSTRAINT_NAME='XYZ'

--Returns one row for each CHECK constraint

SELECT * 
FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS
WHERE CONSTRAINT_NAME='XYZ'

Here is an alternate method

--Returns 1 row for each CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY, and/or DEFAULT

SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint
,SCHEMA_NAME(schema_id) AS SchemaName
,OBJECT_NAME(parent_object_id) AS TableName
,type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT'
AND OBJECT_NAME(OBJECT_ID)='XYZ'

If you require further constraint data, go at the system stored procedure master.sys.sp helpconstraint to learn how to obtain it. Enter the "Object Explorer" in SQL Server Management Studio to see the source code. Expand the "Master" database, then "Programmability," "Stored Procedures," and finally "System Stored Procedures." Then locate "sys.sp helpconstraint," right-click it, and choose "edit." Just make sure you don't save any modifications. You may also use EXEC sp helpconstraint YourTableNameHere to use this system stored procedure on any table.


SQL Default Constraint Create

When we create our tables, we set some columns to be null, so that some columns are never null. If the columns that we create as nullable are sometimes null, then we may want to set the default value to this column if the value is not given to this column.

Example 1: Let’s create a table as follows. In this table, the names of the city and the famous food of these cities will be stored.

The TownName column is defined as NOT NULL as you see below. So this column will never be null. The FamousFood column is defined as NULL. So this column could also be null. Because there might be cities without famous food.

Step 1:

CREATE TABLE dbo.DefaultConstraintExample
(
ID int NULL,
CityName varchar(250) NOT NULL,
FamousFood varchar(250) NULL
) ON [PRIMARY]

Step 2: Let’s do a few insert operations to our table as follows and then we’ll select values from the table.

INSERT INTO [dbo].[DefaultConstraintExample]([ID],[CityName],[FamousFood])VALUES(1,'Gaziantep','Lahmacun')
INSERT INTO [dbo].[DefaultConstraintExample]([ID],[CityName],[FamousFood])VALUES(2,'Ankara',null)
GO

Step 3:

SELECT * FROM [dbo].[DefaultConstraintExample]

As you can see, we set the FamousFood column for Ankara as null.

Step 4: Let’s define a default constraint for FamousFood column with the help of the following script. This default constraint will set the value of the column to “There is not famous food” by default if the FamousFood column is null.

ALTER TABLE dbo.DefaultConstraintExample ADD CONSTRAINT

Step 5: DF_DefaultConstraintExample_FamousFood DEFAULT 'There is not famous food' FOR FamousFood

As you can see, we did not insert a famous food record for Istanbul. For this reason, by default, “There is not Famous Food” was set to FamousFood column.

INSERT INTO [dbo].[DefaultConstraintExample]([ID],[CityName])VALUES(3,'Istanbul')
GO
SELECT * FROM [dbo].[DefaultConstraintExample]

Example 2: There are two ways to create DEFAULT constraints for columns:

Use CREATE TABLE statement if the table is new

Use ALTER TABLE statement for an existing table.

You can assign a DEFAULT constraint to a column in the CREATE TABLE statement as the following statement:

CREATE TABLE books (
  book_id INT NOT NULL PRIMARY KEY,
  title varchar(255) NOT NULL,
  pubdate date NOT NULL,
  isbn varchar(13) DEFAULT '1-84356-028-3',
  author_id INT NOT NULL
)

The isbn column in the books table accepts ‘1-84356-028-3’ as the default value. If we insert a new row into the books table without specifying the value for ISBN column, the database engine will insert the value 1-84356-028-3 into the isbn column. See the following INSERT statement that adds a new book to the books table:

INSERT INTO books(title,pubdate,author_id)
VALUES('SQL Tutorial','2010-01-01',1);

We can query the books table by using SELECT statement to see what value has been inserted into the isbn column:

SELECT * FROM books;

The DEFAULT constraint accepts not only literal value but also a value returned by a function. For example, we assign today date as the default value for the pubdate column by using the following statement:

ALTER TABLE books 
ADD CONSTRAINT df_pubdate
DEFAULT GETDATE() FOR pubdate

SQL Default Constraint Disable

Allows you to specify whether constraint name is enabled or disabled. Only FOREIGN KEY and CHECK constraints can be utilised with this choice. The constraint is disabled when NOCHECK is set, and future inserts or modifications to the column are not validated against the constraint constraints. The restrictions DEFAULT, PRIMARY KEY, and UNIQUE cannot be deactivated.

You'll have to DROP the limitation and then recreate it afterwards.

To remove a default value to a column in SQL Server, use the ALTER TABLE .. DROP CONSTRAINT command:

alter table products
drop constraint df_category;

SQL Default Constraint Drop

In MySQL, removing the default constraint is simple. To remove the default value of a column in MySQL, we will use the ALTER TABLE statement with the DROP DEFAULT clause.

When new rows are added to a table, you may use the default constraint to automatically specify default values for MySQL columns. However, you might need to drop the default value or remove the default constraint in MySQL on occasion.

Syntax:

Here is the syntax to drop default constraint in MySQL

ALTER TABLE table_name           
ALTER column_name DROP DEFAULT;

You must mention the table name and column name for which you wish to remove the default value in the above query.

Example 1: Here is an example to remove default constraint:

ALTER TABLE sample_data        
ALTER order_date DROP DEFAULT; 

Example 2: This SQL Server stored procedure locates the name of the default constraint created on a table column and deletes it. To erase the default constraint, the "ALTER TABLE DROP CONSTRAINT" command is constructed automatically and executed using the SQL sp executesql stored procedure.

CREATE PROC SP_DROP_Default_Constraint
(
 @table_name sysname,
 @column_name sysname
)
AS

--declare @table_name sysname, @column_name sysname,
--select
-- @table_name = N'SampleConstraintsSQLTable',
-- @column_name = N'IsDefaultConstraintColumn'

declare @default_constraint_name sysname, @sql nvarchar(max)

if exists (
 select *
 from sys.default_constraints
 where
 parent_object_id = OBJECT_ID(@table_name)
 AND type = 'D'
 AND parent_column_id = (
 select column_id
 from sys.columns
 where
 object_id = OBJECT_ID(@table_name)
 and name = @column_name
)
)
begin

 select @default_constraint_name = name
 from sys.default_constraints
 where
 parent_object_id = OBJECT_ID(@table_name)
 AND type = 'D'
 AND parent_column_id = (
 select column_id
 from sys.columns
 where
 object_id = OBJECT_ID(@table_name)
 and name = @column_name
)

SET @sql = N'ALTER TABLE ' + @table_name + ' DROP Constraint ' + @default_constraint_name
exec sp_executesql @sql

end
GO

If no further SQL constraints are established on the target column after the default constraint is deleted, SQL programmers can drop the table column. If a SQL Check constraint is specified, for instance, developers or database administrators can drop the check constraint represented on a table column using a comparable stored procedure without understanding the constraint name.


SQL Default Constraint Number

Use the term Default followed by default value to provide a default value for a column. Use a single quotation for string values (Double quotes not allowed). When it comes to numbers, avoid using quotations.

Important Default Constraint Rules

For strings, use single quote marks.

//Do not use the double quote

Department [varchar](20) Default "Admin"

//Throws the Error

In this case, the title "Admin" is not acceptable. Constants, constant expressions, and (in some cases) variables are all eligible expressions. No column names are allowed.

Enter the number without quotations to make it the default value.

//Admin as the defulat Department

Department [varchar](20) Default 'Admin'

//Empty Space as the default

Department [varchar](20) Default ''

//Numeric values as default values

Amount int Default 0
Rate int Default 100
Price decimal(9,2) Default 10.50

You can make use of Brackets

CREATE TABLE Test (
  Department  [varchar](20) Default ('Admin'),
  Amount int Default (0),
  Price decimal(9,2) Default (10.50)
)

Example 1: The scripts below generate the Employee Table with no preset constraints.

CREATE TABLE Employee (
EmployeeID  [int]         ,
FirstName   [varchar](50) ,
LastName    [varchar](50) ,
Department  [varchar](20) ,
)

We insert a row into the table. But the insert query does not contain any value for the Dept column. Since the Dept is a Nullable column, the SQL Server inserts the NULL into the Dept.

insert into Employee (EmployeeID, FirstName, LastName) 
values (1,'Olive','Yew')
 
Select * from Employee

Result:

EmployeeID FirstName LastName Dept
1 Olive Yew NULL

Note that if the Dept is a Non Nullable column then the SQL Server will throw an error.

Example 2: In the following example, We specify the Admin as the default for the department column

Step 1: Create Table

CREATE TABLE Employee (
 EmployeeID  [int]         ,
 FirstName   [varchar](50) ,
 LastName    [varchar](50) ,
 Department  [varchar](20) Default 'Admin' ,
)

Step 2: Insert records

The Dept column does not have any value in the insert query below. As a result, SQL Server enters Admin as the default value.

//No Value for Department. Admin is inserted

insert into Employee (EmployeeID, FirstName, LastName) 
values (1,'Olive','Yew')

Only when no value is provided during the insertion does SQL Server use the default value. If we type null, it will replace the default value with the null value. An error will be thrown if the column does not allow null values.

//null for Department. Null is stored as it is. Default is not used

insert into Employee (EmployeeID, FirstName, LastName, Department) 
values (2,'Aida','Bugg',null)

Step 3: Display Records

Select * from Employee

Output:

EmployeeID FirstName LastName Dept
1 Olive Yew NULL
2 Aida Bugg NULL

SQL Default Constraint Rename

Example 1: To create scripts that can be used to rename Default Constraints according to your company's guidelines. 'DF_SchemaName_TableName_ColumnName' is what your firm wants to call the default constraints:

Solution: To rename Default Constraints, we can utilise the stored procedure sp rename. Use the following script to rename a single Default Constraint.

Exec sp rename 'Current Default Constraint Name','New Default Constraint Name' Because we need to run this script for all default constraints, we may use the query below to rename them.

SELECT 'exec sp_rename '''
    +Schema_name(d.Schema_id)+'.' 
    + '' + d.Name + ''''
    + ',''DF_' +Schema_Name(d.schema_id)
    +'_'+t.name
    +'_'+c.name+'''' as RenameDefaultConstraintQuery
FROM sys.default_constraints d
INNER JOIN sys.columns c ON
    d.parent_object_id = c.object_id
    AND d.parent_column_id = c.column_id
INNER JOIN sys.tables t ON
    t.object_id = c.object_id

I ran the query above on one of the databases and got the following results. You could always filter out tables in the where clause if you want to exclude them.