This seems to happen in 9.0.2 and 11.0.1 :

Foreign key defined to allow nulls - shows correctly in Sybase Central.

Unload the structure - all seems OK:

ALTER TABLE "pears"."TTSCancellation"
ADD FOREIGN KEY "TTS-Contra" ("ContraTTSID")
REFERENCES "pears"."TTS" ("TTSID") on update set null on delete set null
go

but if you cut and paste the object into a text editor you get:


ALTER TABLE "pears"."TTSCancellation" ADD CONSTRAINT "TTS-Contra" NOT
NULL FOREIGN KEY ( "ContraTTTSID" ) REFERENCES "pears"."TTS" ( "TTSID" )
ON UPDATE SET NULL ON DELETE SET NULL;

an incorrect NOT NULL condition has been introduced.

Does anyone else see this?

Thanks

Justin Willey

I don't see that on my installation

[with version 11.0.1#2252 plug-ins, on XP with the
6.0.0.4745 viewer and the JVM installed with that,
running against the server matched to the plug-in
version and the V11 demo database.]

For example when I Ctrl-C copy the Fkey properties
and paste that into textpad I get this

ALTER TABLE "GROUPO"."FinancialData" ADD CONSTRAINT "FK_Code_Code" NOT
NULL FOREIGN KEY ( "Code" ASC ) REFERENCES "GROUPO"."FinancialCodes" (
"Code" ) ON DELETE CASCADE;


"Justin Willey" <gjw (AT) nospam (DOT) iqx.co.uk> wrote

A more complete response this time ....


I don't see that on my installation

[with version 11.0.1#2252 plug-ins, on XP with the
6.0.0.4745 viewer and the JVM installed with that,
running against the server matched to the plug-in
version and the V11 demo database.]

For example when I Ctrl-C copy the Fkey properties
and paste that into textpad I get this

ALTER TABLE "GROUPO"."FinancialData"
ADD CONSTRAINT "FK_Code_Code" NOT
NULL FOREIGN KEY ( "Code" ASC )
REFERENCES "GROUPO"."FinancialCodes"
( "Code" ) ON DELETE CASCADE;


If you are still running 11.0.1 GA there have been a
couple of Clipboard fixes added since then. I didn't
fine one explicitly for this behaviour, but it seems not
to be a problem in my limited testing sooo ... try it
with a newer build maybe?

Hi

Thanks Nick. Have you tried it on a null allowing foreign key - I think
that is where the problem is - I see you example includes the "NOT NULL"
clause. I'll try to download the latest ebf later.

Justin

Nick Elson [Sybase iAnywhere] wrote:

Okay, I see you point clearer now .... and I think
I may have been here before with an earlier posting.

I can reproduce this and control the result directly
but checking and unchecking the "Allow null values"
check box in the Fkey properties [in the Change 'actions'
sub-dialog].

So maybe this is not a SCJView question but
maybe its a dbunload one after all.





"Justin Willey" <gjw (AT) nospam (DOT) iqx.co.uk> wrote

Now in corrected English



"Nick Elson [Sybase iAnywhere]" <@nick@.@elson@@sybase@.@com@> wrote in
message news:4a940fdf$1 (AT) forums-1-dub (DOT) ..

I think DBUnload is correct and SCJView (copy function) is wrong. In
my example both DBUnload and the SCJView user interface show a foreign
key allowing nulls, but the copy and paste produces a script that does
NOT allow nulls.

Sorry for the confusion -it took me a while to work out what was going on!

Justin

Nick Elson [Sybase iAnywhere] wrote:

While I think I understand your point I still think (this time)
DBUnload has it wrong. The reason being ....

On my machine, dbunload will never unload a NOT NULL
foreign key whereas SCJView will copy the correct setting
for that.

And while we are talking about NULLs here! Lets not
lose site of the fact that this is a 3rd sense where foreign
keys and null come up.

1 - your child table must have nullable columns

2 - your trigger action can cascase actions (two
possible) cases for this one setting nulls

*** and the case we are actually talking about here***

3 - a nullable foreign key definition. [This one
allows/prevents direct insert of nulls into
the child table fkey columns]

For this thread we are actually talking about the
this 3rd sense. Sure you have set nulls for
both UPDATE and DELETE cascades, but
those alow the cascade action to happen; they
do not control direct 'orphan' inserts into the
child table.

-----
Just to may sure my left-right ambi-maladroitness
didn't enter into this discussion these are my
results ... just produced .... just now ... again ...

With fkey set as Allowing nulls ... My Clipboard copy of ddl is
=====================
ALTER TABLE "DBA"."child" ADD CONSTRAINT "parent" FOREIGN KEY ( "fk1" ASC )
REFERENCES "DBA"."parent" ( "pk1" ) ON UPDATE SET NULL ON DELETE SET NULL;


With fkey set to Not Allow nulls ... My Clipboard copy of ddl is
======================
ALTER TABLE "DBA"."child" ADD CONSTRAINT "parent" NOT NULL FOREIGN KEY (
"fk1" ASC ) REFERENCES "DBA"."parent" ( "pk1" ) ON UPDATE SET NULL ON DELETE
SET NULL;


Whereas dbunload .... It always comes out as
=============
ALTER TABLE "DBA"."child"
ADD FOREIGN KEY "parent" ("fk1")
REFERENCES "DBA"."parent" ("pk1")
ON UPDATE SET NULL ON DELETE SET NULL
go



If you still don't get this behaviour let me know your
versions and your steps to see your results.




"Justin Willey" <gjw (AT) nospam (DOT) iqx.co.uk> wrote

This can come about pretty easily when using
SCJView to create your foreign keys. The
New Foreign Key Wizard panel for Uniqueness
and Nullable Values makes it pretty easy to just
click on the "Allow null values" check box;
'in passing'.

There could also be an implied non-null added
to any explicit DDL action (if not specified
explicitly in the ALTER TABLE) if connecting
via OpenClient or JConnect. <not verified>


Either way this seems to be a utiliy bug and I
have already submitted that to engineering
to correct when it is appropriate to do so.

"Nick Elson [Sybase iAnywhere]" <@nick@.@elson@@sybase@.@com@> wrote in
message news:4a943c19$1 (AT) forums-1-dub (DOT) ..

Hi

Mmmm all very odd as I have definitely have the reverse problem. However
if engineering can have a look I'm sure they will get to the bottom of it.

Many thanks for going into the guts of this!

Justin


Nick Elson [Sybase iAnywhere] wrote: