Oracle

In Oracle, the CHECK constraint is used to ensure that all values in a column satisfy a specific condition. This is useful when you want to enforce domain integrity by limiting the values that can be stored in a column. The CHECK constraint helps to maintain data consistency by rejecting values that evaluate to false for the specified condition.

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

1. Adding a CHECK Constraint to the author Table

   • Let’s assume that for each new author, we want to ensure their age is at least 18 before they are added to the library system.

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

2. Defining a CHECK Constraint in the books Table

   • In the books table, the published_date column could have a CHECK constraint to ensure that the date is not in the future, meaning published_date cannot be greater than 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 CHECK (published_date <= SYSDATE),
       PRIMARY KEY (book_id),
       FOREIGN KEY (author_id) REFERENCES author(author_id)
   );
   

3. Using a CHECK Constraint in the library Table

   • For the library table, you can check that the location is one of the valid regions, such as 'North', 'South', 'East', or 'West'.

   CREATE TABLE library (
       library_id   NUMBER GENERATED BY DEFAULT AS IDENTITY,
       branch_name  VARCHAR2(100) NOT NULL,
       location     VARCHAR2(100) CHECK (location IN ('North', 'South', 'East', 'West')),
       PRIMARY KEY (library_id)
   );
   

4. Setting CHECK Constraints for Memberships

   • In the membership table, the status of the membership can be restricted to specific values like 'Active', 'Inactive', or 'Suspended'.

   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) CHECK (status IN ('Active', 'Inactive', 'Suspended')),
       PRIMARY KEY (membership_id)
   );
   

5. Using CHECK in the rentals Table

   • In the rentals table, you could set a CHECK constraint for the rental_status column to ensure it only accepts 'Pending', 'Returned', or 'Overdue'.

   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) CHECK (rental_status IN ('Pending', 'Returned', 'Overdue')),
       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 DEFAULTconstraint 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: DEFAULT constraint allows tables to be flexible and reduces the burden on users to input every column manually.

The DEFAULTconstraint 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