Foren

Liferay not able to insert foreign key value as null

Varun Arya, geändert vor 6 Jahren.

Liferay not able to insert foreign key value as null

New Member Beiträge: 24 Beitrittsdatum: 02.03.17 Neueste Beiträge
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.
thumbnail
David H Nebinger, geändert vor 6 Jahren.

RE: Liferay not able to insert foreign key value as null

Liferay Legend Beiträge: 14914 Beitrittsdatum: 02.09.06 Neueste Beiträge
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, geändert vor 6 Jahren.

RE: Liferay not able to insert foreign key value as null

New Member Beiträge: 24 Beitrittsdatum: 02.03.17 Neueste Beiträge
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.
thumbnail
David H Nebinger, geändert vor 6 Jahren.

RE: Liferay not able to insert foreign key value as null

Liferay Legend Beiträge: 14914 Beitrittsdatum: 02.09.06 Neueste Beiträge
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, geändert vor 6 Jahren.

RE: Liferay not able to insert foreign key value as null

New Member Beiträge: 24 Beitrittsdatum: 02.03.17 Neueste Beiträge
Hi David,

Thanks for your valuable advise. But i have the requirement. And i think liferay does not support this thing.
thumbnail
David H Nebinger, geändert vor 6 Jahren.

RE: Liferay not able to insert foreign key value as null

Liferay Legend Beiträge: 14914 Beitrittsdatum: 02.09.06 Neueste Beiträge
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!
thumbnail
Jack Bakker, geändert vor 6 Jahren.

RE: Liferay not able to insert foreign key value as null

Liferay Master Beiträge: 978 Beitrittsdatum: 03.01.10 Neueste Beiträge
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, geändert vor 6 Jahren.

RE: Liferay not able to insert foreign key value as null

New Member Beiträge: 24 Beitrittsdatum: 02.03.17 Neueste Beiträge
Hi David,

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