Just a day ago while working with database Jr. A developer asked me question how to add a column along with a column constraint. He also wanted to specify the name of the constraint. The newly added column should not allow NULL value. He requested my help as he thought he might have to write many lines to achieve what was requested.
It is very easy to add columns and specify default constraints. I have seen many examples where the constraint name is not specified, if constraint name is not specified SQL Server will generate a unique name for itself. I prefer to specify my constraint name as per my coding standards. You can read my coding standard here: SQL SERVER Database Coding Standards and Guidelines Complete List Download
ALTER TABLE TestTable ADD NewCol VARCHAR(50) CONSTRAINT DF_TestTable_NewCol DEFAULT '' NOT NULL GO
Here are few of the related blog posts on this subject.
- How to Disable and Enable All Constraint for Table and Database
- How to Disable and Enable All Constraint for Table and Database
- Create Unique Constraint on Table Column on Existing Table
Please leave a comment about what you think about this blog post about column constraint.
Reference : Pinal Dave (https://blog.sqlauthority.com)
69 Comments. Leave new
The information was quite helpful.Thank you
I have a table with 5 columns and i need to insert my new column after the second column.How do i achieve this.
The ordinal position of the column doesnt matter as long as it is used in the specific order in the select statement. If yo still need it, do it via Management studio
May i know how to add default value to existing table column?
It is not possible. You need to drop the column and add new column with default option
Hi,
I’m using SQL Server 2008. I’m dynamically creating a Table with default value of ‘NP’ for a some columns. that columns may have “Null” string value, so I would like to set the default value(“NP”) for “Null” string while creating a Table. Please help me on this. If this not possible,please let me know.
Thanks
Alexis
How will modify data while creating a table? Have it as default value and remove that column from the insert statement
What will be the performance impact on inserts if we have default columns for a table
I dont think default values will affect performance
This constraint doesnot add the default value in existing rows in the table??…
ALTER TABLE dbo.utl_site ADD AMI_active CHAR(1)
CONSTRAINT DF_utl_site_AMI_active DEFAULT ‘N’ NULL
all of the rows of utl_site still remains NULL not ‘N’
Can u explain please?
Note that it will affect newly inserted data not the old data. You need to write update statement to update old data
How to add new cloumn to table to hold archival status flag
Alter table tablename
add newcolumn datatype
Hello… can i change default to another type? and drop the default?
please help me..
thanks for advance
Ade Ruyani
Hello Sir,
I have a requirement where in I want to add a Column which can calculate values by its own.
What I want to do is I want to add a expression to that column.
I could do that in visual studio using Datatable.columns(index).expression. but it consumes lot of time. please help.
Here’s a small scenario
CREATE TABLE #Temp (RowNo int identity(1,1),Quantity Decimal(18,5) ,ID int, StudentID int)
Insert into #Temp
Select Quantity,ID, StudentID From Student Where ID = 53
Select * from #Temp
Alter Table #Temp Add Calc Decimal(18,5) Default (‘[Quantity] * [ID] * 100’)
Select * from #Temp
Drop Table #Temp
How many rows are there in the database? Also why are you doing it via Visula Studio? Have you tried doing the same in Query Analyser?
Thanks for your valuable time and quick reply.
There are more than 13000 rows for my condition.
Since I already had computed columns in the table and I want to use the same columns for my new Computed column, I put it in code.
But because of the performance issue I need to do that in the back end itself.
i want to add a coloumn name in my table name member aster but it allow only null value otherwise it say it’s not possible try with alter table plz help me
the given error by sql 2005 is :-
‘MemberMaster’ table
– Unable to modify table.
ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column ‘MemberLogin’ cannot be added to non-empty table ‘MemberMaster’ because it does not satisfy these conditions.
help help help
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword ‘set’.
hi everyone
can you help me how to set grid column
my gode is here
Dim sql As String = “select code,Name,curr as [Currency],LOCShtNM as [Short Name] from cost_center order By code”
Dim cmd As New SqlCommand(sql, conn)
dset.Clear()
Dim adapt As New SqlDataAdapter
adapt.SelectCommand = cmd
adapt.Fill(dset, “cost_center”)
DataGridView1.DataSource = dset
DataGridView1.DataMember = “cost_center”
conn.Close()
DataGridView1.Columns(2).Width = 280
Hi Dave,
I have a query where I need to create a column say Status.
It is a Not-Null column. The table I m gonna add this column, already have some data in it.
So, wen creating the column I gave a Default value as 1.
Now after addition of this column, in the same script file, I dropped the default constraint. It throws like ‘Status’ column not found.
Please help me out
Hi How to add new column for existing table after first column in SQL 2008
I want to set a column with a default value = value of primary key column. How can I do this? I cannot set col2 value as computed column because I may want to override its value later but by default it should be = col1 value of the same table which is a primary key column. Your help will be appreciated.
Thanks!
how to display the recently entered record in table ?
in my table do not have any date time column .pls send querry sir
–To add a column to display date and time
ALTER TABLE [tablename]
ADD [newcol] DATETIME not null default getdate()
–To display the most recent insert
SELECT * FROM [tablename]
WHERE [newcol] =
(
SELECT MAX([newcol])
FROM [tablename]
)
create table od
(
id int not null,
name char(10),
place char(30) DEFAULT ‘New_Delhi’,
dat dateTIME DEFAULT GETDATE()
)
insert into od values(83,’AMIT’,”,”)
select * from od
but
it show’s result
id name place dat
———– ———- —————————— ———————–
83 kk 1900-01-01 00:00:00.000
wrong Date and place is Empty, please solve my problem
If you enter ” for place and date, this means you are inserting blanks, therefore it will not take default values.
Use the following query:
insert into od (id, name) values (83,’AMIT’)
id name place dat
83 AMIT New_Delhi 2013-03-04 09:54:52.047
thanks
also done where not null is added in the place and dat column
it is very helpful to me for my table generation.