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 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy