Use service builder to create table with constraints like this

[SHI HONG CHIN, modificado hace 7 años.]

I am using Liferay 6.2 with PostgreSQL database.

Lets look at the following example table:

col1 col2 col3
---- ---- ----
1    a    2016-08-11	
2    a    2016-08-12
3    a    2016-08-13
4    a    NULL
5    b    2016-07-11
6    b    2016-07-12
7    b    NULL
8    c    2016-08-25
9    c    2016-08-26
10   c    2016-08-27

Assume col1 is the primary key. For each distinct col2 value, only one row with NULL value in col3 is allowed. As you can see from the above example table, where col2 = 'c', there is no null value in col3. Where col2 = 'b' or 'a', there is one row which col3 is null. Application or portlet can insert ONLY ONE row with col2 = 'c' and col3 = NULL. But, application or portlet cannot insert any row with col2 = either 'a' or 'b' while col3 = null. When updating the table, where col2 = either 'a' or 'b' while col3 is not null, application or portlet cannot change col3 to null.

Using Liferay's Service Builder, is it possible to create table with above constraint?

I am willing to migrate to another database if PostgreSQL does not support.

[David H Nebinger, modificado hace 7 años.]

SB cannot do this because it is not something you can define using standard relational algebra. If you can't do it in pgsql in a "create table" statement, SB certainly can't do it.

Now, if you're asking if you can build this kind of business logic in, well sure. in the addXxx() and updateXxx() methods for your XxxLocalServiceImpl class, you can do a query to find the count of records with the col2 value and null for col3 and block the save if your constraint is not satisfied.






Come meet me at the LSNA!