How to set the dafult value of a column in sql

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

i have a tables 'users' in which all user information is listed and a column named status whose data type is int.the other table is status in which only two columns i.e. statusid and statusname, the statusid is autoincremented and is referenced in the users table as foreign key. the statusname contains values as pending, approved etc.

Now i have to set the default value of status in users table depending on vlaue of Status table status id as pending as default value in user table

please help me

Posted 21-Feb-13 22:24pm

hinaiqbal

Add a Solution

3 solutions

Solution 1

Hi
Goto Design view of the particular table (DATABASE-->Table-->Design) then look Default Valur or Binding in Column Properties. Here you can specify the default value.

Regards
Willington

Posted 21-Feb-13 22:51pm

willington.d

Comments

hinaiqbal 23-Feb-13 1:00am

you don't understand my point.... through using this it will set the default value for column but my default value comes from the other table that is stored there as default value.. is it possible to write the select query in the default value or binding because i tried but its not working:(

Solution 3

HI,

You can try like this:

SQL

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

Add Default Value to Existing Column

SQL

-- Add default to existing column DateOfHire:
ALTER TABLE [dbo].[Employees] ADD  DEFAULT (getdate()) FOR [DateOfHire]

-- Add default value to existing column IsTerminated
ALTER TABLE [dbo].[Employees] ADD  DEFAULT ((0)) FOR [IsTerminated]

Add Default Value with Create Table

SQL

CREATE TABLE [dbo].[Employees]
(
    [EmployeeID] [INT] IDENTITY(1,1) NOT NULL,
    [FirstName] [VARCHAR](50) NULL,
    [LastName] [VARCHAR](50) NULL,
    [SSN] [VARCHAR](9) NULL,
    -- Add default of zero
    [IsTerminated] [bit] NOT NULL DEFAULT ((0)) ,
    -- Add default of getdate()
    [DateAdded] [datetime] NULL DEFAULT (getdate()),
    [Comments] [VARCHAR](255) NULL,
    [DateOfHire] [datetime] NULL
)

Hope this will give you some idea...

Thanks

Posted 21-Feb-13 23:02pm

Sisir Patro

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Matej Hlatky · Accepted Answer · 2013-02-21T23:00:00

Solution 2

You need to create Default Constraint for that column (assuming statusid for pending is 1):

SQL

ALTER TABLE dbo.users ADD CONSTRAINT
DF_users_statusid DEFAULT 1 FOR statusid

Posted 21-Feb-13 23:00pm

Matej Hlatky

Comments

hinaiqbal 23-Feb-13 1:04am

thanks