Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

How to save foreign key with null value?

I have 2 models, which are tree and branch.

The relationship between these model is 1 optional tree to many optional branch.

There is no issue to have 1 tree with 0 or many branch, but encounter issue when to have 1 branch with 0 tree.

Output: Message: The Tree does not exist. Field: tree_id Type: ConstraintViolation

<?php

//CREATE TABLE tree
//( tree_id VARCHAR NOT NULL DEFAULT md5(now()::TEXT||random()) PRIMARY KEY,
//  name VARCHAR NOT NULL
//);

class Tree extends Phalcon\Mvc\Model {
    public $tree_id; //VARCHAR NOT NULL DEFAULT md5(now()::TEXT||random()) PRIMARY KEY,
    public $name; //name NOT NULL,


    public function initialize() {
        $this->setSource('tree');

        $this->hasMany('tree_id', 'Branch', 'tree_id', array(
            'foreignKey' => array('message' => 'The Tree cannot be deleted because it is being used by Branch.')
        ));

    }
}

<?php

//CREATE TABLE branch
//( branch_id VARCHAR NOT NULL DEFAULT md5(now()::TEXT||random()) PRIMARY KEY,
//  tree_id VARCHAR DEFAULT NULL REFERENCES tree (tree_id),
//  name VARCHAR NOT NULL
//);

class Branch extends Phalcon\Mvc\Model {
    public $branch_id; //VARCHAR NOT NULL DEFAULT md5(now()::TEXT||random()) PRIMARY KEY,
    public $tree_id; //VARCHAR DEFAULT NULL REFERENCES tree (tree_id),
    public $name; //VARCHAR NOT NULL


    public function initialize() {
        $this->setSource('branch');

        $this->belongsTo('tree_id', 'Tree', 'tree_id', array(
            'foreignKey' => array('message' => 'The Tree does not exist.')
        ));
    }
}

<?php

class TestController extends Phalcon\Mvc\Controller {
    public function createAction() {

        \Phalcon\Mvc\Model::setup(array(
            'notNullValidations' => false
        ));

        $branch = new Branch();
        $branch->branch_id = 'branch1';
        $branch->name = 'branch 1';
        $branch->tree = null;
        $branch->tree_id = null;

        if (!$branch->save())
            foreach ($branch->getMessages() as $message) {
                echo "Message: ", $message->getMessage() . '<br/>';
                echo "Field: ", $message->getField() . '<br/>';
                echo "Type: ", $message->getType() . '<br/>';
            }
    }
}


4.5k

I see. Is there workaround for this?



7.4k
Accepted
answer
edited Apr '14
  • an workaround solution may be this:
  • $this->belongsTo("id", "tabel", "id", array('reusable' => true, "alias"=>'table'));
  • instead of:
  • $this->belongsTo("id", "tabel", "id", array('foreignKey'=>true,"alias"=>'table'));


32.5k

If you have a correct DB structure with defined foreign keys, this will fail. ORM will skip foreign key check but MySQL will NOT.



7.4k

im mysql it is set to allow NULL in the foreign key column?



11.1k

the best solution would be to implement a Relation::ACTION_SETNULL

You can setup the foreign key to allow nulls:

$this->belongsTo('tree_id', 'Tree', 'tree_id', array(
            'foreignKey' => array('message' => 'The Tree does not exist.', 'allowNull' => true)
));

This simply doesn't work for me. Is it 'allowNull' or 'allowNulls'? None of them lets me insert NULL values into the database though. I put this in my Newsagent model:

$this->belongsTo("partner_id", "Partner", "id", array( "foreignKey" => array( "allowNulls" => true, "message" => "referencing" ) ));

and this in my Partner model:

$this->hasMany("id", "Newsagent", "partner_id", array("foreignKey" => array( 'message' => 'referenced' )));

MySQL lets me give the partner_id field NULL value manually, but this piece of code still doesn't let me insert NULLs:

$newsagent = Newsagent::findFirst(array( "conditions" => "code = ?1", "bind" => array(1 => $code) ));

$newsagent->partner_id = null; if ($newsagent->update() == false) { foreach ($newsagent->getMessages() as $message) { echo $message, "\n"; } }

It outputs: referencing