Making Read-Only Tables- Tables and Constraints
You can place individual tables in read-only mode. Doing so prevents any INSERT, UPDATE, or DELETE statements from running against a table. An alternate way to do this is to make the tablespace read-only and use this tablespace for the tables that are static for read-only.
There are several reasons why you may require the read-only feature at the table level:
• The data in the table is historical and should never be updated in normal circumstances.
• You are performing some maintenance on the table and want to better determine whether any change while it is being updated.
- You want to drop the table, but before you do, you want to better determine whether any users are attempting to update the table.
Use the ALTER TABLE statement to place a table in read-only mode:
You can verify the status of a read-only table by issuing the following query:
SQL> select table_name, read_only from user_tables where read_only=’YES’;
To modify a read-only table to read/write, issue the following SQL:
SQL> alter table inventory read write;
Using an Identity Column
An autoincrementing (identity) column with the GENERATE AS IDENTITY clause. This example creates a table with the primary key column that will be automatically populated and incremented:
SQL> create table inventory
(inv_id number generated as identity, inv_desc varchar2(30));
Table created.
SQL>alter table inventory add constraint inv_pk primary key (inv_id);
Now, you can populate the table without having to specify the primary key value:
- Book
- Table
When you create an identity column, Oracle automatically creates a sequence and associates the sequence with the column. You can view the sequence information in USER_SEQUENCES.
USER_TAB_COLUMNS will also identify the identity columns.
When creating a table with an identity column (such as in the prior example), you can’t directly specify a value for the identity column, such as when you try this:
SQL> insert into inventory values(3, ‘Chair’);
ORA-32795: cannot insert into a generated always identity column
To avoid this error and create a table that will allow those occasional inserts into an identity column, the following syntax can be used to create the table:
SQL> create table inventory
(inv_id number generated by default on null as identity, Inv_desc varchar2(30));
Because the underlying mechanism for populating an identity column is a sequence, you have some control over how the sequence is created (just like you would if you manually created a sequence). For instance, you can specify at what number to start the sequence and by how much the sequence increments each time. This example specifies that the underlying sequence starts at the number 30 and increments by two each time:
SQL> create table inventory
(inv_id number generated as identity (start with 50 increment by 2), Inv_desc varchar2(30));
There are some caveats to be aware of when using autoincrementing columns:
• Only one per table is allowed.
• They must be numeric.
• They cannot have default values.
• NOT NULL and NOT DEFERRABLE constraints are implicitly applied.
• CREAT TABLE … AS SELECT will not inherit identity column properties.
Also, keep in mind that after inserting into a column that is autoincremented, if you issue a rollback, the transaction is rolled back, but not the autoincremented values from the sequence. This is the expected behavior of a sequence. You can roll back such an insert, but the sequence values are used and gone.
