Database Tutorial

Course Tutorial Site

Site Admin

Adding Constraints

without comments

Learning Outcomes

  • Learn how to add a constraint to a column in a table.
  • Learn how to add a table constraint to a set of column in a table.

Supporting Materials

The following web pages may help you understand the SQL syntax in this tutorial:

Lesson Materials

There are three scenarios for adding constraints on a column or set of columns in a table:

  • Add a NOT NULL constraint on a column in a table.
  • Add a UNIQUE constraint on a column in a table.
  • Add a CHECK constraint on a column in a table.
  • Add a PRIMARY KEY constraint on a column in a table.
  • Add a PRIMARY KEY constraint on a set of columns in a table.
  • Add a FOREIGN KEY constraint on a column in a table.
  • Add a FOREIGN KEY constraint on a set of columns in a table.

Add a NOT NULL constraint

SQL> ALTER TABLE somewhere
  2    ADD ( timbuktu  VARCHAR2(30) CONSTRAINT nn_somewhere NOT NULL);

Add a UNIQUE constraint on a column

SQL> ALTER TABLE something
  2    ADD ( vin  VARCHAR2(30) CONSTRAINT uq_somewhere UNIQUE);

Add a CHECK constraint on a column

SQL> ALTER TABLE something
  2    ADD ( price  NUMBER(15,2) CONSTRAINT ck_somewhere CHECK (price > 5 AND price < 100));

Add a PRIMARY KEY constraint on a column

SQL> ALTER TABLE something
  2    ADD ( something  VARCHAR2(20) CONSTRAINT pk_somewhere PRIMARY KEY);

Add a PRIMARY KEY constraint on a set of columns

SQL> ALTER TABLE something
  2    ADD CONSTRAINT pk_something PRIMARY KEY (something, somewhere, sometime);

Add a FOREIGN KEY constraint on a column

SQL> ALTER TABLE rental_item
  2    ADD CONSTRAINT fk_rental_id_1
  3         FOREIGN KEY (rental_id) REFERENCES rentals (rental_id);

Add a FOREIGN KEY constraint on a set of columns

SQL> ALTER TABLE delegate
  2  ADD CONSTRAINT fk_natural_contact
  3      FOREIGN KEY ( member_id, first_name, middle_name, last_name )
  4      REFERENCES contact (member_id, first_name, middle_name, last_name);

You have now seen the seven key use cases for adding a constraint to column or set of columns in a table.

Written by michaelmclaughlin

August 13th, 2018 at 2:40 pm

Posted in