Creating Constraints

In the context of a relational database, a constraint is a rule that enforces data integrity and consistency. It acts like a set of guidelines that define what kind of data can be stored and how it can be manipulated in a table. Constraints are essential part of an RDBMS, as it helps maintain data integrity. This is where an RDBMS is distinguished from NoSQL databases.

Here are some common types of constrains:

• UNIQUE Constraint: 
• Ensure that all values in a particular column or set of columns are unique across the table.
• Unlike a primary key, a unique constraint allows NULL values, but if a value is present, it must be unique.
• NOT NULL Constraint:
• Requires that a column cannot have NULL values.
• Ensures that every row in the table must have a value in the specified column.
• PRIMARY KEY Constraint:
• Ensures that each row in a table is uniquely identified by a specific column or set of columns.
• It automatically enforces the uniqueness of values in the designated columns and does not allow NULL values.
• FOREIGN KEY Constraint: 
• Establishes a relationship between two tables by linking a column or set of columns in one table to the primary key in another table.
• Ensures referential integrity, meaning that values in the foreign key column(s) must match values in the corresponding primary key column(s) in the referenced table.
• CHECK Constraint: 
• Enforces a condition on the values allowed in a column.
• It allows you to specify a condition using logical expressions, ensuring that only values meeting that condition can be contained in the column.

Constrains are not considered standalone schema objects. Rather, they are elements associated with tables and are considered part of the table's schema definition. Thus, they do not have a distinct command like "CREATE CONSTRAINT." Instead, you can define a constraint in the process of table creation. For example:

In the query above, I defined the PRIMARY KEY and NOT NULL constraints as part of the authors table declaration. It is "in-line" with the column upon which the constraint is applied, meaning that I included each constraint specification after I declare a column. In this example, I have omitted the constraint names, and Oracle database assigned them automatically. Then a query is retrieving constraint names from the user_constraints table.

Alternatively, you can designate the name during table creation by adding the reserved word CONSTRAINT and its name assignment between the column data type and the constraint specification. For example:

In the figure above, I created a table with a PRIMARY KEY, NOT NULL, and CHECK constraint. In this example, they have assigned names PK_MEMBERS, NN_MEMBER_NAME, CHK_EMAIL, respectively. Then the query below shows the name and the type of the constraints. P stands for "Primary Key", while C stands for "Check." In Oracle's USER_CONSTRAINTS table, the CONSTRAINT_TYPE column represents different types of constraints. Specifically:

• C stands for checking.
• P stands for primary key.
• U stands for uniqueness.
• R stands for referencing.

So, when you see C in the CONSTRAINT_TYPE column, it indicates that the corresponding constraint is checking values. The NOT NULL constraint ensures that a column must have a value and cannot be NULL. Thus, it is represented as C in the USER_CONSTRAINTS table.

You can define constraints out-of-line in Oracle as well. In this way, constraints are defined separately from the column definition. Here's an example of defining constraints out of line:

The basic syntax for creating a constraint out of line is:

CONSTRAINT constraint_name CONSTRAINT_TYPE (column_name)  

For a referential constraint, you should also specify the name of the parent table and its primary key column name:

CONSTRAINT constraint_name CONSTRAINT_TYPE (column_name) 
REFERENCES parent_table(primary_key)  

Note: NOT NULL is different. The syntax for NOT NULL constraint is different from others. It cannot be created out-of-line. 

Adding Constraints with ALTER TABLE Statement

CREATE TABLE is not the only way to create a constraint. It can also be created using the ALTER TABLE statement. For example:

In the preceding code, we first created table transactions. Then, by using ALTER TABLE statement, we added 3 constraints on the table.

FOREIGN KEY Constraint Creation: ALTER TABLE vs CREATE TABLE

I personally find using ALTER TABLE statement to add foreign keys offer some pros in terms of modularity and simplified execution. Here's why:

• Modular Structure: Defining foreign keys with ALTER TABLE keeps table definitions focused and independent. Each table stands on its own, with dependencies explicitly established later. This separation of concerns promotes cleaner code and simplifies future modifications.
• Order Independence: With CREATE TABLE, ensuring the correct order for tables with foreign key dependencies can be cumbersome, particularly for complex relationships. ALTER TABLE eliminates this headache. You can build all tables first, then seamlessly add foreign keys, regardless of their direction or nesting.
• Avoiding CREATE TABLE Limitations: Certain scenarios, like combining table creation and data population with AS SELECT, don't allow foreign key constraints in CREATE TABLE. ALTER TABLE bypasses these limitations, offering greater flexibility and avoiding potential errors.

While individual preferences may differ, I recommend using ALTER TABLE for foreign keys whenever possible. Its modularity, streamlined execution, and ease of use outweigh the occasional advantages of including them in CREATE TABLE. This approach promotes cleaner, more maintainable codebases and simplifies future database modifications.

ENABLE NOVALIDATE

In Oracle SQL, the NOVALIDATE option in the ALTER TABLE statement is used when you want to add or enable a constraint without validating existing data against the constraint. For example, let's consider SQL statements below:

Now, suppose that we establish a reference constraint between the books and categories tables as follows:

When we execute the ALTER TABLE statement in the line 28, Oracle throws an error and says that "Parent keys not found." This is because of the fifth row in the books table:

The fifth row has category_id value of 4. However, currently there is no corresponding value in the categories table:

To avoid such an error, you can enable the NOVALIDATE in the ALTER TABLE statement as follows: