How to add a new column in the table if the table has a constraint on it-CodePudding

I need to add a new column with these details.

ADD [Revenue] decimal(10,6) NOT NULL

Currently, my SQL script is like this.

CREATE TABLE [dbo].[FlightTotal]
(
    [FlightID] [int] NOT NULL,
    [CreatedUserID] [bigint] NOT NULL,
    [CreatedUTC] [datetime2](3) NOT NULL,
    [ManualT] [smallint] NULL

    CONSTRAINT [FlightTotal_PK] 
        PRIMARY KEY CLUSTERED ([FlightID] ASC)
                WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                      ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [UD01]
) ON [UD01]
GO

ALTER TABLE [dbo].[FlightTotal] 
    ADD CONSTRAINT [DF_FlightTotal_CreatedUTC]  
        DEFAULT (sysutcdatetime()) FOR [CreatedUTC]
GO

Do you have any idea how to alter the current table so that I can insert the new column?

CodePudding user response：

ALTER TABLE [dbo].[FlightTotal] 
ADD [Revenue] decimal(10,6) NOT NULL
CONSTRAINT [DF_Revenue] DEFAULT 0

ALTER TABLE [dbo].[FlightTotal] 
DROP CONSTRAINT [DF_Revenue]

CodePudding user response：

To add a column to table

ALTER TABLE [FlightTotal] ADD [Revenue] decimal(10,6) NOT NULL

If the table already have some rows, you can't add a NOT NULL column. You need to specify a DEFAULT value

example :

ALTER TABLE [FlightTotal] ADD [Revenue] decimal(10,6) NOT NULL DEFAULT 0

add as a nullable column,

ALTER TABLE [FlightTotal] ADD [Revenue] decimal(10,6)

update the table and set the value accordingly

UPDATE [FlightTotal]
SET    [Revenue]   = {some value}
{WHERE  condition if any}

then change it to NOT NULL

ALTER TABLE [FlightTotal] ALTER COLUMN [Revenue] decimal(10,6) NOT NULL