Liferay not able to insert foreign key value as null

[Varun Arya，修改在7 年前。]

I am facing a issue in my liferay project. In my database, I have two tables parent and child and child has foreign key of its parent.

In my liferay i have two entities in my service builder(wihout any relationship).

parentId has Long type. Now if i am going to insert foreign key(parentId) as null. I am getting bellow error:

org.springframework.dao.DataIntegrityViolationException: Could not execute JDBC batch update; SQL [insert into child (parentId, status, isActive, modifiedBy, dateModified) values (?, ?, ?, ?, ?)]; constraint [null]; nested exception is org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update

so is there is any way to store null values in foreign key. there is not ant not null check in my table. a simple insert with null is saved successfully.

I have used these options:

<column name="parentId" type="long" convert-null="false" />

and

<column name="parentId" type="Long" convert-null="false" />

Both are saving 0 as a default value.

[David H Nebinger，修改在7 年前。]

Okay, maybe you don't know this, but if you have a foreign key to another table, the database is going to verify that the foreign key points to a valid record.

So in your case, to insert into child table, parentId must be a valid ID and an actual record from the parent table.

That's the whole purpose for foreign keys and referential integrity.






Come meet me at the 2017 LSNA!

[Varun Arya，修改在7 年前。]

Hi Devid,

Thanks for the reply. I know the same. But we can paas NULL value in foreign key column. As i mentioned in my question:
"there is not any not null check in my table. a simple insert with null is saved successfully." if i insert null value for foreign key in my child table mysql will not give any kind of error because it accepts NULL.

I have a case where may be there will not any parent of my child table.

Hope you understand my problem.

[David H Nebinger，修改在7 年前。]

Varun Arya:

I have a case where may be there will not any parent of my child table.

No, in a parent-child relationship the concept of a parentless child is invalid. I'm not talking about what mysql will and won't allow you to do, in the concept of relational algebra (which all relational databases are based upon), this is not allowed.

Drop the foreign key because it doesn't really apply.






Come meet me at the 2017 LSNA!

[Varun Arya，修改在6 年前。]

Hi David,

Thanks for your valuable advise. But i have the requirement. And i think liferay does not support this thing.

[David H Nebinger，修改在6 年前。]

They can give you a requirement that you have to walk on the ceiling. That doesn't mean it's possible.

I don't want that to sound harsh, but if someone is giving the requirements that doesn't truly understand parent/child and foreign key relationships in the database, they can and often will ask for things that just can't happen.








Come meet me at the 2017 LSNA!

[Jack Bakker，修改在6 年前。]

David H Nebinger:

They can give you a requirement that you have to walk on the ceiling. That doesn't mean it's possible.

Not even with Magneto boots ? (X-Men reference)

So in 6.2 the following is inserting 0 instead of null ?
<column name="parentId" type="Long" convert-null="false" />

[Varun Arya，修改在6 年前。]

Hi David,

We have decided to remove the foreign key. Thanks for the right advice.