[TRAFODION-714] LP Bug: 1384485 - Alter table allows adding a constraint to a _MD_ table

• Type: Bug
• Status: Closed
• Priority: Critical
• Resolution: Fixed
• Component/s: sql-cmu
• External issue URL: https://bugs.launchpad.net/bugs/1384485
• Assignee: Roberta Marton
• Reporter: Weishiun Tsai
• Created: 22/Jul/15 18:17
• Updated: 21/Aug/15 17:42
• Resolved: 21/Aug/15 17:42

Description

Alter table does not return an error when the user tries to add a constraint to a MD table, although it’s hard to tell if the constraint is actually added or the statement simply aborts internally without returning an error to the user.

This is seen on the v1021_0830 build installed on a workstation.

-------------------------------------------------------------------------
Here is the entire script to reproduce this problem

showddl "MD".views_usage;
alter table "MD".views_usage add constraint c1 check (used_object_uid != 0);
showddl "MD".views_usage;

-------------------------------------------------------------------------
Here is the execution output:

>>showddl "MD".views_usage;

CREATE TABLE TRAFODION."MD".VIEWS_USAGE
(
USING_VIEW_UID LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
, USED_OBJECT_UID LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
, USED_OBJECT_TYPE CHAR(2) CHARACTER SET ISO88591 COLLATE
DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
, PRIMARY KEY (USING_VIEW_UID ASC, USED_OBJECT_UID ASC)
)
;

— SQL operation complete.
>>alter table "MD".views_usage add constraint c1 check (used_object_uid != 0);

— SQL operation complete.
>>showddl "MD".views_usage;

CREATE TABLE TRAFODION."MD".VIEWS_USAGE
(
USING_VIEW_UID LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
, USED_OBJECT_UID LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
, USED_OBJECT_TYPE CHAR(2) CHARACTER SET ISO88591 COLLATE
DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
, PRIMARY KEY (USING_VIEW_UID ASC, USED_OBJECT_UID ASC)
)
;

— SQL operation complete.