Problem with cut and paste of Table Constraints

#11

So are you saying your SYSFKEY."NULLS"
shows it is equal to 'N' for that tableid?

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

Quote:

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:
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) ..
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 in message
news:4a941d34$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:
Now in corrected English

"Nick Elson [Sybase iAnywhere]" <@nick@.@elson@@sybase@.@com@> wrote
in message news:4a940fdf$1 (AT) forums-1-dub (DOT) ..
Okay, I see youR 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
bY 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 in message
news:4a94058c$1 (AT) forums-1-dub (DOT) ..
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

"Justin Willey" <gjw (AT) nospam (DOT) iqx.co.uk> wrote in message
news:4a93f5b4 (AT) forums-1-dub (DOT) ..
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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Problem with cut and paste of Table Constraints

sybase.public.sqlanywhere.general sybase.public.sqlanywhere.general

Re: Problem with cut and paste of Table Constraints - 08-26-2009 , 06:32 PM