I recently installed oracle 8i on a windows 98 platform and am now trying to establish the tables for a new database. First let me say that I am faily aware of the relational database concept but very new to oracle and putting the concept to work. Having said this:
I want to establish a table that should have a primary key (lets say account_ID) that is established automatically by the database everytime a new record is created. everytime a new record is created I want the database to automatically assign a new unique account_ID that can be used to identify the associated record. how do I design and establish the table with oracle 8i navigator? I would like the account_ID to be a 6 digit number and would like to start the first record with 100000 (second record 100001).
I have been trying to use the rowID (sounded right and the documentation doesn't shed much light on how to use it). When I try to input data into the table the column using type rowID says N/A. If I enter the remaining fields of theat record and try to insert a new record I get the following error
ORA-01400: cannot insert NULL into (column with type rowID)
The rowid is something that you can use for query and not for inserting in. This rowid gets assigned by oracle internally. The best way to establish your needs is use the SEQUENCE and then insert those sequence number for your primary key.
thanks so much - that was the concept I was looking for. now I know how I establish a sequence object. what do I have to do to use the sequence with the table. I assume I establish the ID column as number type, size 6, scale 0, no default, primary key 1, no foreign key. Sorry for being such a beginner.