Mysql – foreign key constraints on same table

foreign keyMySQL

I have a table structure like this.

tblquestion
(
  nQuestionId1 INT UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT_NULL,
  nQuestionId2 INT UNSIGNED ALLOW NULL
);

Here..nQuestionId2 has a foreign key constraints to the nQuestionId1 on the same table i.e. tblquestion.

So..while restoring backup i am getting some warning and errors for foreign key on nQuestionId2..So,is it because of the foreign key reference to same table column??

Does this type of table structure can create any issues further?

Thanks

Best Answer

You will get errors if you restore a row that contains a value in nQuestionId2 that references a value in nQuestionId1 that has not been created yet.

There are a few ways to resolve this issue:

You could ensure that you create the data in the correct order.
You could disable the foreign keys before importing the data, then re-enable them after.
If disable/re-enable is not an option, you could drop/recreate them.
Alternatively you could split out the data so that the nQuestionId1 is in a separate table - this will allow you to restore all the nQuestionId1 values first.

I guess but the question really is about what you are trying to achieve.

I have seen designs like this that have a ID column and then a PARENT column that references the ID. In order to get the root parent you have to recurse through all of the parents of the object you are looking at. This means that even though a design like this may make sense logically, querying it could become quite difficult.

Database design is not just about mapping business rules and objects to tables and constraints, but also thinking about what is maintainable, how will the design hold up against large amounts of data, and being pragmatic about your approach.

I hope this helps you.

Related Solutions

MySQL – Foreign Key Constraint Fails

You need to have already declared the table that the foreign key references, before you can define a foreign key that references it.

Once you declare the second table, you can then declare the first table. Tested here on MySQL 5.5.27.

If you need to bypass the validation, you can do this:

SET FOREIGN_KEY_CHECKS = 0;
-- declare tables
SET FOREIGN_KEY_CHECKS = 1;

http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_foreign_key_checks

Mysql – Finding rows for a specified date range

Another way to get the result is this. It finds first all groups that the teacher has surely taught (or is going to) by checking that she has started within the month and then in another subquery it finds - for every group - the last teacher that started at the first day of the month or earlier.

With the unique index you have on the table, the second subquery should be quite efficient. The first subquery would benefit from an index on (teacherid, startdate, groupid):

SELECT groupid
FROM pupilgroupteacher
WHERE teacherid = @teacher 
  AND startdate >= @month + INTERVAL 1 DAY
  AND startdate < @month + INTERVAL 1 MONTH

UNION DISTINCT

SELECT gg.groupid
FROM 
    ( SELECT DISTINCT groupid
      FROM pupilgroupteacher
    ) AS gd
  JOIN pupilgroupteacher AS gg
    ON  gg.groupid = gd.groupid
    AND gg.startdate =
        ( SELECT MAX(gi.startdate)
          FROM pupilgroupteacher AS gi 
          WHERE gi.groupid = gd.groupid
            AND gi.startdate < @month + INTERVAL 1 DAY
        )
WHERE gg.teacherid = @teacher ;

Best Answer

Related Solutions

MySQL – Foreign Key Constraint Fails

Mysql – Finding rows for a specified date range

Related Question