[jira] [Created] (TRAFODION-586) LP Bug: 1361784 - Foreign key constraint "not enforced" option

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

Alice Chen created TRAFODION-586:
------------------------------------

             Summary: LP Bug: 1361784 - Foreign key constraint "not enforced" option
                 Key: TRAFODION-586
                 URL: https://issues.apache.org/jira/browse/TRAFODION-586
             Project: Apache Trafodion
          Issue Type: Bug
          Components: sql-cmu
            Reporter: Chong Hsu
            Assignee: Anoop Sharma


Tested in Trafodion build 2040823_0830.

When “not enforced” option for foreign key constraint is specified, the constraint is still enforced.  Please see the steps below:


create table Female_actors (
f_no          int not null not droppable,
f_name        varchar(30) not null,
f_realname    varchar(50) default null,
f_birthday    date  constraint md1 check (f_birthday > date '1900-01-01'),
primary key (f_no));

Create table Movie_titles (
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,
mv_yearmade    int check (mv_yearmade > 1901),
mv_star_rating char(4),
mv_movietype   varchar(15),
primary key (mv_no),
    constraint fa_fk foreign key (mv_femalestar) 
           references female_actors not enforced
);


--> Note: a WARNING[1313] is expected:
*** WARNING[1313] The referential integrity constraint NEO.HSU.FA_FK has been created with the NOT ENFORCED attribute an
d will not be enforced during INSERT, UPDATE, OR DELETE statements. [2014-08-20 17:52:08] 


insert into Female_actors values (0, 'No female actor','No female actor', current_date),
                                (6111, 'Grace Kelly', 'Grace Patricia Kelly', date '1929-11-12'),
                                 (6123, 'Katherine Hepburn','Katharine Houghton Hepburn', date '1907-05-12'),
                                 (6124, 'Joan Crawford','Lucille Fay LeSueur', date '1904-03-23'),
                                 (6125, 'Ingrid Bergman', 'Ingrid Bergman', date '1915-08-29');

insert into Movie_titles values 
(1,'To Catch a Thief',1111, 6111, 1234, 1955, '****','Mystery'),
(2,'High Noon',1222, 6111, 1456, 1951, '****','Western'),
(3,'Unforgiven', 1333, 0, 1345, 1990, '***', 'Action'),
(4,'The Women', 0, 6124, 1567, 1939, '****', 'Drama'),
(5,'The Philadelphia Story',1111, 6123,1567, 1940, '****','Drama'),
(6,'Notorious', 1111, 6125, 1234, 1946, '****','Mystery');

update movie_titles set mv_femalestar = 27 where mv_no = 1;

*** ERROR[8103] The operation is prevented by referential integrity constraint TRAFODION.TESTSPJ.FA_FK on table TRAFODION.TESTSPJ.MOVIE_TITLES. [2014-08-19 22:37:27]

--> Note:  the constraint is not expected to be "enforced" here.



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