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

Thanks,
Terry

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.

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...

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.

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. :-)

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.

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.

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.

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.

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.

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...

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?

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

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

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.