Where are the foreign keys?

[Terry Mueller, modificado 15 Anos atrás.]

I can't see any FK constraints on tables via Toad. Am I missing something or doesn't Liferay use them?

Thanks,
Terry

[Robert Folkerts, modificado 13 Anos atrás.]

Nope. There are no foreign keys. Relationships are only enforced in Java.

[Hitoshi Ozawa, modificado 13 Anos atrás.]

Robert Folkerts:

Nope. There are no foreign keys. Relationships are only enforced in Java.

And that's how it should be to have flexible models. SpringFramework documentation may be helpful.

[David H Nebinger, modificado 13 Anos atrás.]

Hitoshi Ozawa:

And that's how it should be to have flexible models.

I take exception to that statement. There are ways to incorporate flexibility into your data model without sacrificing the foreign keys...

Foreign keys ensure data (referential) integrity. Foreign keys help manage parent-child relationships (delete the parent and you can also delete the children). Foreign keys allow modelling tools to pull in the tables and graphically show the relationships between them. Foreign keys are used by reporting tools to help build queries that join tables appropriately. Foreign keys ensure that any updates to the data adhere to the referential constraints, whether the update is being done by your java code or someone else's {insert alternative language here} code.

Sure, perhaps the 'owner id' columns should not be foreign keys so it can refer to a user, a group, an organization, etc. But that doesn't mean that it's okay to throw out foreign keys altogether... And it also doesn't mean that there aren't ways to develop the data model that would allow for this kind of dynamic reference requirement while still ensuring data integrity through foreign keys...

[Jens Göring, modificado 13 Anos atrás.]

Nicely said, David.

The way liferay ensures referential integrity is not reliable - we experience occasional data loss and orphaned child records which cannot happen if you have foreign key constraints. This is a huge issue for us and one of the reasons we move away from using the service builders.

[Hitoshi Ozawa, modificado 13 Anos atrás.]

Foreign keys ensure data (referential) integrity. Foreign keys help manage parent-child relationships (delete the parent and you can also delete the children). Foreign keys allow modelling tools to pull in the tables and graphically show the relationships between them. Foreign keys are used by reporting tools to help build queries that join tables appropriately. Foreign keys ensure that any updates to the data adhere to the referential constraints, whether the update is being done by your java code or someone else's {insert alternative language here} code.

Just have to use a different set of tools.

BTW, I've been Oracle certified all over.
I'm also localizing MySQL Workbench to Japanese. :-)

[David H Nebinger, modificado 13 Anos atrás.]

Just have to use a different set of tools.

Changing tools is not always the developer's option. Often times the tools (in the enterprise) depend on many things, and the developer's opinion might not be one of them.

But even if you take the modelling tools and reporting tools off the table, there's still (IMHO the most significant reason) data integrity, parent-child relationship management, and foreign code access.

I'm oracle certified also, but that's neither here nor there. I have never worked with a professional DBA who would ever advocate creating a database w/o specifying the FK relationships. There are times when individual FKs must be excluded from a model (because of data relationships or performance reasons), but never an entire database, especially one of significant size and/or importance to an enterprise.

[Hitoshi Ozawa, modificado 13 Anos atrás.]

I think you're misunderstanding the basic concept behind Liferay. Liferay is a portal framework and not an application framework. It just offers a portal interface to applications. Even if you do deploy an application in the same container as Liferay, mixing Liferay table with custom application table is not recommended.
Hooks and ext plugins features are there so developers can customize Liferay without modifying the Liferay source code itself so Liferay can be more easily updated. Modifying Liferay database defeats this. In all, you shouldn't be touching the Liferay database directly unless you're developing Liferay itself.

You shouldn't be looking at the database. You should be looking at the abstract model and their api's.

[David H Nebinger, modificado 13 Anos atrás.]

Any application that relies on a set of tables in a database, whether it is a framework or a dedicated application system, still has relationships that must be satisfied to function properly, and I don't think that Liferay is any different.

Extending or overriding the message board functionality, for example, would (should) never lead to violating certain parent/child relationships, i.e. the MBMessageFlag table has a permanent relationship to MBMessage. Sure there may be a need to add functionality or replace functionality, but the relationship would still need to be maintained...

I don't advocate accessing the Liferay database directly, and I'm not sure why the OP was looking at the tables in toad to begin with.

I admit that I have looked at the tables in order to fix errors (data errors, probably as a result of code errors). In the last instance, our 5.2.3 installation was set up to import from AD; through a comedy of errors, an upgrade of MS Exchange resulted in duplicated email addresses in AD, and Liferay started showing exceptions every time the auto-sync to AD fired. Removing the duplicates from AD fixed most of the problems except one - an existing orphaned Group_ record w/ a friendlyURL that prevented the sync from completing properly (an existing orphan Group_ record had a friendlyURL that matched what was being generated by the user creation process during import). Sure, there may have been someplace in Liferay that I might have been able to find the group to delete it, but I did a quick delete statement in the database and all was well.

