#13278: Amazon RedShift 1.0 : Cannot display Primary key,Unique Key and Foreign Key in Table Properties window

nisha.gaikwad(*) reported 2015-04-28T12:00:38Z · last modified 2016-04-01T18:20:30Z

Amazon RedShift 1.0 : Cannot display Primary key,Unique Key and Foreign Key in Table Properties window

Dev	ivangron
QA	tariqrahiman

Priority	Major
Complexity	Unknown

Component	DB - RedShift
Version	17.0

Product: Aqua Data Studio

Version: 17.0.0-dev-18

Build #: 43680

Build Date: 2015-Apr-22 06:02:48 PM

Operating Environment: Windows 7 (6.1, amd64) / Cp1252 / en / IN / Oracle Corporation 1.8.0_40-b26

Memory: Max=704,643,072; Total=315,097,088; Free=114,294,104; CPUs=8

Amazon RedShift 1.0 : In "Constraint" tab of table properties window Primary key,Unique Key and Foreign Key are not displayed.

Steps -

1.Connect to Amazon RedShift database

2.Run the following script on Query Analyzer

CREATE TABLE products (

product_no integer PRIMARY KEY,

name text,

price numeric

)

CREATE TABLE orders (

order_id integer PRIMARY KEY,

product_no integer REFERENCES products (product_no),

quantity integer

)

ALTER TABLE public.orders

ADD CONSTRAINT orders_ukey

UNIQUE (quantity)

3.Expand table tree node right click on "orders" table and select option "Table Properties".

4.In "Properties:Table:orders" window switch to "CONSTRAINT" tab.

Actual Result - In "CONSTRAINT" tab of table properties window Primary key,Unique Key and Foreign Key constraints are not displayed.

Observation -

1.Broken build Aqua Data Studio 17.0.0-dev-14

2.This issue is not reproducible in Aqua Data Studio 16.0.4-6

3.Constraints are not displayed in ER-Modeler,Schema Script Generator and Script Object to Window As CREATE/CREATE(Full).

1 attachment

ADS 18 -dev -98.png

2016-04-01T12:49:08Z

56 KB

All Comments (11) Change History

nisha.gaikwad(*) 2015-04-29T14:34:51Z · (edited)

Amazon RedShift 1.0 : View not displayed in "Dependencies" tab of table properties window

Scenario 2 -

1.Connect to Amazon RedShift database

2.Run the following script on Query Analyzer

CREATE TABLE orders (

order_id integer ,

product_no integer ,

quantity integer

)

CREATE VIEW public.view_order11

SELECT orders.order_id, orders.product_no, orders.quantity FROM orders;

3.Expand table tree node right click on "orders1" table and select option "Table Properties".

4.In "Properties:Table:orders" window switch to "Dependencies" tab.

Actual Result - In "Dependencies" tab of table properties window ".view_order11" view is not displayed.

Note - When open Table Properties window it throw "java.sql.SQLException: [Amazon](500310) Invalid operation: syntax error at end of input;" in view log and in SQL log it generate wrong query (i.e at the end of query extra "UNION" keyword is displayed).

Below is the query which generate in SQL log when we open Table Properties window.( Note- at the end of query extra "UNION" is displayed)

SELECT

cs.nspname as objSchema,

c.conname as objName,

c.contype as objType,

rs.nspname as reftblSchema,

ro.relname as reftblName

FROM

pg_catalog.pg_namespace s

LEFT JOIN pg_catalog.pg_class o

ON s.oid = o.relnamespace

LEFT JOIN pg_catalog.pg_depend d

ON o.oid = d.refobjid

INNER JOIN pg_catalog.pg_constraint c

ON d.objid = c.oid

LEFT JOIN pg_catalog.pg_namespace cs

ON c.connamespace = cs.oid

LEFT JOIN pg_catalog.pg_class ro

ON c.confrelid = ro.oid

LEFT JOIN pg_catalog.pg_namespace rs

ON ro.relnamespace = rs.oid

WHERE

d.deptype in ('n',

'e') AND

s.nspname = 'public' AND

o.relname = 'orders1'

UNION

SELECT

view_schema as objSchema,

view_name as objName,

'v' as objType,

null as reftblSchema,

null as reftblName

FROM

information_schema.view_table_usage

WHERE

table_schema = 'public' AND

table_name = 'orders1'

UNION

java.sql.SQLException: [Amazon](500310) Invalid operation: syntax error at end of input;

at com.amazon.redshift.client.messages.inbound.ErrorResponse.toErrorException(Unknown Source)

at com.amazon.redshift.client.PGMessagingContext.handleErrorResponse(Unknown Source)

at com.amazon.redshift.client.PGMessagingContext.getOperationMetadata(Unknown Source)

at com.amazon.redshift.client.PGMessagingContext.handleMessage(Unknown Source)

at com.amazon.jdbc.communications.InboundMessagesPipeline.getNextMessageOfClass(Unknown Source)

at com.amazon.redshift.client.PGMessagingContext.doMoveToNextClass(Unknown Source)

at com.amazon.redshift.client.PGMessagingContext.getReadyForQuery(Unknown Source)

at com.amazon.redshift.client.PGMessagingContext.getOperationMetadata(Unknown Source)

at com.amazon.redshift.client.PGMessagingContext.handleMessage(Unknown Source)

at com.amazon.jdbc.communications.InboundMessagesPipeline.getNextMessageOfClass(Unknown Source)

at com.amazon.redshift.client.PGMessagingContext.doMoveToNextClass(Unknown Source)

at com.amazon.redshift.client.PGMessagingContext.getReadyForQuery(Unknown Source)

at com.amazon.redshift.client.PGMessagingContext.getOperationMetadata(Unknown Source)

at com.amazon.redshift.client.PGMessagingContext.getBindComplete(Unknown Source)

at com.amazon.redshift.client.PGClient.directExecute(Unknown Source)

at com.amazon.redshift.dataengine.PGIQueryExecutor.execute(Unknown Source)

at com.amazon.jdbc.common.SStatement.executeNoParams(Unknown Source)

at com.amazon.jdbc.common.SStatement.executeQuery(Unknown Source)

at \\...\\ .\\हिñçêČάй語简�?한\\.V ⠽ꊣ⣰ 5.executeQuery(Unknown Source)

at \\...\\ .\\हिñçêČάй語简�?한\\.k᠓⢙᠞⠷ while.b(Unknown Source)

at \\...\\ .\\हिñçêČάй語简�?한\\.zꆚꁲꋏꇒ double.l(Unknown Source)

at \\...\\ .\\हिñçêČάй語简�?한\\.zꆚꁲꋏꇒ double.k(Unknown Source)

at \\...\\ .\\हिñçêČάй語简�?한\\.zꆚꁲꋏꇒ double.j(Unknown Source)

at \\...\\ .\\हिñçêČάй語简�?한\\.Gꑕ⡂͒ᠻ public.dJ(Unknown Source)

at \\...\\ .\\हिñçêČάй語简�?한\\.Hꇺͯ⣔ꉫ for 3.process(Unknown Source)

Caused by: com.amazon.support.exceptions.ErrorException: [Amazon](500310) Invalid operation: syntax error at end of input;

... 28 more