DP - 14 - 2 - Practice FAZRULAKMALFADILA - C2C022001

Nama : Fazrul Akmal Fadila
NIM : C2C022001
Kelas : Informatika A
Database Programming with SQL

14-2: PRIMARY KEY, FOREIGN KEY, and CHECK Constraints
Practice Activities Objectives
• Define and give an example of PRIMARY KEY, FOREIGN KEY, and CHECK constraints
• Explain the purpose of defining PRIMARY KEY, FOREIGN KEY, and CHECK constraints on a table
• Demonstrate the creation of constraints at the column level and table level in a CREATE TABLE
statement
• Evaluate a business problem requiring the addition of a PRIMARY KEY and FOREIGN KEY constraint
and write the code to execute the change
Vocabulary
Identify the vocabulary word for each definition below.
ON DELETE CASCADE Allows a foreign key row that is referenced to a primary key
row to be deleted
Check Constraint Explicitly defines a condition that must be met
PRIMARY KEY A column or set of columns that uniquely identifies each row
in a table
NOT NULL Constraint ensures that the column contains no null values
ON DELETE SET NULL Allows a child row to remain in a table with null values
when a parent record has been deleted
FOREIGN KEY Constraint Establishes a relationship between the foreign key column and a
primary key or unique key in the same table or a different table
Try It / Solve It
1. What is the purpose of a

a. PRIMARY KEY
Uniquely identify each row in table.
b. FOREIGN KEY
Referential integrity constraint links back parent table's primary/unique key to child table's
column.
c. CHECK CONSTRAINT
Explicitly define condition to be met by each row's fields. This condition must be returned as true
or unknown.
2. Using the column information for the animals table below, name constraints where applicable at the table
level, otherwise name them at the column level. Define the primary key (animal_id). The
license_tag_number must be unique. The admit_date and vaccination_date columns cannot contain null
values.
animal_id NUMBER(6) - PRIMARY KEY

name VARCHAR2(25)
license_tag_number NUMBER(10)- UNIQUE
admit_date DATE- NOT NULL
adoption_id NUMBER(5),
vaccination_date DATE- NOT NULL
3. Create the animals table. Write the syntax you will use to create the table.
4. Enter one row into the table. Execute a SELECT * statement to verify your input. Refer to the graphic
below for input.
ANIMAL_ NAME LICENSE_TAG_ ADMIT_DATE ADOPTION_ VACCINATION_

ID NUMBER ID DATE
101 Spot 35540 10-Oct-2004 205 12-Oct-2004
5. Write the syntax to create a foreign key (adoption_id) in the animals table that has a corresponding primary-
key reference in the adoptions table. Show both the column-level and table-level syntax. Note that because
you have not actually created an adoptions table, no adoption_id primary key exists, so the foreign key
cannot be added to the animals table.
If there is a value in animals.adoption_id, which is not present as primary key/unique key in parent table, it
will give error, so first fix it:
UPDATE animals
SET adoption_id = ( SELECT id FROM adoptions WHERE ROWNUM = 1);
or
UPDATE animals
SET adoption_id = NULL;
SELECT * FROM animals;
Adding foreign key using column level statement:
ALTER TABLE animals

MODIFY ( adoption_id NUMBER(5,0) CONSTRAINT anl_adopt_id_fk REFERENCES adoptions(id)
ENABLE );
Verify that constraint is generated:

SELECT *
FROM user_constraints
WHERE LOWER(table_name) = 'animals' AND constraint_type = 'R';
Now drop it:

ALTER TABLE animals
DROP CONSTRAINT anl_adopt_id_fk ;
Adding foreign key using table level statement:
ALTER TABLE animals ADD CONSTRAINT anl_adopt_id_fk FOREIGN KEY (adoption_id)
REFERENCES adoptions(id) ENABLE;
Verify that constraint is generated:

SELECT *
FROM user_constraints
WHERE LOWER(table_name) = 'animals' AND constraint_type = 'R';
6. What is the effect of setting the foreign key in the ANIMAL table as:
a. ON DELETE CASCADE
When a record is deleted from the "adoptions" table that is referenced by the foreign key in the
"animals" table, the corresponding records in the "animals" table will be automatically deleted as well.
This ensures the referential integrity and maintains consistency.
b. ON DELETE SET NULL

When a record is deleted from the "adoptions" table that is referenced by the foreign key in the
"animals" table, the value of the foreign key in the "animals" table will be set to NULL. This means that
the relationship between the "animals" table and the "adoptions" table is broken, and the "animals"
records will no longer be associated with any specific adoption.
7. What are the restrictions on defining a CHECK constraint?

 The check condition must be a Boolean expression.
 The check condition cannot refer to columns in other tables.
 The check condition cannot use subqueries.
 The check condition cannot refer to dynamic performance views or current package state.
 The check condition cannot include calls to user-defined functions that are not declared as deterministic.
 The check condition cannot exceed 4,000 bytes in length.
 The check condition cannot contain bind variables or literals with bind variables.

DP - 14 - 2 - Practice FAZRULAKMALFADILA - C2C022001

Uploaded by

Copyright:

Available Formats

DP - 14 - 2 - Practice FAZRULAKMALFADILA - C2C022001

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

DP - 14 - 2 - Practice FAZRULAKMALFADILA - C2C022001

Uploaded by

Copyright:

Available Formats

Nama : Fazrul Akmal Fadila

Database Programming with SQL

1. What is the purpose of a

animal_id NUMBER(6) - PRIMARY KEY

ANIMAL_ NAME LICENSE_TAG_ ADMIT_DATE ADOPTION_ VACCINATION_

Adding foreign key using column level statement:

ALTER TABLE animals

Verify that constraint is generated:

Now drop it:

Verify that constraint is generated:

b. ON DELETE SET NULL

7. What are the restrictions on defining a CHECK constraint?

You might also like