#11885: Foreign Key Match Type comes with keyword UNSPECIFIED which gives error in Netezza

tariqrahiman reported 2014-04-06T23:25:59Z · last modified 2014-04-21T20:08:14Z

Foreign Key Match Type comes with keyword UNSPECIFIED which gives error in Netezza

Dev	ivangron
QA	tariqrahiman

Priority	Low
Complexity	Unknown

Component	App - Visual Editing
Version	14.0

Version: 14.0.10-11

Build #: 37098

Build Date: 2014-Apr-04 05:44:58 PM

Foreign Key Match Type comes with keyword UNSPECIFIED which gives error in Netezza

Create a Foreign Key and then script to window, you can see that instead of the Match types Full or Partial, the keyword UNSPECIFIED is scripted. If i try to recreate the foreign key I get SQL error

^ found "UNSPECIFIED" (at char 149) expecting `FULL' or `PARTIAL'

1. To reproduce, go to TARIQ database and script the foreign key under RS schema for table RS.ACTIVITIES

2. If you drop the foreign key and try to script it using ADS you get an error

ALTER TABLE "RS"."ACTIVITIES"

DROP CONSTRAINT "FK_PK" CASCADE

ALTER TABLE "RS"."ACTIVITIES"

ADD CONSTRAINT FK_PK FOREIGN KEY("ACTIVITY_ID")

REFERENCES "TARIQ"."RS"."activities"("activity_id")

MATCH UNSPECIFIED

ON UPDATE NO ACTION

ON DELETE NO ACTION

DEFERRABLE

INITIALLY IMMEDIATE

3 attachments

Screen Shot 2014-04-06 at 4.26.37 PM.png

2014-04-06T23:25:59Z

22 KB
Screen Shot 2014-04-18 at 11.34.45 AM.png

2014-04-18T18:32:40Z

51 KB
Match.png

2014-04-21T09:11:03Z

204 KB

All Comments (7) Change History

ivangron 2014-04-07T00:54:52Z

modifications to the catalog tables needed handling

tariqrahiman 2014-04-07T18:10:34Z

Verified in 14.0.10-12

tariqrahiman 2014-04-08T17:19:56Z

Verified in v15.0.0-dev-217

narendravatsaraj(*) 2014-04-18T10:23:12Z · (edited)

In Aqua Data Studio 14.0.11,

I dropped foreign key

ALTER TABLE "table_employee"

DROP CONSTRAINT "FK04" CASCADE

and try to add that foreign key back,

ALTER TABLE "table_employee"

ADD CONSTRAINT FK04 FOREIGN KEY("name")

REFERENCES "my_database"."table_company"("id")

MATCH UNSPECIFIED

ON UPDATE RESTRICT

ON DELETE CASCADE

DEFERRABLE

INITIALLY IMMEDIATE

I got same error.

>[Error] Script lines: 5-13 -------------------------

ERROR: 'ALTER TABLE "table_employee"

ADD CONSTRAINT FK04 FOREIGN KEY("name")

REFERENCES "my_database"."table_company"("id")

MATCH UNSPECIFIED

ON UPDATE RESTRICT

ON DELETE CASCADE

DEFERRABLE

INITIALLY IMMEDIATE'

error ^ found "UNSPECIFIED" (at char 126) expecting `FULL' or `PARTIAL'

[Executed: 18/4/2014 3:49:18 PM] [Execution: 0ms]

ivangron 2014-04-18T17:42:52Z

This was fixed 11 days ago and checked in as revision #37116 into source control. I am unable to reproduce this from 14.0.11 or from source code. The old code would script as per what the database told us the value was and it started returning UNSPECIFIED. The new code no longer scripts uncontrolled strings. It can only script FULL || PARTIAL. I am not sure how you are getting this error in 14.0.11.

Could you please make sure you have the correct version?

tariqrahiman 2014-04-18T18:34:57Z

@narendra: The visual editor now restricts any items other than FULL and PARTIAL. See attached screenshot.

Can you attach a screenshot of how you are getting UNSPECIFIED ? Also let us know what version of Netezza.

narendravatsaraj(*) 2014-04-21T09:11:38Z · (edited)

After reading Ivan's comment,I understood this bug more clearly.
See attached screenshot(Match.jpg)
When we keep "Match" option blank,SQL do not include Match keyword in SQL Query.

I am verifying this on Netezza 7.0.3

Search Tips

Aqua Data Studio / nhilam

Foreign Key Match Type comes with keyword UNSPECIFIED which gives error in Netezza

3 attachments

Issue #11885

Completion

Search Tips

Aqua Data Studio / nhilam

Title

Foreign Key Match Type comes with keyword UNSPECIFIED which gives error in Netezza

3 attachments

Issue #11885

Completion