Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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 23:24pm
Rate this: bad
good
Please Sign up or sign in to vote.

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
  Permalink  
Comments
engr.hinaiqbal at 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:(
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

You need to create Default Constraint for that column (assuming statusid for pending is 1):
ALTER TABLE dbo.users ADD CONSTRAINT
DF_users_statusid DEFAULT 1 FOR statusid
  Permalink  
Comments
engr.hinaiqbal at 23-Feb-13 1:04am
   
thanks
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

HI,
 
You can try like this:
 
ALTER TABLE {TABLENAME}
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL}
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
 
Add Default Value to Existing Column

-- 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
 
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
  Permalink  

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



Advertise | Privacy | Mobile
Web04 | 2.8.1411022.1 | Last Updated 22 Feb 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100