[Handler]

What I want to do:
A) Restrict changing the ID of a ticket if the ticket has comments
Delete comments if the ticket gets deleted

Currently trying:

	// Creates ht_tickets
	private final String CREATE_TICKETS_QUERY = "CREATE  TABLE `*database*`.`*prefix*tickets` ("
                + "  `htid` INT UNSIGNED NOT NULL AUTO_INCREMENT ,"
                + "  `htmessage` VARCHAR(140) NULL ,"
                + "  `htopen` BIT NULL DEFAULT 1 ,"
                + "  `htopenby` VARCHAR(20) NULL ,"
                + "  `htclosedby` VARCHAR(20) NULL ,"
                + "  `htopendate` BIGINT NULL ,"
                + "  `htclosedate` BIGINT NULL ,"
                + "  `blockX` INT NULL ,"
                + "  `blockY` INT NULL ,"
                + "  `blockZ` INT NULL ,"
                + "  `world` VARCHAR(45) NULL ,"
                + "  PRIMARY KEY (`htid`) ,"
                + "  INDEX `OPEN` (`htopen` ASC) );";

	// Creates ht_comments
	private final String CREATE_COMMENTS_QUERY = "CREATE  TABLE `*database*`.`*prefix*comments` ("
                + "  `commentid` INT NOT NULL AUTO_INCREMENT ,"
                + "  `htid` INT NULL ,"
                + "  `commentby` VARCHAR(20) NULL ,"
                + "  `comment` VARCHAR(140) NULL ,"
                + "  `date` BIGINT NULL ,"
                + "  PRIMARY KEY (`commentid`) ,"
                + "  INDEX `indx_htid` (`htid` ASC),"
                + " CONSTRAINT `FK_ticket` FOREIGN KEY (`htid`) REFERENCES `*prefix*tickets` (`htid`) ON DELETE CASCADE ) ;";

Is that correct?

[macosxnerd101]

Why are you trying to create tables from your Java application? Your database design should be separate from your Java application. The Java application should simply interact with the database and SELECT, DELETE, UPDATE, and INSERT data. It shouldn't deal with table structure.

I'm going to move this to Databases for better discussion. Also, try running your queries.

[Ionut]

A) You can do a simple select to verify if comments table has values for current ticket id

DECLARE @Count INT
SELECT @Count = Count(*) from *prefix*comments where htid = @someId
If IsNull(@Count, 0) = 0 
    --update statement

B ) before you delete a ticket, save the id value into a variable and then delete from both tables based on that value

DELETE FROM *prefix*comments where htid = @ID
DELETE FROM *prefix*tickets where htid = @ID

Provided code is for MSSQL. Make sure you make it compatible with the db engine you use.