[jira] [Created] (TRAFODION-365) LP Bug: 1328283 - missing error message 1046 when referencing column do not match constraint

["Alice Chen (JIRA)" <ji...@apache.org>]

Alice Chen created TRAFODION-365:
------------------------------------

             Summary: LP Bug: 1328283 - missing error message 1046 when referencing column do not match constraint
                 Key: TRAFODION-365
                 URL: https://issues.apache.org/jira/browse/TRAFODION-365
             Project: Apache Trafodion
          Issue Type: Bug
          Components: sql-exe
            Reporter: Apache Trafodion
            Assignee: Anoop Sharma
             Fix For: 0.8 (pre-incubation)


Various negative tests for referencing column lists do not match for constraint

SQL>-- N001.3 Create child table with different datatype

SQL>-- #expect any *ERROR[1046]*

SQL>-- ERROR[1046] Referenced and referencing column lists do not match for constraint D_FK3

SQL>Create table Movie_titles3 (
mv_no          int not null not droppable,
mv_name        varchar (40) not null,
mv_malestar    int default NULL constraint ma_fk3 references male_actors(m_no),
mv_femalestar  int default NULL,
mv_director    int default 0 not null,
--mv_yearmade    datetime year,
mv_yearmade    interval year,
mv_star_rating char(4),
mv_movietype   char(15),
primary key (mv_no),
constraint fa_fk3 foreign key (mv_femalestar) references female_actors,
constraint d_fk3 foreign key (mv_director, mv_movietype) references directors (d_no, "d_specialty")
);

--- SQL operation complete.

SQL>drop table Movie_titles3 cascade;

--- SQL operation complete.

SQL>-- N001.4 Create child table with different data length

SQL>-- #expect any *ERROR[1046]*

SQL>-- ERROR[1046] Referenced and referencing column lists do not match for constraint D_FK4

SQL>Create table Movie_titles4 (
mv_no          int not null not droppable,
mv_name        varchar (40) not null,
mv_malestar    int default NULL constraint ma_fk4 references male_actors(m_no),
mv_femalestar  int default NULL,
mv_director    int default 0 not null,
mv_yearmade    interval year,
mv_star_rating char(4),
mv_movietype   varchar(12),
primary key (mv_no),
constraint fa_fk4 foreign key (mv_femalestar) references female_actors,
constraint d_fk4 foreign key (mv_director, mv_movietype) references directors (d_no, "d_specialty")
);

--- SQL operation complete.

SQL>drop table Movie_titles4 cascade;

--- SQL operation complete.

SQL>--N001.7 Order of keys in constraint much match

SQL>-- #expect any *ERROR[1046]*

SQL>-- ERROR[1046] Referenced and referencing column lists do not match for constraint D_FK7

SQL>Create table Movie_titles7 (
mv_no          int not null not droppable,
mv_name        varchar (40) not null,
mv_malestar    int default NULL,
mv_femalestar  int default NULL,
mv_director    int default 0 not null unique,
mv_yearmade    interval year,
mv_star_rating char(4),
mv_movietype   varchar(12),
primary key (mv_no),
constraint fa_fk7 foreign key (mv_femalestar) 
                  references female_actors,
constraint d_fk7 foreign key (mv_movietype,mv_director) 
                 references directors (d_no, "d_specialty")
);

--- SQL operation complete.

SQL>drop table Movie_titles7 cascade;

--- SQL operation complete.

SQL>--(precision,scale)

SQL>-- #expect any *ERROR[1046]*

SQL>-- ERROR[1046] Referenced and referencing column lists do not match for constraint

SQL>create table n002_t10 (
a int,
b numeric (5,3) unique not null not droppable,
c decimal (6,2) unique not null not droppable,
Primary key (b,c));

--- SQL operation complete.

SQL>--N002.1 create child table with different scale

SQL>-- #expect any *ERROR[1046]*

SQL>-- ERROR[1046] Referenced and referencing column lists do not match for constraint 

SQL>create table n002_t11 ( 
aa int,
bb numeric (5,2) unique not null not droppable,
cc decimal (7,2) unique not null not droppable,
Primary key (bb,cc),
constraint t2_c1 foreign key (bb,cc)
references n002_t10(b,c) );

--- SQL operation complete.

SQL>log off;



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)