Oracle

In Oracle, the DEFAULT constraint is used to automatically assign a default value to a column if no value is provided during an INSERT operation. This is useful when you want to ensure that specific fields have a default value, avoiding NULL values or providing common defaults for optional fields. The DEFAULT constraint helps to maintain data consistency without requiring the user to specify values for every column when adding records.

Below are examples of how to use the DEFAULT constraint for tables designed for a library system.

1. Adding a DEFAULT Constraint to the author Table

   • Let’s assume that for each new author, if no value is provided for the bio column, a default message is inserted.

   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 DEFAULT 'Bio not available',
       PRIMARY KEY (author_id)
   );
   

2. Defining a DEFAULT Value in the books Table

   • In the books table, the published_date column could default to the current date if no value is provided, indicating the book was published on today’s date.

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

3. Using a DEFAULT Constraint in the library Table

   • For the library table, if no specific location is provided for a new library branch, it defaults to ‘Unknown Location’.

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

4. Setting Default Values for Memberships

   • In the membership table, the join_date could default to the current date, and the status of the membership could default to ‘Active’.

   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. Using DEFAULT in the rentals Table

   • In the rentals table, you could set a default value for the rental_status column to ‘Pending’ when a new rental record is inserted.

   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 NULL,
       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:

• Automatic Values: The DEFAULT constraint is useful for assigning automatic values to columns when no specific data is provided during an INSERT.
• Data Integrity: By setting default values, you help maintain data consistency and avoid unnecessary NULL values in important columns.
• Flexibility: The DEFAULT constraint allows tables to be flexible and reduces the burden on users to input every column manually.

The DEFAULT constraint is a handy tool for streamlining data entry and ensuring that key columns always have valid values, even if some are not explicitly provided by users.

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