Database Tutorial

Week #2: Articles

Learning Outcomes

• Learn how to create a column with a FOREIGN KEY constraint.
• Learn how to modify a column with a FOREIGN KEY constraint.

• NOT NULL Constraint
• CHECK Constraint
• UNIQUE Constraint
• PRIMARY KEY Constraint
• FOREIGN KEY Constraint

PRIMARY KEY Constraint

A FOREIGN KEY constraint checks whether a primary key value or set of values exists in another table or another copy of the same table. A FOREIGN KEY constraint based on a single column leaves the column with optional cardinality. A FOREIGN KEY constraint based on a set of columns leaves all of the columns with their default optional cardinality.

Optional column cardinality means a column may have a null value or a real value. It is represented by a cardinality expression where the left value is the minimum cardinality and the right value is the maximum cardinality. There are two dots (.) between the minimum and maximum cardinality values, or 0..1.

In today’s world, most primary keys are single ID column values. Developers link a database sequence to the ID column. A database sequence is a structure that holds a value and increments that value by a counter value (typically one).

Primary and foreign key columns that use single ID column values are not part of the data that describes the subject of a table. You use ID column values to identify unique rows that have a natural key. The natural key uses column values that describe the unique subject of the table.

While the PRIMARY KEY constraint guarantees uniqueness and makes columns mandatory, the FOREIGN KEY constraint is almost always non-unique. FOREIGN KEY columns typically have a low to moderate cardinality value, when cardinality measures a scale of uniqueness or non-uniqueness.

The ID column values are surrogate, or stand-in placeholders for, the natural keys. They are typically the primary key of a table. They exist because they support an easier path to evolving our understanding a table’s subject, which you can read about in the PRIMARY KEY page.

Foreign keys are simply copies of the values found in a primary key column. As such, they support external reference operations in joins between tables.

Oracle lets you implement a FOREIGN KEY constraint in a CREATE statement as either an in-line or out-of-line constraint. You may use the in-line syntax when there’s only one column in the foreign key constraint. However, you must use a table-level or out-of-line when the foreign key constraint applies across more than one column.

The alter command works when you have one column or a set of columns in a foreign key constraint.

• CREATE Statement:
• You can define a FOREIGN KEY constraint on the same line as you define a single column. This approach is called placing an inline constraint on a column.

  The following sample table has three named inline constraints:

  CREATE TABLE product ( product_id NUMBER CONSTRAINT product_pk PRIMARY KEY , product_text VARCHAR2(20) CONSTRAINT product_nn NOT NULL , sample_id NUMBER REFERENCES sample(sample_id));

  CREATE TABLE product ( product_id NUMBER CONSTRAINT product_pk PRIMARY KEY , product_text VARCHAR2(20) CONSTRAINT product_nn NOT NULL , sample_id NUMBER REFERENCES sample(sample_id));

  The first inline constraint is a PRIMARY KEY constraint on the sample_id column. The second inline constraint is a NOT NULL constraint on the product_text column. The third inline constraint is a FOREIGN KEY constraint but you might wonder how you know that because it doesn’t use the CONSTRAINT keyword.

  Oracle’s SQL syntax is a little less consistent with creating FOREIGN KEY constraint with inline syntax. Oracle’s SQL syntax just assumes every SQL developer knows that the only constraint that references anything is a FOREIGN KEY constraint.

  You should also notice that this syntax doesn’t explicitly assign a constraint name to the FOREIGN KEY constraint, which means Oracle would assign a less than meaningful system generated name to the FOREIGN KEY constraint.

  There is another syntax that allows you to name the FOREIGN KEY constraint while providing them with meaningful constraint names:

  CREATE TABLE product ( product_id NUMBER CONSTRAINT product_pk PRIMARY KEY , product_text VARCHAR2(20) CONSTRAINT product_nn NOT NULL , sample_id NUMBER CONSTRAINT product_fk REFERENCES sample(sample_id));

  CREATE TABLE product ( product_id NUMBER CONSTRAINT product_pk PRIMARY KEY , product_text VARCHAR2(20) CONSTRAINT product_nn NOT NULL , sample_id NUMBER CONSTRAINT product_fk REFERENCES sample(sample_id));

  The only difference between the FOREIGN KEY and other inline constraints is that Oracle opts to exclude the FOREIGN KEY keywords from the syntax.

• You can define a FOREIGN KEY constraint after the list of column values as a table-level or out-of-line constraint. It is the preferred way to assign the FOREIGN KEY constraint in a CREATE statement.

  Unfortunately, the reason may not be intuitively obvious to a beginner. You need to remember that the cardinality of foreign key columns is optional, or 0..1, which means you can insert a row without a foreign key value. You can’t join the sample and product table unless:

  • There is a sample_id primary key column in the sample table, and
  • There is a sample_id foreign key column in the product table.

  The default behavior or a FOREIGN KEY constraint fails when your design requires every row in the product table should be linked to a row in the sample table. It fails because you can insert or update a row without a foreign key value in the column constrained by the FOREIGN KEY.

  The overriding behavior of a FOREIGN KEY constraint succeeds when you add a NOT NULL constraint to the column before layering a FOREIGN KEY constraint on it. Please recall from the NOT NULL page that you must add NOT NULL constraints inline, which is why Oracle anticipates you will add FOREIGN KEY constraints as table-level or out-of-line constraints.

  The most common syntax to solve the problem is:

  CREATE TABLE product ( product_id NUMBER CONSTRAINT product_pk PRIMARY KEY , product_text VARCHAR2(20) CONSTRAINT product_nn1 NOT NULL , sample_id NUMBER CONSTRAINT product_nn2 NOT NULL , CONSTRAINT product_fk FOREIGN KEY(sample_id) REFERENCES sample(sample_id));

  CREATE TABLE product ( product_id NUMBER CONSTRAINT product_pk PRIMARY KEY , product_text VARCHAR2(20) CONSTRAINT product_nn1 NOT NULL , sample_id NUMBER CONSTRAINT product_nn2 NOT NULL , CONSTRAINT product_fk FOREIGN KEY(sample_id) REFERENCES sample(sample_id));

  You should note that constraint names are unique, like table names. That means you can’t reuse product_nn twice. The script simply add distinguishing numbers to the NOT NULL constraints, like product_nn1, product_nn2, et cetera.

• ALTER Statement:
• You can add or drop a FOREIGN KEY constraint when you want to add a product_fk constraint, like

  You drop an existing customer_pk FOREIGN constraint with the following syntax:

  ALTER TABLE product DROP CONSTRAINT customer_fk;

  ALTER TABLE product DROP CONSTRAINT customer_fk;

• After you drop the customer_uq FOREIGN KEY constraint, you can add a new version of the customer_pk FOREIGN KEY constraint with the following syntax:

  ALTER TABLE product ADD CONSTRAINT product_fk FOREIGN KEY(sample_id) REFERENCES sample(sample_id);

  ALTER TABLE product ADD CONSTRAINT product_fk FOREIGN KEY(sample_id) REFERENCES sample(sample_id);