Have two simple SQL tables — one holding a foreign key, which references field in other table.

Last Updated on: (senast uppdaterad på:)

Working with databases from a Java EE application requires understanding of the SQL database tables design. The design which includes table naming, table fields naming, field types and other well-thought-out design elements described by database administrators also includes how the relationship between the various tables works.

It is much better to design the tables with the right structure at first place rather than altering later. Otherwise altering a table would require SQL queries that sometimes take more time to figure out.

Three alteration examples for a MySQL Database table would be:

-- make "id" field in "tasks" table primary:
ALTER TABLE tasks ADD CONSTRAINT pk_id PRIMARY KEY (id);

-- add auto increment to field in "tasks" table:
ALTER TABLE tasks AUTO_INCREMENT=0;

-- rename column name "id" to "taskStatus_id":
ALTER TABLE task RENAME COLUMN id TO taskStatus_id;

Relationships between tables can be designed through fields that connect one table to the other. Let’s say for example that we design two tables, one called “tasks” and the other called “taskstatus” with a relationship. table A would have fields “id”, “topic”, “description”, “parent_status” and table “taskstatus” holds fields “id”, “status”, “description”. The field “parent_status” of “tasks” table would reference “status” in the “taskstatus” table since we are planning to have our list of task status such as “to do”, “done”, “doing” in its own table.

Relationships that may help us design a better database and application would include one-to-one, one-to-many, and many-to-many relationship between table fields. For these to work the fields should have certain constraints. For a MySQL one-to-many relationship to work, for example, the field that is being referenced in the parent table should either be PRIMARY or UNIQUE.

The completed tables’ SQL structure for our two tables is as follows:

CREATE TABLE taskstatus (
id INT NOT NULL AUTO_INCREMENT,
status VARCHAR(255) UNIQUE,
description VARCHAR(255),
PRIMARY KEY(id)
) ENGINe=INNODB;


CREATE TABLE task (
id INT NOT NULL AUTO_INCREMENT,
topic VARCHAR(255),
parent_status VARCHAR(255),
description VARCHAR(255),
INDEX tasksstatus_ind (parent_status),
PRIMARY KEY(id),
FOREIGN KEY(parent_status) references taskstatus(status) ON DELETE CASCADE
) ENGINE=INNODB;

Now that the tables are ready we can add data:

-- insert an item to the parent table "taskstatus" that holds UNIQUE status
INSERT INTO taskstatus (id, status, description) VALUES (1, "to do", "");

-- now that a "status" is available in the parent table, we can add a task that uses the status we mentioned in order to insert a task with the "status" foreign key value.
INSERT INTO tasks (id, topic, parent_status, description) VALUES(2,"clean garden","to do", "");

To add a record which has a foreign key we can use the SQL code syntax below:

INSERT INTO tasks(topic, parent_status) 
  SELECT 'put books on the shelf',
  status FROM taskstatus 
  
  WHERE
  
  status='to do' LIMIT 1;

Understanding SQL Table relations help design an application with consideration for foreign keys and the SQL statements required when interacting with the database.

Resources during development

Lämna ett svar