April 16, 2009 at 3:27 pm
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.
April 16, 2009 at 3:45 pm
Can you post the table definition and a sample insert statement?
April 16, 2009 at 3:51 pm
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'))
April 16, 2009 at 4:02 pm
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.
April 16, 2009 at 4:30 pm
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.
April 16, 2009 at 4:38 pm
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.
April 16, 2009 at 5:07 pm
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.
April 17, 2009 at 7:15 am
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.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 17, 2009 at 9:43 am
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