Adding a FK for column that have NULL values gives a constra

Posted by Admin on 02-Dec-2007 06:11

Progress version: OpenEdge Progress 10.1B02

Adding a foreign key for column that have NULL values gives a constraint violation.

From other side its possible to set NULL value for already existing foreign key.

Example:

create table pub."Test1"
(
"Field1" number not null,
"Field2" varchar(100),
PRIMARY KEY ( "Field1" )
)

insert into pub."Test1" values (1, 'Value 1')
insert into pub."Test1" values (2, 'Value 2')
insert into pub."Test1" values (3, 'Value 3')
insert into pub."Test1" values (4, 'Value 4')

select * from pub."Test1"

create table pub."Test2"
(
"Field1" number not null,
"Field2" number,
"Field3" varchar(100),
PRIMARY KEY ( "Field1" ),
CONSTRAINT "Fk_Test2_Test1_Field2" FOREIGN KEY ( "Field2" ) REFERENCES pub."Test1" ( "Field1" )
)

insert into pub."Test2" values (1,1, 'Some Value 1')
insert into pub."Test2" values (2,1, 'Some Value 2')
insert into pub."Test2" values (3,2, 'Some Value 3')
insert into pub."Test2" values (4,2, 'Some Value 4')
insert into pub."Test2" values (5,3, 'Some Value 5')
insert into pub."Test2" values (6,3, 'Some Value 6')
insert into pub."Test2" values (7,null, 'Some Value 7')
insert into pub."Test2" values (8,null, 'Some Value 8')

select * from pub."Test2"

alter table pub."Test2"
drop CONSTRAINT "Fk_Test2_Test1_Field2"

alter table pub."Test2"
add CONSTRAINT "Fk_Test2_Test1_Field2"
FOREIGN KEY ( "Field2" ) REFERENCES pub."Test1" ( "Field1" )

The last sql statement gives following error: Constraint violation (7597)

How can I solve this problem?

All Replies

Posted by Admin on 06-Dec-2007 00:58

no ideas?

it's a bug in progess?

Posted by Tim Kuehn on 06-Dec-2007 08:00

no reason to respond to someone who won't put their name on their posts?

Posted by Admin on 06-Dec-2007 10:03

If I was call myself John Smith or some other name it was have a difference? Or it was changes the fact of not expected behavior in progress db?

Posted by jtownsen on 07-Dec-2007 14:06

Kinda looks like a bug to me. The only thing you can do now is talk to Tech Support.

- Jamie

This thread is closed