Command for Adding a Default Constraint

Command for adding a default constraint

Pretty much, yes for an ALTER TABLE

You can add a columnn with default in one step for CREATE or ALTER too.

ALTER TABLE dbo.TableName
    ADD bar varchar(100) CONSTRAINT DF_Foo_Bar DEFAULT ('bicycle');

ALTER TABLE dbo.TableName
    ADD bar varchar(100) DEFAULT ('bicycle');

As you noted, the system generates a name if one is not supplied. CONSTRAINT constraint_name is optional says MSDN. The same applies to any column or table CONSTRAINT

---

If the column was already created, and you only want to add a (named) DEFAULT constraint, then use:

ALTER TABLE dbo.TableName
    ADD CONSTRAINT DF_Foo_Bar DEFAULT 'bicycle' FOR FieldName;

To have the system generate the DEFAULT constraint name (which will be of the form DF_{TableName}_{Column}_{8RandomChars}, e.g. DF_TableName_FieldName_12345678) then omit the CONSTRAINT <name> part, like so:

ALTER TABLE dbo.TableName
    ADD DEFAULT 'bicycle' FOR FieldName;

What is the standard way to add DEFAULT constraint using ALTER TABLE query?

Either works.

If you use MODIFY COLUMN, you must remember to include the column data type and other options that might be present on that column such as NOT NULL, AUTO_INCREMENT, or ZEROFILL. If you forget one of these options, it is removed from the column definition.

This can have unintended side effects. For example, changing the default of a column is a metadata-only change, so it is instant even if the table is huge. But changing a column's nullability to or from NOT NULL requires a table restructure. So if you accidentally change the nullability of the column by leaving that option out, you find yourself waiting for hours when you didn't have to.

If you use ALTER COLUMN ... SET DEFAULT, you don't have to spell out all those column options. They are left unaltered. This is more convenient and less error-prone if you only want to change the default value.

As for which one is standard, the ALTER COLUMN .. SET DEFAULT is in the ANSI/ISO SQL specification. MODIFY COLUMN is a MySQL extension to the standard for the sake of Oracle compatibility.

How to set a default value for an existing column

This will work in SQL Server:

ALTER TABLE Employee ADD CONSTRAINT DF_SomeName DEFAULT N'SANDNES' FOR CityBorn;

Adding Default Constraints to table in PostgreSQL

There is no such thing as a "default constraint". You simply define default values.

alter table alerts alter column bisactive set default 1;

---

Unrelated, but:
bisactive sounds like that is some kind of flag. You should define that as a proper boolean column, not an integer.

Can I create a named default constraint in an add column statement in SQL Server?

This should work:

ALTER TABLE t_tableName 
    ADD newColumn VARCHAR(50)
    CONSTRAINT YourContraintName DEFAULT '' NOT NULL

Alter column, add default constraint

Try this

alter table TableName 
 add constraint df_ConstraintNAme 
 default getutcdate() for [Date]

example

create table bla (id int)

alter table bla add constraint dt_bla default 1 for id

insert bla default values

select * from bla

also make sure you name the default constraint..it will be a pain in the neck to drop it later because it will have one of those crazy system generated names...see also How To Name Default Constraints And How To Drop Default Constraint Without A Name In SQL Server

Add a column with a default value to an existing table in SQL Server

Syntax:

ALTER TABLE {TABLENAME} 
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} 
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
WITH VALUES

Example:

ALTER TABLE SomeTable
        ADD SomeCol Bit NULL --Or NOT NULL.
 CONSTRAINT D_SomeTable_SomeCol --When Omitted a Default-Constraint Name is autogenerated.
    DEFAULT (0)--Optional Default-Constraint.
WITH VALUES --Add if Column is Nullable and you want the Default Value for Existing Records.

Notes:

Optional Constraint Name:

If you leave out CONSTRAINT D_SomeTable_SomeCol then SQL Server will autogenerate

    a Default-Contraint with a funny Name like: DF__SomeTa__SomeC__4FB7FEF6

Optional With-Values Statement:

The WITH VALUES is only needed when your Column is Nullable

    and you want the Default Value used for Existing Records.

If your Column is NOT NULL, then it will automatically use the Default Value

    for all Existing Records, whether you specify WITH VALUES or not.

How Inserts work with a Default-Constraint:

If you insert a Record into SomeTable and do not Specify SomeCol's value, then it will Default to 0.

If you insert a Record and Specify SomeCol's value as NULL (and your column allows nulls),

    then the Default-Constraint will not be used and NULL will be inserted as the Value.

Notes were based on everyone's great feedback below.

Special Thanks to:

    @Yatrix, @WalterStabosz, @YahooSerious, and @StackMan for their Comments.

How to add default constraint with condition in Sql Server?

This is what you need if you need that to check only the record with BookId = 1

CHECK (BookId = 1 AND RecordPath = 'path/to/1') OR (BookId != 1)

Esentialy this says: If BookId equals to 1, then the path has to be the one specified, or the BookId can be anything else and for those records the record path can be anything.

RecordPath = RecordPath is not necessary for the second part, since it is always TRUE

Or do you want a computed default?

That is not possible, DEFAULT constraint cannot depend on other columns. To achieve something similar, you'll need a TRIGGER or you can use a computed column or a view instead and use that column/view in your application when reading data.

altering DEFAULT constraint on column SQL Server

When you add a default, you should use names for your constraints. This way you can later refer to those constraints by name.

ALTER TABLE [dbo].[PMIPatients] ADD CONSTRAINT [PatientFallRiskLevel_Default] DEFAULT ((0)) FOR PatientFallRiskLevel

Then you can drop it using:

ALTER TABLE [dbo].[PMIPatients] DROP CONSTRAINT [PatientFallRiskLevel_Default] 

---

Related Topics