Constraints

Prerequisites

• Has learned the preliminary basics of SQL

Constraints are used to specify rules for data in a table (acc to. W3Schools), especially before the record is inserted in the table.

These are the standard constraints offered by Oracle Database:

• NOT NULL

• UNIQUE

• PRIMARY KEY

• FOREIGN KEY

• CHECK

• DEFAULT

Not Null

It is used to restrict the column from having null values. Here is the following example:

CREATE TABLE users (

  ...

  name VARCHAR(150) NOT NULL,

  ...

);

Now, this following SQL statement will cause an integrity error (an error that suggests violation of the constraint):

INSERT INTO users VALUES (..., NULL, ...);

or

INSERT INTO users (...) VALUES (...); -- excluding the column restricted with NOT NULL

Unique

It is used to disallow the column from having duplicate or alike values. Here is the following example:

CREATE TABLE users (

  ...

  username VARCHAR(100) NOT NULL UNIQUE,

  ...

);

Let us say the username crunchy_biscuit is taken by some user.

INSERT INTO users VALUES (..., 'crunchy_biscuit', ...);

Then, when the SQL statement above is executed again (same username applied and with different values from other columns), it will cause an error.

Primary Key

This constraint is a combination of both NOT NULL and UNIQUE constraints. When simplified, it disallows null and duplicate values. It is commonly used in IDs or tokens that determine a single unique (also identifiable by WHERE clauses) entity. Here is the following example of using PRIMARY KEY:

CREATE TABLE users (

  id NUMBER PRIMARY KEY,

  ...

);

Primary keys largely help ensure uniqueness of the records by providing an identification token to each record. Because these things cannot have duplicates and be not null, thus resembling an ID.

Foreign Key

Terms:

• Parent record – A record from the referenced table.

• Child record – A record from the table where a column depends on the parent table's column (can be considered as referencing record).

Foreign keys are used to create links with other tables by referencing the primary key column (or any column that resembles a set of unique values) from another table. It also ensures deleting any parent records—records that have child records depending on those—or inserting a record that has a nonexistent value from the referenced column (typically from another table) are prevented.

The following SQL creates a table where the user_id column depends on the users table's id column.

CREATE TABLE items (

  item_id NUMBER PRIMARY KEY,

  item_name VARCHAR(150) NOT NULL,

  item_price NUMBER NOT NULL,

  user_id NUMBER NOT NULL,

  FOREIGN KEY (user_id) REFERENCES users(id);

);

Anything highlighted in blue are identifiers for the table itself (items), whereas highlighted in yellow are from the other table (users).

When inserting data to the items table, the user_id column must match its value to any of the existing values in the referenced column.

Assume the values of id column from the users table are only numbers from 1 to 15 except 7 and 9, the item record that will be inserted belongs to the ID 6. Will the execution of inserting the record be successful? The answer is yes because the ID 6 belongs in the list of existing values from the id column. 

What if they try to insert an item record that belongs to the ID 17? The answer is no, as it will violate the constraint, which is particularly the inserting of a value—in the user_id column that depends on the referenced column—that does not exist in the id column.

The figure below is the relationship of the two tables in terms of its connected columns:

It is also worth mentioning that if an attempt to delete a parent record—which it has child records depending on that—is made, it will just cause an error indicating a constraint violation.

Check

It is used to restrict values that do not satisfy the condition provided for the column. The CHECK constraint must not reference a different column other than the column where the constraint is applied. Here is the following example:

CREATE TABLE users (

  id NUMBER PRIMARY KEY,

  name VARCHAR(150) NOT NULL,

  age NUMBER CHECK (age >= 8),

  ...

);

It checks if the age is equal to or greater than 8. Otherwise, it will fail on insertion. The underlying condition is surrounded by the parentheses, and the column should be involved. 

Default

DEFAULT constraint lets the column assign a specified value if the column containing the constraint is omitted within the INSERT statement. The following example shows a table definition wherein the role column contains the DEFAULT constraint with the default value "Student":

CREATE TABLE users (

  id NUMBER PRIMARY KEY,

  name VARCHAR(150) NOT NULL,

  role VARCHAR(50) DEFAULT 'Student',

  ...

);

In case that the role column gets omitted when inserting a record comprising specific columns (columns without the DEFAULT constraint), the role column will be automatically filled with the assigned default value.