Refferential Integrity Issue: Parent Table FK not setting on INSERT

[Popgun]

Hi all,

 

I'm having an issue with a parent table not setting the foreign key column after an inset to the child table (which is setting the PK and FK correctly) I'm not sure if the issue is in my record set or the INSERT and am wondering if someone could take a peek. I'm not sure why the FK on the parent table would not set on an insert. Code below, table structure is as follows:

 

Tables are both InnoDB

 

USER TABLE (parent)

user_ID medint( Not Null/Auto Increment/unsigned (PK)

disclaimer_ID medint( Null (FK) (default setting for update/delete)

 

DISCLAIMER TABLE (child)

disclaimer_ID  medint( Not Null/Auto Increment/unsigned (PK)

user_ID medint( Null (FK) (Set to On Update = Cascade)

 

 

<?php require_once('../../Connections/conn.php'); ?><?php

 

//initialize the session

if (!isset($_SESSION)) {

  session_start();

}

$_SESSION['MM_Username'];

$_SESSION['user_ID'];

?><?php

if (!function_exists("GetSQLValueString")) {

function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")

{

A bunch of dreamweaver recordset code spaghetti

  }

  return $theValue;

}

}

 

$editFormAction = $_SERVER['PHP_SELF'];

if (isset($_SERVER['QUERY_STRING'])) {

  $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);

}

 

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "disclaimerdisplay")) {

  $insertSQL = sprintf("INSERT INTO DISCLAIMER (user_ID, tos, health, legal, criminal, privacy, disclaimeragree) VALUES (%s, %s, %s, %s, %s, %s, %s)",

                      GetSQLValueString($_POST['user_ID'], "int"),

                      GetSQLValueString($_POST['tos'], "text"),

                      GetSQLValueString($_POST['health'], "text"),

                      GetSQLValueString($_POST['legal'], "text"),

                      GetSQLValueString($_POST['criminal'], "text"),

                      GetSQLValueString($_POST['privacy'], "text"),

                      GetSQLValueString($_POST['disclaimeragree'], "text"));

 

  mysql_select_db($database_conn, $conn);

  $Result1 = mysql_query($insertSQL, $conn) or die(mysql_error());

}

 

$colname_rsDisclaimer = "-1";

if (isset($_SESSION['user_ID'])) {

  $colname_rsDisclaimer = $_SESSION['user_ID'];

}

mysql_select_db($database_conn, $conn);

 

$query_rsDisclaimer = sprintf("SELECT * FROM USERS LEFT JOIN DISCLAIMER USING (user_ID) WHERE user_ID = %s", GetSQLValueString($colname_rsDisclaimer, "int"));

$rsDisclaimer = mysql_query($query_rsDisclaimer, $connmatrix) or die(mysql_error());

$row_rsDisclaimer = mysql_fetch_assoc($rsDisclaimer);

$totalRows_rsDisclaimer = mysql_num_rows($rsDisclaimer);

 

$_SESSION['user_ID'];

?>

 

Ive tried:

 

Setting the USER table Fk to (disclaimer_ID) Not Null but that creates an error on registration of users.

 

Not sure what the issue is, I have a feeling it might be that I somehow need to specify the disclaimer_ID on the USERS table during INSERT, but that means inserting to more than 1 table I think

 

As always thanks in advance

[artacus]

Well it looks like you've got a circular reference. Do both tables refer to each other? If so that wont work.

 

Why would you have a disclaimer_id in the user table?

[Popgun]

Well it looks like you've got a circular reference. Do both tables refer to each other? If so that wont work.

 

Why would you have a disclaimer_id in the user table?

 

Artacus! You are right I just reviewed my database, my users table has circular references (lots of them)! I'm going to have to review and drop them I believe.