Abhay Agarwal wrote:Again this approach do not sound good as we have made composite key which contains every column of table. Not good.
Emanuele Mazzante wrote:
Normally I would execute a select query on the db and if no record are returned, I insert the data. In my small test program may work, but I don't think that this method is suitable in a project which there are many connected users. Between the search and the insertion can be created the same record type.
K. Tsang CEng MBCS PMP PMI-ACP OCMJEA OCPJP
K. Tsang wrote:Given the person table has a auto-increment key (person_id) then you can check for uniqueness in your code. For example, in the Person class have the equals() and hashcode() methods using the other 4 attributes. If the equals() method returns true, then there is a duplicate. However, to do this in code, you will need to loop all records in table. If doing in the SQL side, you can use a where clause to check if the count is 1, if so then duplicate.
Emanuele Mazzante wrote:
)- Composite primary key: I can remove the auto increment Person_ID field and make a composite field with all the remaining fields. In this case I can catch duplicate SQL exception and also inform the user about it. But this way is considered a good design? May be too redundant to have a field that relates the values of other four fields? May adversely affect the performance?
Emanuele Mazzante wrote:
)- Stored procedure and trigger: I have not yet studied it, however, I do not know if this allows me to inform the user of the non-inclusion because of the attempted duplication.
)- It could be possible to make something like this: INSERT INTO People (field1, field2,....) VALUES (value1, value2,....) WHERE NOT EXIST (SELECT * FROM People WHERE field1 = value1 AND field2 = value2 AND....) or I said a very stupid thing?
Emanuele Mazzante wrote:
)- Composite primary key: I can remove the auto increment Person_ID field and make a composite field with all the remaining fields. In this case I can catch duplicate SQL exception and also inform the user about it. But this way is considered a good design? May be too redundant to have a field that relates the values of other four fields? May adversely affect the performance?
Marshall Blythe wrote:
Emanuele Mazzante wrote:
)- Stored procedure and trigger: I have not yet studied it, however, I do not know if this allows me to inform the user of the non-inclusion because of the attempted duplication.
)- It could be possible to make something like this: INSERT INTO People (field1, field2,....) VALUES (value1, value2,....) WHERE NOT EXIST (SELECT * FROM People WHERE field1 = value1 AND field2 = value2 AND....) or I said a very stupid thing?
I think these just shift the potential for a race condition from your Java app to the database because they both ultimately rely on a query being executed BEFORE the insert.
Maxim Karvonen wrote:
Not a composite field. Unique index on several fields. It is somewhat similar to "composite field", but is maintained by the DB.
Abhay Agarwal wrote:Does unique key keeps a combination of all fields unique OR uniqueness is maintained per field ?
No more Blub for me, thank you, Vicar.
Don't count your weasels before they've popped. And now for a mulberry bush related tiny ad:
Smokeless wood heat with a rocket mass heater
https://woodheat.net
|