I have the following table:

CREATE TABLE "GRUPO"
(
"IDGRUPO" GUID COLLATE "UNI",
"ID" GUID COLLATE "UNI" NOT NULL,
"DOMINIO" INTEGER,
"OWNER" GUID COLLATE "UNI",
"NOME" VARCHAR(100) COLLATE "UNI" NOT NULL,
"DESCRICAO" VARCHAR(100) COLLATE "UNI",
"ORDEM" INTEGER,
"ESTRUTURAL" VARCHAR(100) COLLATE "UNI",
"LINK" VARCHAR(20) COLLATE "UNI",
CONSTRAINT "PK_GRUPO" PRIMARY KEY ("ID"),
CONSTRAINT "FK_GRUPO_GRUPO_IDGRUPO" FOREIGN KEY ("IDGRUPO") REFERENCES "GRUPO" ("ID")
  ON UPDATE NO ACTION ON DELETE NO ACTION
)

Where I have that second constrait that makes a PARENT relationship between a record and its parent on the same table.

when I try:

delete from grupo

I get:

ElevateDB Error #1004 The foreign key constraint FK_GRUPO_GRUPO_IDGRUPO for the table GRUPO has been violated (Foreign keys exist which refer to the primary or unique key {D596D17A-F4A2-4B10-A898-CBC08584EA7B} in the table GRUPO)

I beleve this command go from the first record one by one, and in this case the constraint can be violated.

Is there a EMPTY table command that is more low level and does not pay attention to this?

This is not used in production, is more for development, since I load records, and see errors and need to do all again and drop table is not good option.

Thanks !!

Eduardo:

DROP CONSTRAINT "FK_GRUPO_GRUPO_IDGRUPO"

before deleting.

Eduardo


There is an EMPTY TABLE command but I think it will still take notice of the constraints (haven't tried it) but if you just want a quick and dirty method of clearing out the table use DOS - ok Windows Explorer and just delete the 2 or 3 files that make up the table. All the metadata is in the catalog so ElevateDB will simply re-create the table when needed.

Its what I used to do before EMPTY TABLE came along.

Roy Lambert

Eduardo,

Any one of the previously suggested solutions would work: dropping the constraint then delete the rows, or EMPTY TABLE, however the second one suggested by Roy is way faster and simpler.

If you choose to drop the constraint, the correct syntax is:
  ALTER TABLE "GRUPO" DROP CONSTRAINT "FK_GRUPO_GRUPO_IDGRUPO"

Then, after deleting the rows you should recreate the constraint:
  ALTER TABLE "GRUPO" ADD CONSTRAINT "FK_GRUPO_GRUPO_IDGRUPO" FOREIGN KEY ("IDGRUPO") REFERENCES "GRUPO" ("ID")


The faster solution is:
  EMPTY TABLE Grupo IGNORE CONSTRAINTS

Please note the IGNORE CONSTRAINTS clause.


--
Fernando Dias
[Team Elevate]

Fernando:

That made the trick !

Thank you!


Fernando Dias wrote:

Eduardo,

Any one of the previously suggested solutions would work: dropping the constraint then delete the rows, or EMPTY TABLE, however the second one suggested by Roy is way faster and simpler.

If you choose to drop the constraint, the correct syntax is:
  ALTER TABLE "GRUPO" DROP CONSTRAINT "FK_GRUPO_GRUPO_IDGRUPO"

Then, after deleting the rows you should recreate the constraint:
  ALTER TABLE "GRUPO" ADD CONSTRAINT "FK_GRUPO_GRUPO_IDGRUPO" FOREIGN KEY ("IDGRUPO") REFERENCES "GRUPO" ("ID")


The faster solution is:
  EMPTY TABLE Grupo IGNORE CONSTRAINTS

Please note the IGNORE CONSTRAINTS clause.


--
Fernando Dias
[Team Elevate]