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
Dave,
I think something is wrong with your blog software. The new blog post today is labeled as the 19th, but you posted it today, 3/21. Just wanted you to be aware.
It means that he saved the post at 19th and publish it 21st only
hello sir,
i could not delete a column in a table through ‘ drop column’ option in ‘alter table’ query even though the column is not having any relationships or any other constraints.
please help me.
with regards,
adiraju
Did you get any error? Post the exact code you used
@adiraju
If that column you are trying to drop is used by any derived column, then you cannot drop it.
Example: in Example11 I created a table with out any derived column so I was able to drop column ename.
CREATE TABLE EXAMPLE11( EID INT , ENAME VARCHAR(10))
GO
ALTER TABLE EXAMPLE11
DROP COLUMN ENAME
GO
Result:Command(s) completed successfully.
Example2: In this example I created third column using first and second column, so when I try to drop second column, it gives me an error because second column in used in third column
CREATE TABLE EXAMPLE12 ( COLA INT, COLB INT , COLC AS COLA + COLB )
GO
ALTER TABLE EXAMPLE12
DROP COLUMN COLB
Go
Result: Msg 5074, Level 16, State 1, Line 1
The column ‘colc’ is dependent on column ‘COLB’.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE DROP COLUMN COLB failed because one or more objects access this column.
Hope this helps.
Thanks,
Imran.
Hi,
Can someone please tell me how to specify a default constraint for an existing column?
Thanks in advance!!
Treasa
ALTER TABLE EXAMPLE12
DROP COLUMN COLC
Dear Treasa!
ALTER TABLE TableName WITH NOCHECK
ADD CONSTRAINT DF_DefaultName DEFAULT ‘Default Value’ FOR ColumnName
Check it out !
Thanks. Your suggestion helps me.
Thanks it helps me……..
Thanks it helps me
Thanks a lot, it help me
I have an existing query which return 1000 of rows, i want to add another column and have constant numerical value (2.7 for example). can someone tell me how to do that? many thanks
até que enfim um exemplo simples e eficaz. boa=good!
Thanks for the solution. Works!
Hi Simon
An Example you can test on Adventure works
select Name, GroupName, 2.7 as Constant from HumanResources.Department
thanks
Hi
Thanks for this.
Hi
Thanks for this best tutorial.
Please can anybody tell me how i need to start working on
Application DBA. I mean from basics.
Thanks in advance.
Santhosh;
I suggest you download Microsofts free database software ‘Sql Server Express, and ‘SQL Server Manager Express’ and then search for ‘Beginning, SQL Server’ under Google videos. Microsofts got some good beginner videos as well. … I started with reading a few books on SQL, and use ‘SQL Authority’ as my key source for help on SQL Server queries etc.
good luck
Server Error in ‘/’ Application.
——————————————————————————–
Index (zero based) must be greater than or equal to zero and less than the size of the argument list.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.FormatException: Index (zero based) must be greater than or equal to zero and less than the size of the argument list.
Source Error:
Line 40: cmd.CommandType = System.Data.CommandType.Text;
Line 41: String s = String.Empty;
Line 42: s = String.Format(“insert into Customerdetails2 values(‘{0}’,'{1}’,{2},{3},{4},{5},'{6}’,'{7}’,'{8}’,'{9}’,'{11}’)”,
Line 43: TextBox1.Text, TextBox2.Text, DropDownList1.SelectedItem.ToString(), DropDownList2.SelectedItem.ToString(), TextBox5.Text, TextBox6.Text,
Line 44: TextBox7.Text, TextBox8.Text, TextBox9.Text, TextBox10.Text,TextBox11.Text);
Source File: E:\MURTHY PRO\cusdetails\cusdetails\Default.aspx.cs Line: 42
Stack Trace:
[FormatException: Index (zero based) must be greater than or equal to zero and less than the size of the argument list.]
System.Text.StringBuilder.AppendFormat(IFormatProvider provider, String format, Object[] args) +7485085
System.String.Format(IFormatProvider provider, String format, Object[] args) +107
System.String.Format(String format, Object[] args) +9
cusdetails._Default.Button1_Click(Object sender, EventArgs e) in E:\MURTHY PRO\cusdetails\cusdetails\Default.aspx.cs:42
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +111
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +110
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565
——————————————————————————–
Version Information: Microsoft .NET Framework Version:2.0.50727.4952; ASP.NET Version:2.0.50727.4927
how to store data in sqlsever2008 with default date and time without giving from frontend
Have a column that has a default value of getdate(). You dont need to pass values explicitely
can you give a example for this
use tempdb
go
Create table x (i int, j datetime default getdate())
go
Insert into x (i) values (1) — no value given and it would insert getdate automatically.
Insert into x (i,j) values (2,’14-Feb-2015′) — given the value which would be inserted.
go
Select * from x
hi ,
how to change null value to not null in a existing table.
thanks in advance..
First you need to update null values with any default values if there are data in the table. Then use alter command
alter table your_table
alter column your_column datatype
hi i wanna two existing tables to add in new database how to do this with schema and data????????
Script the objects and run them in another database. Use import/export wizard to export data
Hai …
i want sql script to add column in sql table with ordinal position specification.
is it possible. if Please help me ..
Do it via Management studio
thanks for this information. I was using alter table table_name add( First_Name varchar(20), Last_Name varchar(20)) … but it shows an syntax error.