Add foreign column with default value

February 1, 2023

I need to add a foreign key column in an existing table using alter statement. And need to set default value to insert default in case of null. For that I added below alter query:

ALTER TABLE COM.USER_GENERATED_TASK_LIST
ADD Application_ID INTEGER NOT NULL
CONSTRAINT DK_Application_ID DEFAULT (5) WITH VALUES,
CONSTRAINT FK_Application_ID FOREIGN KEY (Application_ID)
REFERENCES ADMIN.APPLICATION (Application_ID)

But with these, if I excluded value of new column in the insert statement, I will get this error message:

Column name or number of supplied values does not match table definition.

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.
Visit Medevel

>Solution :

To resolve this issue, you can set the default value for the newly added column in the ALTER statement itself:

ALTER TABLE COM.USER_GENERATED_TASK_LIST
ADD Application_ID INTEGER NOT NULL DEFAULT 5,
CONSTRAINT FK_Application_ID FOREIGN KEY (Application_ID)
REFERENCES ADMIN.APPLICATION (Application_ID)

This way, if the value is not specified in the INSERT statement, the default value (5 in this case) will be used.