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

[nikki sinha]

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]

How about using triggers and/or cascade options?

[chris webster]

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).