Adding Constraints
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.