Oracle

In Oracle, columns in a table can be defined as either NULL or NOT NULL, which determines whether a column can store NULL (empty or unknown) values. By default, a column allows NULL values unless explicitly marked as NOT NULL. Using NOT NULL is useful when you want to enforce that a certain field must always contain data, while NULL is useful for optional fields. Understanding how and when to use NULL and NOT NULL is essential for maintaining data integrity.

Below are examples of how to use NULL and NOT NULL with tables designed for a library system.

1. Defining a Column as NOT NULL in the author Table

   • The first_name and last_name columns must always have values when creating or updating rows in the author table, so they are marked NOT NULL.

   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
   );
   

2. Allowing NULL Values in the books Table

   • Some fields like isbn (International Standard Book Number) might be optional, so they can be defined as NULL to indicate that these values are not required.

   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,
       PRIMARY KEY (book_id),
       FOREIGN KEY (author_id) REFERENCES author(author_id)
   );
   

3. Using NOT NULL in the library Table

   • The branch_name is a required field and should never be left empty, while the location can be left as NULL.

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

4. Defining NOT NULL Constraints in the membership Table

   • In this case, the member_name and join_date are required fields and cannot be NULL, but preferences can be left NULL if not provided.

   CREATE TABLE membership (
       membership_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
       member_name   VARCHAR2(100) NOT NULL,
       join_date     DATE NOT NULL,
       preferences   CLOB NULL,
       PRIMARY KEY (membership_id)
   );
   

5. Handling NULL in the rentals Table

   • For the rentals table, you might want to allow return_date to be NULL if the book has not been returned yet.

   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,
       PRIMARY KEY (rental_id),
       FOREIGN KEY (book_id) REFERENCES books(book_id),
       FOREIGN KEY (member_id) REFERENCES membership(membership_id)
   );
   

Additional Notes:

• NULL: A column defined as NULL allows empty or unknown values, which can be useful for optional data.
• NOT NULL: A column defined as NOT NULL ensures that a value must always be provided during INSERT or UPDATE operations.
• Data Integrity: Using NOT NULL constraints helps enforce data integrity by ensuring that important fields are always filled.

The NULL vs NOT NULL constraints are simple yet powerful tools for controlling the presence of data in your tables, allowing flexibility where needed and ensuring consistency where required.

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