SA16 sa_get_table_definition does not return FKs information

[Thiago_Reis]

We used "sa_get_table_definition" system procedure for a long time in SA 11.0.1.

After upgrading to SA16 we have noticed that Foreign Keys information is no longer present.

Only Self-keys are present in the results of sa_get_table_definition.

Maybe a bug? Should I open an Incident?

Thanks.

[Vlad]

The question to the audience, is this topic relevant for this question: Running pre-16.0 system procedures as invoker or definer?

[Thiago_Reis]

I checked an I´m using the DEFINER model:

"SELECT IF ((HEXTOINT(SUBSTRING(DB_PROPERTY('Capabilities'),1,LENGTH(DB_PROPERTY('Capabilities'))-20)) & 😎 = 😎 THEN 1 ELSE 0 END IF"

It returned 0.

[Breck_Carter · Accepted solution]

Looks like there's a bug an undocumented behavior change in SQL Anywhere 12, 16 and 17.

----------------------------------------------------------------
SELECT @@VERSION;
SELECT sa_get_table_definition ( 'GROUPO', 'SalesOrderItems' );

@@VERSION
'11.0.1.3158'

CREATE TABLE "GROUPO"."SalesOrderItems" (
    "ID"                             integer NOT NULL
   ,"LineID"                         smallint NOT NULL
   ,"ProductID"                      integer NOT NULL
   ,"Quantity"                       integer NOT NULL
   ,"ShipDate"                       date NOT NULL
   ,CONSTRAINT "SalesOrderItemsKey" PRIMARY KEY ("ID" ASC,"LineID" ASC) 
)
;

COMMENT ON TABLE "GROUPO"."SalesOrderItems" IS 
    'individual items that make up the sales orders'
;

GRANT SELECT ON "GROUPO"."SalesOrderItems" TO "GROUPR" 
;

GRANT SELECT,INSERT,DELETE,UPDATE ON "GROUPO"."SalesOrderItems" TO "GROUPRW" 
;

commit work
;
ALTER TABLE "GROUPO"."SalesOrderItems"
    ADD NOT NULL FOREIGN KEY "FK_ProductID_ID" ("ProductID" ASC)
    REFERENCES "GROUPO"."Products" ("ID")

;

ALTER TABLE "GROUPO"."SalesOrderItems"
    ADD NOT NULL FOREIGN KEY "FK_ID_ID" ("ID" ASC)
    REFERENCES "GROUPO"."SalesOrders" ("ID")
    ON DELETE CASCADE 
;

commit work
;
----------------------------------------------------------------
SELECT @@VERSION;
SELECT sa_get_table_definition ( 'GROUPO', 'SalesOrderItems' );

@@VERSION
'12.0.1.4231'

CREATE TABLE "GROUPO"."SalesOrderItems" (
    "ID"                             integer NOT NULL
   ,"LineID"                         smallint NOT NULL
   ,"ProductID"                      integer NOT NULL
   ,"Quantity"                       integer NOT NULL
   ,"ShipDate"                       date NOT NULL
   ,CONSTRAINT "SalesOrderItemsKey" PRIMARY KEY ("ID" ASC,"LineID" ASC) 
)
;

COMMENT ON TABLE "GROUPO"."SalesOrderItems" IS 
    'individual items that make up the sales orders'
;

GRANT SELECT ON "GROUPO"."SalesOrderItems" TO "GROUPR" 
;

GRANT SELECT,INSERT,DELETE,UPDATE ON "GROUPO"."SalesOrderItems" TO "GROUPRW" 
;

commit work
;
commit work
;
----------------------------------------------------------------
SELECT @@VERSION;
SELECT sa_get_table_definition ( 'GROUPO', 'SalesOrderItems' );

@@VERSION
'16.0.0.2344'

CREATE TABLE "GROUPO"."SalesOrderItems" (
    "ID"                             integer NOT NULL
   ,"LineID"                         smallint NOT NULL
   ,"ProductID"                      integer NOT NULL
   ,"Quantity"                       integer NOT NULL
   ,"ShipDate"                       date NOT NULL
   ,CONSTRAINT "SalesOrderItemsKey" PRIMARY KEY ("ID" ASC,"LineID" ASC) 
)
;

COMMENT ON TABLE "GROUPO"."SalesOrderItems" IS 
    'individual items that make up the sales orders'
;

GRANT INSERT,DELETE,UPDATE ON "GROUPO"."SalesOrderItems" TO "MODIFY_ROLE" 
;

GRANT SELECT ON "GROUPO"."SalesOrderItems" TO "READ_ROLE" 
;

commit work
;
commit work
;
----------------------------------------------------------------
SELECT @@VERSION;
SELECT sa_get_table_definition ( 'GROUPO', 'SalesOrderItems' );

@@VERSION
'17.0.7.3399'

CREATE TABLE "GROUPO"."SalesOrderItems" (
    "ID"                             integer NOT NULL
   ,"LineID"                         smallint NOT NULL
   ,"ProductID"                      integer NOT NULL
   ,"Quantity"                       integer NOT NULL
   ,"ShipDate"                       date NOT NULL
   ,CONSTRAINT "SalesOrderItemsKey" PRIMARY KEY ("ID" ASC,"LineID" ASC) 
)
;

COMMENT ON TABLE "GROUPO"."SalesOrderItems" IS 
    'individual items that make up the sales orders'
;

GRANT INSERT,DELETE,UPDATE ON "GROUPO"."SalesOrderItems" TO "MODIFY_ROLE" 
;

GRANT SELECT ON "GROUPO"."SalesOrderItems" TO "READ_ROLE" 
;

commit work
;
commit work
;

[Thiago_Reis · Accepted solution]

Thanks Carter, I will open an incident.

[MarkCulp]

This issue has been fixed in 16.0.0.2549 and 17.0.8.4043.

The sa_get_table_definition built-in system procedure should return the SQL statements required to create the specified table and its indexes, foreign keys, triggers, and granted privileges.

Currently, it is not including foreign key constraints. This procedure last worked correctly in 12.0.1.4181 and 16.0.0.1975 and has not worked correctly in 17.0.

This problem has been fixed.

This fix will also revert dbunload to its earlier behavior where the unloading of a subset of tables (-t option) could include foreign key references to tables that are not included in the unload.