0

Possible Duplicate:
How to create id with AUTO_INCREMENT on Oracle?

I am creating my first application that uses the Entity Framework to access data in an Oracle Database. The Database consists of 16 different related tables. So far I have created a prototype database and loaded some sample data using Oracle SQL Developer. The application is successfully reading the data from the selected tables as well as the related tables.

Now it is time to start adding data from the application. We have had several discussions about how to do that. From the examples I have seen this process seems straight-forward but I do have a question regarding Primary Key generation.

For most (if not all) of the Tables, the Primary Key will be a number with no significance other than i's unique use as a relational Key. This is because the many of the records have no specific field in them that is guaranteed to be unique. The question that comes up is how that Key is generated.

For databases I have worked with before, there was a concept of an auto-increment field type that can be used so the key would be automatically created by the Database. I have been informed by the DBA that Oracle does not have an auto-increment field type. He told me Oracle can support the Key Generation using a Sequence but that would need to be done in a Stored Procedure. On the other hand I am getting major push-back on the use of Stored Procedures. The general feeling is that none of the information on Entity Framework indicated this is needed and that Entity Framework should eliminate the need for Stored Procedures.

Can anyone clarify what I do (or what happens) regarding the Primary Key when I add a record to a Oracle Table using the Entity Framework?

Community
  • 1
  • 1
markshancock
  • 690
  • 2
  • 7
  • 25
  • Your DBA must have told you more than that... Simply put trigger + sequence; or simply a sequence if you include it in the insert statement (though that's not as safe). – Ben Nov 19 '12 at 21:15
  • Using sequence has to be on a stored procedure... Who told you that? `INSERT INTO TABLE(first_name,last_name,sequence_no) VALUES('Johnny', 'Bravo', seq_name.nextval);` will work perfectly even outside of a Stored Procedure, provided `seq_name` sequence already exists. – Anjan Biswas Nov 19 '12 at 22:30
  • Also, bear in mind that there could be possible gaps in a sequence. – Anjan Biswas Nov 19 '12 at 22:36
  • Thanks for the comments. I had not see How to create id with AUTO_INCREMENT on Oracle. Sorry this may be a duplicate. I am anxious to try these ideas out; but, as Murphy would have it, the Database server/connection seems to be down. Hopefully IT will get it up soon so I can play. – markshancock Nov 19 '12 at 23:22
  • BTW, What the key is and whether there are gaps are or not is not a problem for this application. | Also, DBA didn't tell me any more. Using the term DBA in this case is probably a pretty big stretch. It seems he isn't much further down the learning cure than I am. He has just done this once before and so he created the initial database. – markshancock Nov 19 '12 at 23:27

0 Answers0