In SQL Server, you can create a column with a default value using the DEFAULT constraint when you create the table or when you modify an existing table.
1. Creating a New Table with a Default Value
When you create a new table, you can specify a default value for a column like this:
CREATE TABLE YourTableName (
ID INT PRIMARY KEY,
Name NVARCHAR(50),
CreatedDate DATETIME DEFAULT GETDATE() -- Sets the default value to the current date and time
);
In this example, the CreatedDate column will automatically have the current date and time as its default value when a new row is inserted without explicitly specifying a value for that column.
2. Adding a Column with a Default Value to an Existing Table
If you want to add a new column with a default value to an existing table, you can use the ALTER TABLE statement like this:
ALTER TABLE YourTableName
ADD NewColumn INT DEFAULT 0; -- Sets the default value to 0
In this case, NewColumn will be added to YourTableName, and any new rows inserted into the table without specifying a value for NewColumn will have the default value of 0.
3. Altering an Existing Column to Add a Default Value
To add a default value to an existing column, you would do it like this:
ALTER TABLE YourTableName
ADD CONSTRAINT DF_ColumnName DEFAULT 'DefaultValue' FOR ColumnName;
Replace ColumnName with the name of the column you're modifying and 'DefaultValue' with the desired default value. For example:
ALTER TABLE YourTableName
ADD CONSTRAINT DF_CreatedBy DEFAULT 'System' FOR CreatedBy;
This would set the default value of the CreatedBy column to 'System' for any new entries that do not specify a value for that column.
Additional Notes
- Ensure the default value matches the data type of the column.
- If you drop a default constraint, the default value will no longer be applied to the column for future inserts.
Feel free to replace the example names and values with those relevant to your database schema!