sql: within a transaction, dropping the foreign key constraint followed by dropping the referenced table doesn't work

**Describe the problem**

Within a transaction, dropping the foreign key constraint followed by dropping the referenced table doesn't seem to work

**To Reproduce**

Create two tables with the following schema

```
CREATE TABLE public."Owners" (
"Id" STRING NOT NULL,
"Name" STRING NULL,
"Version" INT8 NOT NULL,
CONSTRAINT "PK_Owners" PRIMARY KEY ("Id" ASC)
)
```

```
CREATE TABLE public."Blogs" (
"Id" UUID NOT NULL,
"Order" INT8 NOT NULL,
"OwnerId" STRING NULL,
"Version" INT8 NOT NULL,
CONSTRAINT "PK_Blogs" PRIMARY KEY ("Id" ASC),
CONSTRAINT "FK_Blogs_Owners_OwnerId" FOREIGN KEY ("OwnerId") REFERENCES public."Owners"("Id"),
INDEX "IX_Blogs_OwnerId" ("OwnerId" ASC)
)
```

Try to drop the foreign key constraint `FK_Blogs_Owners_OwnerId` and then drop the two tables in the same transaction as follow

```
BEGIN;
ALTER TABLE public."Blogs" DROP CONSTRAINT "FK_Blogs_Owners_OwnerId";
DROP TABLE public."Owners";
DROP TABLE public."Blogs";
COMMIT;
```

The following error is returned

```
2BP01: "Owners" is referenced by foreign key from table "Blogs"
```

**Additional context**

This is affecting some tests in efcore.pg library

@fqazi @rafiss

Jira issue: CRDB-30285

Epic CRDB-60817