Add foreign column with default value-CodePudding

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.

CodePudding user response：

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.