Can we write Entity Bean for a table which does not have a Primary Key defined?

[Gurumurthy Ramamurthy]

Hi,
Can we write an Entity Bean for a table which does not have a Primary Key defined?
If yes, what mechanism we apply to a result of more than one rows for a particular query?
Regards,
Guru

[Gurumurthy Ramamurthy]

Guys,
I think it is possible with EJB-QL...any answers....
Guru

[Pradeep bhatt]

How?? Every entity has a primary key.

Originally posted by Gurumurthy Ramamurthy:
Guys,
I think it is possible with EJB-QL...any answers....
Guru

[Lasse Koskela]

I have a strong feeling that it's not possible to have a CMP entity bean with a primary key that is not mapped to a database field. With BMP it's possible because you write the JDBC code and decide the logic for storing the data.
EJB-QL will not solve anything because the problem is encountered already in the "write" part...

[Pradeep bhatt]

With BMP it's possible because you write the JDBC code and decide the logic for storing the data.

I WAS JUST WONDERING what value needs to be passed to findByPrimaryKey method ? What will be the return value of the finder method ..will it be a generic java.lang.Object ?
[ August 28, 2003: Message edited by: Pradeep Bhat ]

[Lasse Koskela]

The same restrictions apply for the primary key class as with CMP. I guess java.lang.Object would probably be a safe choice.

[Pradeep bhatt]

I am not sure which entity will the findByPrimarymethod return when java.lang.Object is passed and in this case where the entity has no primary key.

What will the ejbStore/ejbLoad method do ? Which row it is going yo update. When we do ejbContext.getPrimaryKey() it will return me java.lang.Object and this object does not help me to find me the row to be loaded/updated, isn't.
Entity beans without primary keys are not workable.
:roll:
[ August 28, 2003: Message edited by: Pradeep Bhat ]

[Lasse Koskela]

I totally agree that entity beans should not be used without real primary keys. However, I have a feeling that one could pull it off technically using BMP, even though it wouldn't make much sense (for example, you'd be forced to return "any" record from ejbFindByPrimaryKey() and the container would probably make a mess trying to cache stuff using the primary key).

[Dana Hanna]

Is there any reason that the table has no primary key? Perhaps we could address that issue...

[Gurumurthy Ramamurthy]

It is not necessary that you should have a primary key for every table (technically). If you are embedding your JDBC code inside the entity bean, this is possible and you give the composite key (collection of more than one field) as the parameter to the PrimaryKey class. So, from my point of view, you can write entity bean to a table which does not have any Primary Key (whether it is CMP or BMP).
Guru

[Pradeep bhatt]

So, from my point of view, you can write entity bean to a table which does not have any Primary Key (whether it is CMP or BMP).

How did you conclude this ? I suggest you have a column for a primary key in the table may be you use a sequence for this.
[ August 28, 2003: Message edited by: Pradeep Bhat ]

[Lasse Koskela]

Guru's reasoning sounds valid. What I'm not sure about it is whether there are any issues with having a "primary key constraint" in the EJB Container but not in the database. I guess it's possible that the EJB Container might check for a duplicate primary key before going to the database (which would prevent you from adding identical records into the table even though the data model allows it), but I really have no idea whether any implementation out there does this.

[Pradeep bhatt]

It is not required that table have a primary key as long as entity ejb primary key is able to uniquely identify the row.

[Chris Mathews]

Originally posted by Lasse Koskela:
Guru's reasoning sounds valid. What I'm not sure about it is whether there are any issues with having a "primary key constraint" in the EJB Container but not in the database. I guess it's possible that the EJB Container might check for a duplicate primary key before going to the database (which would prevent you from adding identical records into the table even though the data model allows it), but I really have no idea whether any implementation out there does this.

Regardless... the big question is why would you want to do this? This could potentially sacrifice the integrity of your data, especially if external applications or people modify the database, and for no good reason. Until someone comes up with a reason as to why this would be beneficial then I will continue to think that this is an extremely bad idea and one to surely piss off every DBA in your company. Don't leave the data integrity up to the EJB Container, it belongs in the database and it is more efficiently applied there.
[ August 29, 2003: Message edited by: Chris Mathews ]

[Ajay Hatkar]

if database table have any column which has unique constraint, is it possible to have CMP based on it ?

[Lasse Koskela]

Yes you can. The field doesn't have to be "primary key" in the database (as in "CONSTRAINT PRIMARY KEY my_id_field" in the table creation script).

[Ajay Hatkar]

I think it may not work for in all situation.
If unique column contains null value ( it's unique right and only one record may have null value ), what primary key object container will return ?

[Pradeep bhatt]

Originally posted by Ajay Hatkar:
If unique column contains null value ( it's unique right and only one record may have null value ), what primary key object container will return ?

I am not sure about this.. I feel it is not going to work..why not just try using the you app server..and post the result here

[Tejpal Singh]

Originally posted by Gurumurthy Ramamurthy:
It is not necessary that you should have a primary key for every table (technically). If you are embedding your JDBC code inside the entity bean, this is possible and you give the composite key (collection of more than one field) as the parameter to the PrimaryKey class. So, from my point of view, you can write entity bean to a table which does not have any Primary Key (whether it is CMP or BMP).
Guru

Guru i think it is a bad design stratgy to do that since the same database table may be accessed by another object or application, in that case the data integrity become a major issue.
Anyways i feel it may not be practically workable though it sounds so in theory....
Generally where possible you should use the primary key functionality to
ensure that all rows in a table are unique.
Sometimes for convenience a surrogate key is assigned to rows based on a
sequence. In this case this becomes the primary key to the table but does
not ensure uniqueness of data between rows in the table. It is therefore
necessary to supplement the surrogate key with a unique index (key) on the
table which does ensure uniqueness of data in each row.
Cheers!!