This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes JDBC and the fly likes create two tables linked with foreign key, insert,delete,and update in mysql Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "create two tables linked with foreign key, insert,delete,and update in mysql" Watch "create two tables linked with foreign key, insert,delete,and update in mysql" New topic
Author

create two tables linked with foreign key, insert,delete,and update in mysql

nikki sinha
Greenhorn

Joined: Jul 04, 2012
Posts: 14
i have a scenario, in which i have to create a table with following fields

department_name, ministry_name, domain_name, coordinator_name, coordinator_email

now, for each department_name there can be more than one coordinator_name and corresponding to it more than one coordinator_email.

i can't do this in one table(department) because i can't store two coordinator name and their coordinator email in a single row of mysql.

so i planned to make one table(coordinator) with department_name(primary key), ministry_name, domain_name
and second table with department_name(primary key), coordinator_name, coordinator_email with a foreign key reference to department(department_name)

now how can i use insert statement so that the data gets inserted in both tables at once. and similarly how can i use delete and update statement...

please help... thankyou...
Swastik Dey
Rancher

Joined: Jan 08, 2009
Posts: 1449
    
    6

How about using triggers and/or cascade options?


Swastik
chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1607
    
  13

nikki sinha wrote:i have a scenario, in which i have to create a table with following fields

department_name, ministry_name, domain_name, coordinator_name, coordinator_email

now, for each department_name there can be more than one coordinator_name and corresponding to it more than one coordinator_email.

i can't do this in one table(department) because i can't store two coordinator name and their coordinator email in a single row of mysql.

so i planned to make one table(coordinator) with department_name(primary key), ministry_name, domain_name
and second table with department_name(primary key), coordinator_name, coordinator_email with a foreign key reference to department(department_name)

now how can i use insert statement so that the data gets inserted in both tables at once. and similarly how can i use delete and update statement...

please help... thankyou...


You've got your tables the wrong way around. Each Department can have many Coordinators, so your tables should reflect this.

  • You need a Departments table, containing only information that relates to the Department. The PK for this table should be a numeric department ID, but you might also define a unique key on the department name.
  • Then you need a Coordinators table that contains only information that relates to the Coordinator. This should have a numeric Coordinator ID as the primary key.
  • Now you need a foreign key to define the relationship between Coordinators and Departments. One Department can have many Coordinators, so you need to include the primary key of the parent table (Department ID) in the child table (Coordinators).
  • Finally, define a foreign key constraint to enforce this relationship.

  • Data manipulation (DML):

  • INSERTs: You always have to insert the parent record first, then you can add a child record (which will include the ID of the new Department) i.e. you need two SQL INSERT statements here, although they can be executed as part of the same transaction to ensure consistency.
  • DELETEs: You cannot delete a parent record while it still has children (the FK constraint checks this), so if you have to delete a Department, you'll need to remove all its Coordinators (or re-allocate them to a different Department) first. There are options for defining your FK constraint to "cascade" deletions automatically, but you need to be sure this is really what you want to happen, or you may end up losing data.
  • UPDATEs: Any UPDATEs to Departments or Coordinators will be independent of each other, so you simply update whichever table needs modifying e.g. to change a Coordinator's email address.

  • Tips:
  • Read some basic tutorials on relational data modelling because right now you obviously have no idea how this stuff works.
  • Start thinking clearly about your data - work out which attributes belong with which entity, and you will find it all much easier.
  • Make sure you know how to define a primary key constraint, a foreign key constraint, a unique key constraint, and how to populate an auto-incrementing ID column (e.g. using an Oracle sequence, or a MySQL auto-increment column).
  • Remember that ideally a primary key should never change its value, so your PK needs to be something that will remain unchanged for the lifetime of the object. Using a unique numeric ID means you don't need to worry if the company decides to re-name the department, for example.
  • A numeric ID also takes up less space and is easier to manage when you need to include it as a foreign key in child tables, especially if the "natural" PK of your parent object would normally be a combination of values (a composite key).


  • No more Blub for me, thank you, Vicar.
     
    I agree. Here's the link: http://aspose.com/file-tools
     
    subject: create two tables linked with foreign key, insert,delete,and update in mysql
     
    Similar Threads
    one to many concepts
    What effects by using a lot of foreign keys?
    Help with FK in PreparedStatement
    inserting data of two forms into two tables with same transaction id
    Update value of primary key impossible