locked
set default value to bit column RRS feed

  • Question

  • User81789783 posted

    i have column is active in database table which i  just set null able=no ,now i want to set its default value to 1 by a SQL query  ,please not down is active column data type is bit ,

    how to set default value to 1  .i have multiple tables which have isactive column

    Thursday, November 7, 2019 9:45 AM

All replies

  • User77042963 posted

    For table test, here is an sample:

    Alter table test
       ADD CONSTRAINT isactive_default  
        DEFAULT 1 FOR isactive; 

    Thursday, November 7, 2019 7:14 PM
  • User665608656 posted

    Hi erum,

    According to your description, If your table has been created, as the case provided by @limnodex, you can use the alter syntax to implement it.

    ALTER TABLE YouTableName
    ADD CONSTRAINT df_isactive 
    DEFAULT 1 FOR isactive

    If you set the default value of the column when you create the table, you can follow this code:

    CREATE TABLE Persons (
        ID int NOT NULL,
        LastName varchar(255) NOT NULL,
        FirstName varchar(255), 
        isactive bit DEFAULT 1
    );

    You can also refer to this link:  SQL DEFAULT Constraint

    Best Regards,

    YongQing.

    Friday, November 8, 2019 1:53 AM
  • User81789783 posted

    thanks limno for reply , One more extra help /step required

    let say , I have column  isactive(bit type) and allow null is true   in database which is using currently in the system  .i..e existing column

    Now i want to make sure that it should be allow null to false and put 1 as default value  ,that means set all values already  in the system to 1 and also for upcoming records ,

    Can help me

    Please not down i need to do it via query

    Friday, November 8, 2019 2:00 AM
  • User665608656 posted

    Hi erum,

    According to your description, you need to step through to implement your requirements.

    Because the isactive field of your table already has null data, so if you want to update the field isactive property to not allow null, you need to update the null value in isactive field to the value 1 firstly.

    You can also refer to this link : How to Alter a Column from Null to Not Null in SQL Server

    The following are the detailed steps, you can refer to:

    1. First update the value of the isactive column in your table to 1.

    UPDATE
      TableName
    SET
      isactive = 1
    WHERE
      isactive IS NULL;

    2. Modify the isactive field attribute of the table to not allow null.

    ALTER TABLE TableName ALTER COLUMN isactive bit NOT NULL;

    3.Set the default attribute value to 1. in the isactive field of the table.

    ALTER table TableName 
       ADD CONSTRAINT  df_isactive   
        DEFAULT 1 FOR isactive;

    Best Regards,

    YongQing.

    Friday, November 8, 2019 7:20 AM
  • User81789783 posted

    can we update multiple tables at same time??

    Friday, November 8, 2019 7:33 AM
  • User665608656 posted

    Hi erum,

    can we update multiple tables at same time??

    No, because different tables have different table names, you need to write the corresponding statement to operate the corresponding data table.

    For more details, you can refer to this link :

    How to update two tables in one statement in SQL Server 2005?

    Best Regards,

    YongQing.

    Friday, November 8, 2019 9:12 AM