Had there been FKs set up to begin with, I'm pretty sure that the LDAP/AD sync process would never have failed in the first place (the removal of the user from AD would have had the sync process delete the User_ record which would have correctly cascaded to the Group_ table all on it's own, leaving no orphans).

The point to all of this is that any application can break when the fundamental relationships that it expects to be honored are violated. FKs help to prevent those violations from happening, even for portal frameworks.

[Hitoshi Ozawa, modificado 13 Anos atrás.]

David, if you're attending the upcoming Liferay conference, let's talk it over a drink then.
I'm trying to get my company to approve to let me go.

[David H Nebinger, modificado 13 Anos atrás.]

Hitoshi Ozawa:

David, if you're attending the upcoming Liferay conference, let's talk it over a drink then.
I'm trying to get my company to approve to let me go.

I'll be at the Liferay Symposium coming up in May! Looking forward to hooking up w/ a lot of folks I've only spoken with online...

[Mike Harris, modificado 12 Anos atrás.]

Another thing that we discovered is that even if you want to have integrity in your own tables, if you use the service builder, it's not possible for relations that can be NULL.
Liferay's service builder use the long type for entities, which can't be set to null.

Is there a way to change that? Or should we use something else than LR's service builder to generate our entities if we want integrity in our data?

[Esa Hekmat, modificado 11 Anos atrás.]

I'm interested to know is there any FK in Liferay Enterprise Edition database or not?

[Roger Thomas, modificado 11 Anos atrás.]

While this is an old thread, I can put forward one key reason why FKs can not be defined at the database layer.

Many areas of the standard code base can be overridden with third party code so its possible to replace something like the user model with a different model. Such a model may not need or support the current user table in the liferay schema. If FKs are inforced at the DB level information would still have to be placed into the user table.

Roger

[David H Nebinger, modificado 11 Anos atrás.]

Replacing Liferay entities w/ your own is a no-no.

You should either use Expandos or your own custom entities, but never ever replace Liferay entities. You'll box yourself in to a specific Liferay version and make dealing w/ upgrades a difficult and painful process.

[Atif Hussain, modificado 10 Anos atrás.]

David H Nebinger:

Hitoshi Ozawa:

David, if you're attending the upcoming Liferay conference, let's talk it over a drink then.
I'm trying to get my company to approve to let me go.

I'll be at the Liferay Symposium coming up in May! Looking forward to hooking up w/ a lot of folks I've only spoken with online...

Can you tell the summary of your conversation you had specially "before the drink"? ;)
What you guys suggest? Relationships should be maintained on code level or database level in Liferay Portlet development?

Regards,
Atif Hussain

[David H Nebinger, modificado 10 Anos atrás.]

Never actually hooked up; Hitoshi had to cancel at the last minute...

So here's where I'm at on this today. If you are doing internal development of enterprise services, I would recommend letting your DBA set up the tables (including whatever foreign keys are necessary). You can then define your services against those tables and not worry about the FK issues. Note however, that SB does not handle the relationships very well so it's better treating the columns as IDs rather than as entities.

If you are building plugins for deployment within the marketplace, then you should depend upon SB doing all of the table building (especially since you're going to be cross-db compatible).

In either case, whether the DB may be verifying your FK values or not, it will always be up to the code to maintain the relationships.

Personally I still think that FKs are important and have a great deal of value, from Liferay's perspective it would definitely make handling table updates difficult so I get why they avoid them.

But really you can boil it down to whatever the entities are. All of the Liferay entities, while important, are not really that mission critical in an enterprise. Whether it is a blog entry, a wiki page, etc. if a single record is dropped your business won't grind to a halt. For those records that are mission critical, you should pursue the FK relationships as you have a lot more riding on them.

[Jens Göring, modificado 13 Anos atrás.]

Hitoshi Ozawa:

I think you're misunderstanding the basic concept behind Liferay. Liferay is a portal framework and not an application framework. It just offers a portal interface to applications. Even if you do deploy an application in the same container as Liferay, mixing Liferay table with custom application table is not recommended. Hooks and ext plugins features are there so developers can customize Liferay without modifying the Liferay source code itself so Liferay can be more easily updated. Modifying Liferay database defeats this. In all, you shouldn't be touching the Liferay database directly unless you're developing Liferay itself.

Liferay is actively marketed as an application framework, even on the "liferay overview" page on liferay.com. Liferay also recommends mixing Liferay tables with custom tables in the development guide.

Overall I don't think that we use Liferay in a way it is not intended to be used. It would be nice if Liferay had an option to create Foreign Key Constraints in the service builder. This would achieve a huge gain in data consistency without much effort - for custom portlet code and for liferay itself.