Oracle

In Oracle, the UNIQUE constraint ensures that all values in a column (or group of columns) are distinct, meaning no duplicate values are allowed. This constraint is typically used to enforce data integrity, ensuring that key data, such as identification numbers, remains unique across the table. Unlike a primary key, a table can have multiple UNIQUE constraints, and NULL values are allowed unless otherwise restricted.

Below are examples of how to use the UNIQUE constraint in tables for a library system that tracks authors, books, libraries, memberships, and rentals.

1. Using UNIQUE Constraint in the author Table

   • In the author table, we may want to ensure that no two authors have the same combination of first_name and last_name. This prevents duplicate author entries.

   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,
       CONSTRAINT unique_author_name UNIQUE (first_name, last_name)
   );
   

2. Defining a UNIQUE Constraint in the books Table

   • In the books table, the isbn (International Standard Book Number) must be unique, as each book is identified by its specific isbn.

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

3. Setting a UNIQUE Constraint in the library Table

   • The branch_name should be unique in the library table, as no two library branches can have the same name within the system.

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

4. Enforcing Uniqueness in the membership Table

   • For the membership table, you could enforce uniqueness on the combination of member_name and join_date to prevent duplicate records for the same member joining on the same date.

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

5. Applying a UNIQUE Constraint in the rentals Table

   • In the rentals table, to avoid duplicate rental entries for the same book and member, you can add a UNIQUE constraint on book_id and member_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 NULL,
       rental_status  VARCHAR2(20) DEFAULT 'Pending',
       CONSTRAINT unique_book_member UNIQUE (book_id, member_id),
       PRIMARY KEY (rental_id),
       FOREIGN KEY (book_id) REFERENCES books(book_id),
       FOREIGN KEY (member_id) REFERENCES membership(membership_id)
   );
   

Additional Notes:

• Multiple UNIQUE Constraints: A table can have multiple UNIQUE constraints on different columns or combinations of columns.
• NULL Values: Unlike a primary key, columns with a UNIQUE constraint can accept NULL values, but only one NULL is allowed per column.
• Data Integrity: The UNIQUE constraint is crucial in maintaining data integrity by preventing the duplication of key information in your tables.

The UNIQUE constraint is a powerful tool for ensuring that key pieces of data remain distinct across a table, providing flexibility for both individual and composite columns.

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