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

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 0.8 (pre-incubation)
• Component/s: sql-exe
• External issue URL: https://bugs.launchpad.net/bugs/1328283
• Assignee: Anoop Sharma
• Reporter: Apache Trafodion
• Created: 22/Jul/15 18:15
• Updated: 31/Aug/15 05:16
• Resolved: 31/Aug/15 05:16

Description

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;