From the course: Oracle Database 19c: Advanced SQL

Primary key constraints - Oracle Database Tutorial

From the course: Oracle Database 19c: Advanced SQL

Start my 1-month free trial

Primary key constraints

- [Instructor] Primary key constraints make sure that a column or a group of columns, is unique across all rows in a table. Other columns or combinations of columns can be defined as unique. But there is one and only one primary key index. Let's focus on the primary key constraint. How it can be created and how it's different from a unique constraint. Here's what a primary key constraint looks like. It's actually a combo constraint. Three for the price of one. Two data dictionary objects are created. A constraint of type PRIMARY KEY ("P") in the data dictionary along with a unique index to enforce the uniqueness of the constraint. Also, when a primary key constraint is created on one or more columns, the nullability of the columns in the primary key are changed to not null. Finally, there can only be one primary key constraint per table. That's an important difference between unique constraints and primary key constraints. Unique constraints can have no values in any column of the unique index. As long as the combination of column values is still unique within the table. For the table EMP_DEPT_ ROLE. If the primary key were on the combination of employee ID and department ID three of these rows would not be allowed in the table. In index on the primary key, cannot have no values. If this were a unique constraint, that would be fine, since all pairs of column values are unique. Null values are treated like actual values for the purposes of uniqueness. This is not so with an index supporting a primary key constraint. This is why all columns and a primary key become not null when they become part of our primary key constraint. In this example, I'm creating a primary key constraint on the region ID column of the regions table. In other words, the region ID column is a primary key and also unique. Both a constraint and a unique index is created on the region ID column to enforce uniqueness. I can query user constraints and user indexes to find them. The constraint type is "P" which of course stands for primary key. The index name is the same as the constraint name by default. Notice also that the index is unique but since it's enforcing a primary key it can not have any, no values. One more thing, when I look at the column attributes I noticed that region ID, the primary key column is not null even though I didn't declare it that way because of primary key was defined on that column. It was automatically set to not null. What if I create a table, but forget to define a primary key. Well, it's easy to add later. The alter table statement adds the constraint on the table sets region ID to not null and automatically creates the unique index on the region ID column. A primary key constraint can be created in line or out of line in the create table statement. The syntax allows it both ways. I've been creating the primary key constrict out of line in the previous examples. If it's a single key primary key constraint it can be in line. But if the constraint is on more than one column it must be out of line. The effect is the same, primary key constraint is created on the region name column, along with the associated index to enforce the constraint. You need to know about primary key constraints. They ensure that values for that column or combination of columns across all rows are unique. When a primary key constraint is defined a constraint object is created in the data dictionary. An index with the unique attribute is created on the column or columns in the table. Values are not allowed in columns that are part of our primary key constraint. This is one of the main differences between a unique constraint and a primary key constraint. When you define a primary key constraint on one or more columns, they're all marked is not null. Also when defining a constraint it can be in line or out of line but only if the unique constraint is on a single column. Every table in a database should have a primary key defined with an associated index. Relational database design dictates that normalized data models contain tables whose rows can be differentiated by one or a combination of several columns in a table.

Contents