Last Updated on
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.
- Tutorialspoint.com has a complete series on SQL at https://www.tutorialspoint.com/sql
One of the descriptions about foreign keys in a table is at the link: https://www.tutorialspoint.com/sql/sql-foreign-key.htm
- W3Schools.com has a very useful series and the page describing foreign keys is found at its series page: https://www.w3schools.com/sql/sql_foreignkey.asp
- For a more thorough introduction to table relationships, there is https://launchschool.com/books/sql/read/table_relationships
Resources during development
- How to insert values in table with foreign key using MySQL?