Default constraint is not working

  • I have table which has defalut constraint on the field called POST_PD. whenever I trying to insert rows in the table getting an error.

    Looks like default constraint is not firing. default constraint means that if there is a null value then get whatever value is in the default constraint.

    Insert is failing. I did not figure out the issue.

  • Can you post the table definition and a sample insert statement?

  • Here is the insert statement

    INSERT INTO TRANSAC_ARCHIVE

    SELECT top 1 * FROM TEMP_TRANSAC where post_pd is null

    Here is the table definition.

    CREATE TABLE [dbo].[TRANSAC_ARCHIVE](

    [GROUP_ZZ] [int] NOT NULL,

    [PATIENT] [numeric](12, 0) NOT NULL,

    [INVOICE] [numeric](12, 0) NOT NULL,

    [TRANSACTION_ZZ] [numeric](12, 0) NOT NULL,

    [I_INV_NUM] [numeric](20, 0) NOT NULL,

    [ADJ_AMT] [money] NULL,

    [ALLOCATION_AMT] [money] NULL,

    [ALLOWED_AMT] [money] NULL,

    [BANK_DEP_DT] [datetime] NULL,

    [BATCH_NUM] [int] NULL,

    [BATCH_TX_NUM] [int] NULL,

    [COMMENT] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [CR_AMT] [money] NULL,

    [DEBIT_AMT] [money] NULL,

    [DX_NUM] [varchar](75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [FEE_SCHED] [int] NULL,

    [FRONT_DESK_PAYCODE] [int] NULL,

    [FR_END_ADJ_AMT] [money] NULL,

    [FR_END_ADJ_PAY_CODE] [varchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [FSC] [int] NULL,

    [HMO_ALLOWED_AMT_ADJ] [money] NULL,

    [HMO_ALLOW_AMT_ADJ_PAYCODE] [int] NULL,

    [HMO_COV_CAT] [int] NULL,

    [HMO_FUND] [int] NULL,

    [HMO_NON_COV_AMT] [money] NULL,

    [INS_COMP] [int] NULL,

    [INS_COMP_NM] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [MCA_AMT] [money] NULL,

    [MCA_APPROVED_AMT] [money] NULL,

    [MCA_COPAY_AMT] [money] NULL,

    [MCA_DAYS] [numeric](12, 3) NULL,

    [MCA_DED_AMT] [money] NULL,

    [MCA_DISPOSITION_CODE] [int] NULL,

    [MCA_FUND_CODE] [int] NULL,

    [MCA_PENDING_AMT] [money] NULL,

    [MCA_REJECTED_AMT] [money] NULL,

    [MCA_UNITS] [numeric](12, 3) NULL,

    [MCD_PROFILE_FEE] [money] NULL,

    [MC_PROFILE_FEE] [money] NULL,

    [NATIONAL_DRUG_CODE] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [OVR_FLAG] [int] NULL,

    [PAY_AMT] [money] NULL,

    [PAY_CODE] [int] NULL,

    [PAY_CODE_NUM] [int] NULL,

    [PIECE_12] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [MOD] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [POST_DT_1] [datetime] NULL,

    [POST_FIELD] [datetime] NULL,

    [POST_PD] [int] NOT NULL CONSTRAINT [DF_TRANSAC_ARCHIVE_POST_PD] DEFAULT ((10001)),

    [PREV_FSC] [int] NULL,

    [PROC_ZZ] [int] NULL,

    [PVENDOR] [int] NULL,

    [PX_CODE] [int] NULL,

    [PX_DESC_OVR] [varchar](225) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [REJ_1] [int] NULL,

    [REJ_2] [int] NULL,

    [REJ_3] [int] NULL,

    [REJ_4] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [REJ_DE_1] [int] NULL,

    [RVU] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [RVU_EXPENSE_COMP] [numeric](12, 6) NULL,

    [RVU_MALPRATICE_COMP] [numeric](12, 6) NULL,

    [RVU_WORK_COMP] [numeric](12, 6) NULL,

    [SER_DT] [datetime] NULL,

    [STATISTICAL] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [STMT_RUN_NUM] [int] NULL,

    [UNITS] [numeric](12, 3) NULL,

    [UNITS_ADDITIONAL] [numeric](12, 3) NULL,

    [UNITS_BASE] [numeric](12, 3) NULL,

    [UNITS_DURATION] [numeric](12, 3) NULL,

    [UNITS_TIME] [numeric](12, 3) NULL,

    [UNITS_TOT] [numeric](12, 3) NULL,

    [U_ACTUAL_TIME] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [U_IF_ACCESSION_NUM] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [U_INS_SRV_CAT] [int] NULL,

    [U_MED_COMMENT] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [U_MOD_1] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [U_MOD_2] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [U_MOD_3] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [U_REF_CERT_ID] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [U_START_TIME_INT] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [U_STAT_FLAG] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [VENDOR_FROM_DICT] [int] NULL,

    [CHARGE_AMOUNT] [money] NULL,

    [ZGW_REJ_1] [int] NULL,

    [ZGW_REJ_2] [int] NULL,

    [ZGW_REJ_3] [int] NULL,

    [ZGW_REJ_4] [int] NULL,

    [ZGW_TES_BATCH_NUM] [numeric](12, 0) NULL,

    [ZGW_TES_ENC_NUM] [numeric](12, 0) NULL,

    [ZGW_TES_TXN_CREAT_DT] [datetime] NULL,

    [ZGW_TES_TXN_CREAT_INI] [int] NULL,

    [ZGW_TES_TXN_NUM] [numeric](12, 0) NULL,

    [DATESTAMP] [datetime] NULL DEFAULT (getdate()),

    CONSTRAINT [pk_TRANSAC_ARCHIVE] PRIMARY KEY CLUSTERED

    (

    [GROUP_ZZ] ASC,

    [PATIENT] ASC,

    [INVOICE] ASC,

    [TRANSACTION_ZZ] ASC,

    [POST_PD] ASC

    )WITH (IGNORE_DUP_KEY = OFF) ON [TRNAPScheme]([POST_PD])

    ) ON [TRNAPScheme]([POST_PD])

    GO

    SET ANSI_PADDING OFF

    GO

    USE [IDX]

    GO

    ALTER TABLE [dbo].[TRANSAC_ARCHIVE] WITH CHECK ADD CONSTRAINT [Transac_Archive_Range_Post_PD_CK] CHECK (([POST_PD]<'10304'))

  • You are explicitly inserting a null value into that column (you are selecting rows where post_pd is null); the default will only apply if you don't insert explicitly to that column.

    Run this example:

    create table tab1 (id int, value int not null default 1);

    insert into tab1 (id) values (1);

    select * from tab1;

    insert into tab1 (id, value) values (2, null);

    The first insert works as the value column was not specified, the second fails.

  • In the first statement, you are inserting value that is why it's not failing.

    I think the default means that if there is null value than store whatever value in the default constraint.

    I did modify the constrint to allow null but it's still failing. Actually, there are lots of null values in the table and I wanted to insert with a default value. that is wy I created defalut constraint.

  • I tried your example and modified the constraint into Allow Null values and insert the rows and it did converted into default value.

    but the problem is I cannot modify the POST_PD field into Allow Null value because it is a Primary key of the table.

  • I think I figured out the issue. It's a design issue at our part. Data gets loaded into temp table first and then from temp table to real table.

    I think we shoud not have constraint on temp table. I will modify the constraint on temp table and it should be fine.

    Thanks a lot for your help.

  • balbirsinghsodhi (4/16/2009)


    I think the default means that if there is null value than store whatever value in the default constraint.

    This is incorrect. As Matt already stated, a Default value is only applied when the column is NOT included in the insert. NULL is a value. What Matt's example demonstrated is that when you explicitly supply a NULL value to a column with a default, the default is not applied.

  • Yes, You are correct Jack.

    Thanks a lot.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply