Oracle

In Oracle, the concept of auto-incremented columns is typically achieved using the IDENTITY column or a combination of sequences and triggers. Auto-increment is used to generate unique values automatically for a column, often used for primary keys, ensuring that every new row inserted has a distinct identifier. The IDENTITY option simplifies this process by auto-generating a value, eliminating the need for a manual sequence or trigger setup.

Below are examples demonstrating the use of auto-increment (IDENTITY) for tables related to a library system, including authors, books, libraries, memberships, and rentals.

1. Using Auto Increment in the author Table

   • In the author table, the author_id is an automatically incremented identifier.

   CREATE TABLE author (
       author_id   NUMBER GENERATED BY DEFAULT AS IDENTITY,
       first_name  VARCHAR2(50) NOT NULL,
       last_name   VARCHAR2(50) NOT NULL,
       bio         CLOB,
       PRIMARY KEY (author_id)
   );
   

2. Defining Auto Increment in the books Table

   • The book_id in the books table can also be auto-incremented using the IDENTITY clause, ensuring that every book entry has a unique identifier.

   CREATE TABLE books (
       book_id        NUMBER GENERATED BY DEFAULT AS IDENTITY,
       title          VARCHAR2(150) NOT NULL,
       author_id      NUMBER NOT NULL,
       isbn           VARCHAR2(20),
       published_date DATE,
       PRIMARY KEY (book_id),
       FOREIGN KEY (author_id) REFERENCES author(author_id)
   );
   

3. Auto Increment for the library Table

   • In the library table, the library_id is auto-incremented to uniquely identify each library branch.

   CREATE TABLE library (
       library_id   NUMBER GENERATED BY DEFAULT AS IDENTITY,
       branch_name  VARCHAR2(100) NOT NULL,
       location     VARCHAR2(100),
       PRIMARY KEY (library_id)
   );
   

4. Using Auto Increment in the membership Table

   • The membership_id for each new member in the membership table is automatically incremented.

   CREATE TABLE membership (
       membership_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
       member_name   VARCHAR2(100) NOT NULL,
       join_date     DATE DEFAULT SYSDATE,
       status        VARCHAR2(20) DEFAULT 'Active',
       PRIMARY KEY (membership_id)
   );
   

5. Auto Increment in the rentals Table

   • In the rentals table, the rental_id is auto-incremented to ensure every rental record has a unique ID.

   CREATE TABLE rentals (
       rental_id      NUMBER GENERATED BY DEFAULT AS IDENTITY,
       book_id        NUMBER NOT NULL,
       member_id      NUMBER NOT NULL,
       rental_date    DATE NOT NULL,
       return_date    DATE,
       rental_status  VARCHAR2(20) DEFAULT 'Pending',
       PRIMARY KEY (rental_id),
       FOREIGN KEY (book_id) REFERENCES books(book_id),
       FOREIGN KEY (member_id) REFERENCES membership(membership_id)
   );
   

Additional Notes:

• IDENTITY Columns: Starting from Oracle 12c, the IDENTITY column is the simplest way to implement auto-increment functionality, removing the need for sequences and triggers.
• GENERATED BY DEFAULT: This option allows the user to specify a value for the column during insertion. If no value is provided, Oracle will automatically generate the next value.
• Simplifies Primary Key Generation: Auto-increment makes it easier to manage unique keys, particularly for tables that require unique identifiers for each record.

By using the IDENTITY column, Oracle allows for easy auto-increment functionality, making it ideal for creating unique identifiers in a variety of tables without the need for additional configuration or manual entry.

To gain complete access, login with gmail or outlook, no need of signup. click here