3.1 Primary Key
| The PRIMARY KEY of a table constrains a single column, or set of columns , to a unique and mandatory value ”mandatory, meaning that no column in the primary key can ever be null. A table need not have a primary key, but this is the exception; most tables are created with a primary key. Consider a table that stores information about students. We must be able to identify a student and store data associated with that individual student. Each student must have a row and only one row in the STUDENTS table. Also, each row in the STUDENTS table should identify one, and only one, student. The primary key mechanism enables an application, for example, to properly process student tuition bills. Every student will get one, and only one, tuition bill. In addition to business rule enforcement, there are other database motivations. Primary keys are an integral part of parent-child relationships that enforce referential integrity. Additionally, a primary key requires an index, a physical structure consuming disk space that, when used properly, provides fast access to the data. The DDL for the primary key constraint can be embedded within the CREATE TABLE statement. Embedded DDL has two syntax versions: a column constraint clause and a table constraint clause. The primary key constraint can be created separate from the table creation statement. This is accomplished with an ALTER TABLE statement. Creation of the constraint with ALTER TABLE can be done immediately after the table is created. It can also be done after the table is populated . The Oracle database engine enforces the rule of the primary key constraint. Once you create a table with a primary key, you are saying that: first, all values for the primary key are unique; and second, they must have a value, which means that no column in the primary key can ever be NULL. Enforcement of a constraint can be temporarily disabled and later enabled. This is accomplished with an ALTER TABLE statement and the constraint options: DISABLE and ENABLE. One reason to load data with a disabled constraint is to reduce the load time. The load time with a disabled constraint will be less because indexes are disabled and therefore not updated. Within a transaction, an application can temporarily suspend constraint enforcement. In this case a program begins a transaction by setting the constraint to a deferred state. The data is loaded followed by a commit. Upon commit, the constraint is applied by Oracle. This option requires that the constraint be created with the DEFERRABLE keyword. You can load data into a table with a constraint disabled and, after the load, enable the constraint so that the rule is applied only to new data. The old data, should it violate the business rule, can remain in the table. This strategy to business rule enforcement can apply to data warehouses that must have historical data available for analysis. This option requires that the constraint be enabled with a NOVALIDATE keyword. Several types of primary key enforcement, such as DEFERRABLE and NOVALIDATE, will affect the type of index used with the primary key constraint. These options will use a nonunique index. A conventional primary key constraint, without ever attempting to violate it, even temporarily within a transaction, will use a unique index. The fact that a table has a primary key is stored in the data dictionary. We have looked at the data dictionary view, USER_TAB_COLUMNS (see Chapter 1, Section 1.6.2, "Data Dictionary"), which is the dictionary view for looking at column names within a table. We also have views for constraints, these are USER_CONSTRAINTS and USER_CONS_COLUMNS. These views show what tables have constraints, as well as constraint name , type, and status. Two methods by which you can challenge a primary key constraint is to INSERT a duplicate row or UPDATE a column value forcing a duplicate ”in either case, the INSERT or UPDATE statement will fail with an Oracle error. Declaring constraints is only part of an application. Application code should be integrated with constraint enforcement and handle errors that propagate from constraint violations. If your application is an OLTP system and data entry can cause a duplicate insert, the code should contain graceful error handling. It should produce a meaningful end- user response more meaningful than the generic text of a constraint violation or an alarming stack trace. 3.1.1 Creating the ConstraintThis section covers creating the constraint with the column constraint clause, table constraint clause, and the ALTER TABLE statement. This section will use a sample table, TEMP, that has the following description: Name Null? Type ------------------------------ -------- ------------ ID VARCHAR2(5) NO NUMBER There are several syntax methods and styles for creating a primary key:
The following discussion addresses the three styles with respect to creating a PRIMARY KEY constraint. Other types of constraints, UNIQUE, FOREIGN KEY, and CHECK, can also be created with each style. 3.1.1.1 COLUMN CONSTRAINT CLAUSEThe following creates a table, TEMP, with two columns. The column ID is the primary key. This is an example of a column constraint clause. SQL> CREATE TABLE temp(id VARCHAR2(5) PRIMARY KEY, no NUMBER); Table created. Once the table is created rows are inserted; however, an INSERT with a duplicate value for the column ID will fail. The third INSERT statement is a duplicate and consequently fails. SQL> insert into temp values ('AAA', 1); First row. 1 row created. SQL> insert into temp values ('BBB', 2); Second row. 1 row created. SQL> insert into temp values ('AAA', 3); Duplicate. insert into temp values ('AAA') * ERROR at line 1: ORA-00001: unique constraint (SCOTT.SYS_C006083) violated For the last insert, SQL*Plus flushed an error message to the display. This is the behavior of SQL*Plus. If we are writing code in Java or PL/SQL and anticipate a duplicate insert, we can write error handling code, capture the error condition, and handle it gracefully. The duplicate insert error message prefix is "ORA". The error number is "-00001." That is not a dash between "ORA" and the number, but "ORA" and a minus one. The aforementioned error message references "SCOTT.SYS_C006083"; this is the name of the primary key constraint. This name was internally generated by Oracle during execution of the CREATE TABLE TEMP statement. To name the primary key constraint with a column constraint clause: CREATE TABLE temp (id VARCHAR2(5) CONSTRAINT PRIMARY KEY my_constraint_name, no NUMBER); Column constraint clauses are quick and appropriate for ad hoc SQL. Two restrictions on column constraint clauses for primary keys are: (a) no concatenated primary key can be declared and (b) you cannot stipulate the tablespace of the index created on behalf of the constraint. Both concatenated keys and tablespace clauses for indexes are covered later in this chapter. 3.1.1.2 TABLE CONSTRAINT CLAUSEThe table constraint clause is attached at the end of the table definition. Table constraint clauses are part of the CREATE TABLE statement ”they just come after all the columns are defined. If there is a syntax error in the constraint clause, the statement fails and no table is created. The following illustrates, in template form, a CREATE TABLE statement that declares a primary key. The table constraint clause allows multiple constraints to be included with a comma separating each constraint definition. CREATE TABLE temp (id VARCHAR2(5), no NUMBER, CONSTRAINT PRIMARY KEY (id), CONSTRAINT. . next constraint, CONSTRAINT. . next constraint ) TABLESPACE etc ; The following creates the TEMP table, using a table constraint clause. CREATE TABLE temp (id VARCHAR2(5), no NUMBER, CONSTRAINT PRIMARY KEY (id)) TABLESPACE student_data; 3.1.1.3 ALTER TABLE STATEMENTThe ALTER TABLE statement is another option for managing constraints. Once you create a table, you can use the ALTER TABLE statement to manage constraints, add columns, and change storage parameters. The ALTER TABLE command regarding constraints is used to perform the following:
The following DDL consists of two DDL statements: a CREATE TABLE statement and an ALTER TABLE statement for the primary key. In this example, the constraint is named PK_TEMP. CREATE TABLE temp (id VARCHAR2(5), no NUMBER); ALTER TABLE temp ADD CONSTRAINT pk_temp PRIMARY KEY (id); The ALTER TABLE command has many options. An approach to remembering the syntax is to consider the information Oracle needs to perform this operation:
3.1.2 Naming the ConstraintThis section will use a sample table, TEMP, that has the following description: Name Null? Type ------------------------------ -------- ------------ ID VARCHAR2(5) NO NUMBER The following example creates a table with an unnamed primary key constraint. CREATE TABLE temp(id VARCHAR2(5) PRIMARY KEY, no NUMBER); All constraints have a name and when no name is supplied, Oracle internally creates one with a syntax, SYS_C, followed by a number. The constraint name is important. Troubleshooting frequently requires that we query the data dictionary for the constraint type, table, and column name. For the aforementioned CREATE TABLE statement, a duplicate insert will produce an error. ORA-00001: unique constraint (SCOTT.SYS_C006083) violated The following creates the table and assigns the constraint name PK_TEMP: CREATE TABLE temp (id VARCHAR2(5) CONSTRAINT pk_temp PRIMARY KEY, no NUMBER); A duplicate insert here includes the constraint name as well. This constraint name, PK_TEMP, is more revealing as to the nature of the problem. ORA-00001: unique constraint (SCOTT.PK_TEMP) violated Regardless of the syntax form, you can always name a constraint. The following illustrates the TEMP table and primary key constraint with a column constraint clause, table constraint clause, and ALTER TABLE statement, respectively. (1) CREATE TABLE temp (id VARCHAR2(5) CONSTRAINT pk_temp PRIMARY KEY, no NUMBER); (2) CREATE TABLE temp (id VARCHAR2(5), no NUMBER, CONSTRAINT pk_temp PRIMARY KEY (id)); (3) CREATE TABLE temp (id VARCHAR2(5), no NUMBER); ALTER TABLE temp ADD CONSTRAINT pk_temp PRIMARY KEY (id); Having consistency to constraint names, or following a format, is just as important as naming the constraint. For all stages of a database, be it development, test, or production, it makes sense to have primary key constraints named. Primary key constraints are commonly named with one of two formats. Both formats indicate the constraint type with the PK designation and the table name. 1. PK _table_name 2. table_name_ PK Suppose you just installed a new application and your end user calls you. Would you rather have the user say, "I ran this application and got an Oracle error that says: SYS_C006083 constraint violation." Or, would you rather hear, "I ran this application and got an Oracle error: PK_TEMP." If the constraint name is PK_TEMP, you can quickly comfort the caller because you know exactly what the problem is: a primary key constraint violation on that table. Without a meaningful constraint name, you would begin with querying the data dictionary views DBA_CONSTRAINTS to track the table upon which that constraint, SYS_C006083, is declared. A common oversight when using a data modeling tool is to not name constraints. A data modeler is usually far more concerned with system requirements than constraint names. Having to go back and type in numerous constraint names can be tedious . If you use such a tool, it is worthwhile to do a small demo to see what DDL the tool generates. You want to make sure the final DDL meets your criteria and that you are using the features of the tool that generate the desired DDL. Oracle Designer, very conveniently, will automatically name primary key constraints with a "PK" prefix followed by the table name. 3.1.3 The Primary Key IndexThis section covers the index that must always exist as part of the primary key. The topic of tablespace is included. Refer to Chapter 1, Section 1.6.1, "Application Tablespaces," for additional information on the definition, use, and creation of an Oracle tablespace. This section will use a sample table, STUDENTS, that has the following description: Name Null? Type ------------------------------ -------- ------------ STUDENT_ID VARCHAR2(10) STUDENT_NAME VARCHAR2(30) COLLEGE_MAJOR VARCHAT2(15) STATUS VARCHAR2(20) Whenever you create a primary key, Oracle creates an index on the column(s) that make up the primary key. If an index already exists on those columns, then Oracle will use that index. Indexes are an integral part of the primary key. Depending on the primary key options, the index may be unique or nonunique. Deferrable primary key constraints use nonunique indexes. Indexes are not used to enforce the business rule of the primary key, but an index is still required. The benefits of the index are seen with queries against the table. If the primary key constraint is disabled, the index is dropped and query performance suffers. Tables occupy physical storage. Indexes also use physical storage. The creation of the primary key should designate a tablespace for that index. Because of I/O contention and the fact that indexes grow differently than tables, we always place indexes in separate tablespaces. The following ALTER TABLE statement creates the primary key, plus designates the tablespace for the index ”USING INDEX TABLESPACE is a keyword phrase to this syntax. CREATE TABLE students (student_id VARCHAR2(10), student_name VARCHAR2(30), college_major VARCHAR2(15), status VARCHAR2(20)) TABLESPACE student_data; ALTER TABLE students ADD CONSTRAINT pk_students PRIMARY KEY (student_id) USING INDEX TABLESPACE student_index; If you do not designate a tablespace for the primary key index, that index is built in your default tablespace. All Oracle user accounts are created with a default tablespace. Tables and indexes created by a user with no tablespace designation fall into this default tablespace. For example, the following DDL creates an index object in the default tablespace. Because there is no tablespace clause on the CREATE TABLE statement, the table is also created in the default tablespace. CREATE TABLE temp(id VARCHAR2(5) PRIMARY KEY, no NUMBER); The following puts the TEMP table in the STUDENT_DATA tablespace and the primary key in the STUDENT_INDEX tablespace. CREATE TABLE temp(id VARCHAR2(5), no NUMBER) tablespace STUDENT_DATA; ALTER TABLE temp ADD CONSTRAINT pk_temp PRIMARY KEY (ID) USING INDEX TABLESPACE student_index; To create tables and indexes in tablespaces other than your default requires privileges. If you have the RESOURCE role, then you may still have the privilege UNLIMITED TABLESPACE ”this privilege is automatically inherited with the RESOURCE role and gives you unlimited tablespace quotas. With UNLIMITED TABLESPACE you are able to create objects in any tablespace including the SYSTEM tablespace. For this reason, the privilege is often revoked from application developers and tablespace quotas are added. The change made to developer accounts is similar to the change made to the SCOTT account as follows . REVOKE UNLIMITED TABLESPACE FROM SCOTT; ALTER USER SCOTT QUOTA UNLIMITED ON STUDENT_DATA; ALTER USER SCOTT QUOTA UNLIMITED ON STUDENT_INDEX; ALTER USER SCOTT DEFAULT TABLESPACE STUDENT_DATA; Check your account privileges and tablespace quotas with the following SQL. Privileges and roles that are granted to your Oracle account are queried from the data dictionary views USER_ROLE_PRIVS and USER_SYS_PRIVS: column role_priv format a30 SELECT 'ROLE: 'granted_role role_priv FROM user_role_privs UNION SELECT 'PRIVILEGE: 'privilege role_priv FROM user_sys_privs; ROLE_PRIV ------------------------------ PRIVILEGE: SELECT ANY TABLE PRIVILEGE: CREATE ANY MATERIALIZED VIEW ROLE: CONNECT ROLE: RESOURCE ROLE: SELECT_CATALOG_ROLE To see tablespace quotas, query USER_TS_QUOTAS: SELECT tablespace_name, max_bytes FROM user_ts_quotas; The aforementioned SQL will return a minus 1 for any tablespace for which you have an unlimited quota. For example: TABLESPACE_NAME MAX_BYTES ------------------------------ ---------- STUDENT_DATA -1 STUDENT_INDEX -1 An index is an object that is created in a tablespace. It is a physical structure that consumes disk space. When you create a Primary Key or Unique constraint, an index is either automatically created or an existing index may be reused. An index is based on a tree structure. Indexes are used by Oracle to execute SELECT statements. The execution of a SELECT using an index is generally faster than a SELECT that does not use an index. Indexes are generally created in tablespaces separate from tablespaces for tables. Indexes from primary keys make up a portion of all indexes in an application. A tablespace for indexes will have indexes from primary key constraints, indexes from unique constraints, and indexes created to speed up selected queries. Figure 3-1 illustrates the physical separation of the index structures and table structures. The left side of Figure 3-1 represents a tablespace for indexes. All index structures physically exist in the files for this tablespace. The right side illustrates the allocation of tables. Tables physically exist in the files for the STUDENT_DATA tablespace. Figure 3-1. Primary Key Indexes. To determine the physical space allocated by the STUDENTS table and the primary key index, query DBA_EXTENTS and DBA_DATA_FILES. The following illustrates a SQL*Plus session query against these views. Because these views begin with DBA, you need either the DBA role or SELECT_CATALOG_ROLE role (refer to Chapter 5, Section 5.1, "What You Can See," for additional information on data dictionary views). SQ1> SELECT a.extent_id, 2* a.segment_name, 3* b.file_name, 4* round(a.bytes/1024) KBytes 5* FROM dba_extents a, 6* dba_data_files b 7* WHERE segment_name in ('STUDENTS','PK_STUDENTS') 8* AND a.file_id=b.file_id; SQL> The result from the previous code will produce something like the following. EXTENT_ID SEGMENT_NAME FILE_NAME KBYTES --------- ------------ -------------------------- ------ 0 STUDENTS D:\. .\STUDENT_DATA01.DBF 60 0 PK_STUDENTS D:\. .\STUDENT_INDEX01.DBF 60 The previous output shows that the index created from the primary key initially consumes 60K of disk space in the file STUDENT_INDEX01.DBF. This allocated space is in a file, separate from where the initial 60K of disk space is allocated for the STUDENTS table. The SEGMENT_NAME column, PK_STUDENTS, is the name of the index, not the constraint. When you create a primary key constraint, an index is created with the same name. That is the situation discussed earlier. The index name can differ if the index is created first and then the constraint is created. When a primary key or unique constraint is created, Oracle looks for an index on the table and on the same columns of the constraint. If such an index exists, that index is used, regardless of the index name. The following paragraphs explain the aforementioned SQL*Plus query that selects data dictionary information from two views: DBA_EXTENTS and DBA_DATA_FILES. Objects that require disk space are also called segments. A table is a relational object, but it is also a physical segment ”it uses disk space. You query DBA_SEGMENTS to get the physical attributes of tables, partitions, indexes, clusters, and materialized views ”any object that requires disk capacity. Stored procedures and sequences are not segments ”they are objects that are defined in the data dictionary and their definitions exist in the system tablespace. For every segment, there are one or more extents. An extent is a contiguous set of database blocks. A table is a segment. The table segment can consume 15 contiguous blocks on disk, where each block is 4K. Blocks 1 through 15, times the 4K block size, yields 60K. This is one extent. There can be other extents. Each extent is a contiguous series of 15 blocks, each block being 4K. Block sizes can vary, and so can the size and number of extents. The query result shows that there is 60K of contiguous blocks allocated for the index and 60K of contiguous blocks allocated for the STUDENTS table. This is what is first allocated just as a result of creating the table and index. When the 60K fills up, the table or index will extend to another 60K. The datafile for the STUDENT_DATA tablespace is 5M. We are only using 60K of that. The data dictionary views DBA_SEGMENTS, DBA_EXTENTS, and DBA_DATA_FILES are three of many dictionary-wide views that provide detailed information about space allocation of segments. The following summarizes the relationship between the primary key constraint and the index.
3.1.4 SequencesThis section will use a sample table, STUDENTS, that has the following description: Name Null? Type ------------------------------ -------- ------------ STUDENT_ID VARCHAR2(10) STUDENT_NAME VARCHAR2(30) COLLEGE_MAJOR VARCHAT2(15) STATUS VARCHAR2(20) The STUDENTS table with primary key constraint is created with the following DDL. CREATE TABLE students (student_id VARCHAR2(10), student_name VARCHAR2(30), college_major VARCHAR2(15), status VARCHAR2(20)) TABLESPACE student_data; ALTER TABLE students ADD CONSTRAINT pk_students PRIMARY KEY (student_id) USING INDEX TABLESPACE student_index; For our student system database, what do we use for a STUDENT_ID? One option is to generate a unique student ID for each student that enters the college. We could use social security numbers, information from student visas, even driver's license data. An alternative is for the application code to auto-generate student ID numbers as each student is added to the database. Databases handle auto-generated columns in a variety of ways. SQL Server uses an IDENTITY column; Oracle uses sequences. We start with creating a sequence that is an object. The existence of that object is stored in the data dictionary. The sequence always has state information, such as current value, and that context remains within the data dictionary. Once you create a sequence you can query the attributes of that sequence from the data dictionary view USER_SEQUENCES. A sequence has two attributes, NEXTVAL and CURRVAL:
We can experiment with sequences right within SQL*Plus as illustrated with the following script. The SQL*Plus session text that follows creates a sequence named MY_SEQUENCE and then uses that sequence to insert values into a TEMP table, also created here. The first statement creates a sequence with defaults, which means that the first time we use that sequence, the value for NEXTVAL will be the number 1. SQL> CREATE SEQUENCE my_sequence; Sequence created. SQL> CREATE TABLE temp(n NUMBER); Table created. SQL> INSERT INTO temp VALUES (my_sequence.NEXTVAL); 1 row created. SQL> / Repeat the last insert. 1 row created. SQL> / Repeat the last insert. 1 row created. SQL> INSERT INTO temp VALUES (my_sequence.CURRVAL); 1 row created. SQL> SELECT * FROM temp; Now, what is in this table? N ---------- 1 2 3 3 The keyword START WITH designates the starting point of a sequence. To recreate the sequence with a starting point of 10: SQL> DROP SEQUENCE my_sequence; Sequence dropped. SQL> CREATE SEQUENCE my_sequence START WITH 10; Sequence created. SQL> SELECT my_sequence.NEXTVAL from dual; NEXTVAL ---------- 10 If you shut the database down, start it back up, and repeat the previous three INSERT statements, the sequence numbers will continue with 4, 5, and 6. They will continue sequencing because the state of the sequence number is maintained in the data dictionary. When the database is shut down, the state of that sequence number is somewhere in the system tablespace datafile. Sequences can be created so that the numbers either cycle or stop sequencing at some maximum value. Keywords for this are CYCLE and NOCYCLE. The INCREMENT BY interval can create the sequence to increment by a multiple of any number; the default is 1. The CACHE option pre- allocates a cache of numbers in memory for improved performance. The following illustrates a sequence that will cycle through the values: 0, 5, 10, 15, and 20, then back around to 0. SQL> CREATE SEQUENCE sample_sequence 2* MINVALUE 0 3* START WITH 0 4* MAXVALUE 20 5* INCREMENT BY 5 6* NOCACHE 7* CYCLE; Sequence created. Sequence numbers are not tied to any table. There is no dependency between any table and a sequence. Oracle has no way to know that you are using a particular sequence to populate the primary key of a specific table. If you drop and recreate a sequence you may possibly, temporarily, invalidate a stored procedure using that sequence. This would occur if a stored procedure includes the sequence with NEXTVAL or CURRVAL in a PL/SQL or SQL statement. Once the sequence is dropped, the stored procedure becomes invalid. Once the sequence is recreated the procedure will be compiled and validated at run time. Once you create a sequence you can use it to populate any column in any table. For this reason it is very desirable to carefully name sequences to reflect their use and restrict sequences to the purpose of populating a specific column. If the primary key is generated by a sequence, the sequence should be named with the following format: table_name_ PK_SEQ Using this syntax, a sequence dedicated to the generation of primary key values in the STUDENTS table would be named: STUDENTS_PK_SEQ The view, USER_SEQUENCES, shows the attributes of all sequences in a schema. If we see a sequence named STUDENTS_PK_SEQ, we are quite certain this sequence is used to populate the primary key column of the STUDENTS table. We are certain only because of the sequence name, not from any other data dictionary information. The complete DDL to create the STUDENTS table, primary key constraint with tablespace clause, and the sequence is the following: CREATE TABLE students (student_id VARCHAR2(10), student_name VARCHAR2(30), college_major VARCHAR2(15), status VARCHAR2(20)) TABLESPACE student_data; ALTER TABLE students ADD CONSTRAINT pk_students PRIMARY KEY (student_id) USING INDEX TABLESPACE student_index; CREATE SEQUENCE students_pk_seq; 3.1.5 Sequences in CodeYou can use the NEXTVAL attribute in any SQL INSERT statement to add a new student. The following Java procedure uses the sequence STUDENTS_PK_SEQ to insert a new student. This code could be called when a student submits, as part of the admission process, an HTML form with the student name, subject major, and status. There is no student ID on the HTML form ”the student ID is evaluated as part of the SQL INSERT statement. public void insertStudent(String StudentName, String CollegeMajor, String Status) { try { stmt = conn.prepareStatement ("INSERT INTO students (student_id," + " student_name, college_major, status)" + " VALUES( students_pk_seq.NEXTVAL, :b1, :b2, :b3) "); stmt.setString(1, StudentName); stmt.setString(2, CollegeMajor); stmt.setString(3, Status); stmt.execute(); } catch (SQLException e) { if (e.getErrorCode() == 1) { System.out.println("We have a duplicate insert."); } } } Section 3.1.1, "Syntax Options," illustrates a SQL*Plus session where a duplicate insert is met with an ORA error number of minus 1 and an Oracle error text message. The aforementioned Java procedure includes a try-catch exception handler for the same type of primary key constraint violation. This try-catch handler uses the getErrorCode() method, which returns the five-digit ORA number ”in this case, 1. Such an error may be unlikely , but it would not be impossible . Error handling code is supposed to be used infrequently, but it is not supposed to be missing from the application. The aforementioned Java method, insertStudent(), inserts STUDENTS_PK_SEQ.NEXTVAL as the new student ID for column STUDENT_ID. This expression "NEXTVAL" always evaluates to an integer. Even though the column for STUDENT_ID is string, VARCHAR2(10), the sequence result is implicitly converted to a string by Oracle. You can manipulate the sequence and use it to build a unique string that satisfies a desired format. We are using a VARCHAR2(10) data type for STUDENT_ID. Suppose we want a STUDENT_ID to be the letter "A" followed by a string of nine digits. We could declare a nine-digit sequence from 1 to 999,999,999 ”then, concatenate that string with our prefix letter. Our CREATE SEQUENCE statement would first declare the MINVALUE and MAXVALUE using these limits. INSERT statements would then "zero-left-pad" the sequence and concatenate the prefix. The INSERT statement by itself would be the following. INSERT INTO students VALUES ('A' LPAD(student_id.NEXTVAL, 9, '0'), etc ); Sequence numbers are a safe strategy for guaranteeing unique values. The default CREATE SEQUENCE syntax will generate 10 27 numbers before you cycle around ”this should handle most applications. The auto-generation feature of sequences should not be a reason to forgo error handling code. An application program using the sequence may be the main vehicle for adding students, but rare events may force inserts from other means, such as SQL*Plus. A rare situation could cause a student to be added "by hand" ”possibly due to a problem with a missed student, resolved by an operations person entering the student with SQL*Plus. The operations staff may just use the next highest number in the STUDENTS table for that INSERT. The next time the application runs and enters a new student, it will generate the next sequence value and collide with what operations did with SQL*Plus. Problems with sequences can also occur when applications are migrated and a sequence is inadvertently dropped and recreated. In this case the sequence starts over with a student ID of 1 and causes a duplicate insert. With regard to error handling, the sequence does provide uniqueness, but mistakes happen. As rare as a duplicate insert might be, a graceful capture of a primary key constraint violation will always save hours of troubleshooting. 3.1.6 Concatenated Primary KeyIn this section we make use of a table, STUDENT_VEHICLES, that stores information on vehicles that students keep on campus. The table description is: Name Null? Type ------------------------------ -------- ------------ STATE NOT NULL VARCHAR2(2) TAG_NO NOT NULL VARCHAR2(10) VEHICLE_DESC NOT NULL VARCHAR2(30) STUDENT_ID NOT NULL VARCHAR2(10) PARKING_STICKER NOT NULL VARCHAR2(10) Your client, the motor vehicle department of the school, expresses a need, "We want to track student vehicles on campus. Students come from all over the country with cars. Students are allowed to keep cars on campus, but the college needs to track vehicles. The college issues a parking sticker that permits the car to remain on campus." Data model analysis begins with looking at vehicle tag information. License's tags are issued by each state, so we can be certain that all vehicle license tag numbers issued by New York are unique within that state and all tag numbers from California are unique within that state. Tag numbers are short-string combinations of letters and numbers. We can assume the following rule: within each state, all tags within that state are unique. Consequently, the combination of state abbreviation and the tag number of any student's vehicle will be always unique among all students. This rule is enforced with a combination of these two columns forming a concatenated primary key. Below is sample data for California (CA) and New York (NY) license numbers for three students. There is no rule about a student registering more than one vehicle on campus. As you can see, student A104 has two registered vehicles. STATE TAG_NO VEHICLE_DESC STUDENT_ID PARKING_STICKER ----- -------- ------------ ---------- --------------- CA CD 2348 1977 Mustang A103 C-101-AB-1 NY MH 8709 1989 GTI A104 C-101-AB-2 NY JR 9837 1981 Civic A104 C-101-AB-3 We will store this information in a new table called STUDENT_VEHICLES. We know there will be other columns of interest (e.g., vehicle registration information); for now, we'll just include vehicle description, the student who uses the car, and the number of the parking sticker issued by the campus police. The key here is that, in the real world, we can definitely say that the combination of a state abbreviation and license tag number is always unique ”this makes (STATE, TAG_NO) a concatenated primary key. The DDL for this table is: CREATE TABLE student_vehicles (state VARCHAR2(2), tag_no VARCHAR2(10), vehicle_desc VARCHAR2(20), student_id VARCHAR2(10), parking_sticker VARCHAR2(10)) TABLESPACE student_data; ALTER TABLE student_vehicles ADD CONSTRAINT pk_student_vehicles PRIMARY KEY (state, tag_no) USING INDEX TABLESPACE student_index; Concatenated primary keys and sequence-generated keys do not mix. A single column is all that is needed for a sequence-generated primary key. This table should not incorporate a sequence; it stores information on vehicles that naturally and uniquely distinguishes each vehicle by state and tag number. During your initial design, you may be told by your client that certain values will always be unique. Your client may say, for example with an inventory system, that the combination of COMPANY and PART_NO will always be unique. You go with this concept and construct a concatenated primary key. Months later, you learn that there are exceptions ”sometimes this is as much a revelation to the client as it is to you. Your approach then is to rebuild the tables and add a new column for the sequence. Some modelers will, as a standard practice, make every primary key a sequence-generated column. This works but, to a degree, discards reality. For example, there is no doubt that a state abbreviation and a tag number, in the real world, are unique and truly can be used to uniquely identify the attributes of any vehicle on campus. When the de facto standard in a project is to make all primary keys sequence-generated, you have to pay closer attention to how you query the table because you may frequently query the table using real-world attributes like state abbreviation and tag number. These columns might not have indexes, unless you specifically create them. The column with the sequence has an index because it has a primary key; however, other columns may not have, but should have indexes. 3.1.7 Extra Indexes with Pseudo KeysThe STUDENT_VEHICLES table has a natural primary key ”it seems natural to assume that the combination of state abbreviation and license number will always be unique. Suppose we add a pseudo key, insisting on a new column called VEHICLE ID. We'll make VEHICLE_ID the primary key. We then have the following table. CREATE TABLE student_vehicles (vehicle_id NUMBER, state VARCHAR2(2), tag_no VARCHAR2(10), vehicle_desc VARCHAR2(20), student_id VARCHAR2(10), parking_sticker VARCHAR2(10)) TABLESPACE student_data; ALTER TABLE student_vehicles ADD CONSTRAINT pk_student_vehicles PRIMARY KEY (vehicle_id) USING INDEX TABLESPACE student_index; CREATE SEQUENCE student_vehicles_pk_seq; Suppose we have the same sample data as Section 3.1.6, "Concatenated Primary Key." The primary key, VEHICLE_ID, is a sequence number that starts with 1. After adding three vehicles we have the following data. VEH_ID STATE TAG_NO VEHICLE_DESC STUDENT_ID PARKING_STICKER ------ ----- -------- ------------ ---------- --------------- 1 CA CD 2348 1977 Mustang A103 C-101-AB-1 2 NY MH 8709 1989 GTI A104 C-101-AB-2 3 NY JR 9837 1981 Civic A104 C-101-AB-3 The following SQL queries the table using the primary key column in the query. This query will use the index. SELECT * FROM student_vehicles WHERE vehicle_id = '1'; The execution plan, shown here, will include the index. Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'STUDENT_VEHICLES' 2 1 INDEX (UNIQUE SCAN) OF 'PK_STUDENT_VEHICLES' (UNIQUE) What if the application code relies on the columns for state and license number? This is a likely possibility. A lookup of information based on a state and license plate number could be dependent on fields selected on an HTML form. The application developer could have used the primary key, but chose to use the other columns. The following SQL will return the same information as the previous select statement. SELECT * FROM students WHERE state = 'CA' AND tag_no = 'CD 2348'; The difference is that the first query will likely run faster. If there are a significant number of students, the first query will use the index of the primary key. The second query will not use an index because there is no index on columns STATE and TAG_NO. The execution plan shows a table scan that will cause considerable wait time for the end user. Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF 'STUDENT_VEHICLES' When a pseudo key is created, as earlier, and there is a natural primary key in the table, there is the risk that the application developed will take fields, such as STATE and TAG NO from a form (i.e., an HTML form or client GUI form) and use those fields to query the database. When this happens, the performance of that query is slow unless actions are taken to identify these table scans and resolve them with additional indexes. The decision to add a pseudo key is not being judged here ”the issue is to recognize when and if the primary key, whatever that key may be, is being used and when it is not being used. There are three options when the application is not using the sequence primary key.
Why would someone choose to use a sequence rather than columns that, in the real world, identify a unique row? This is a decision made by the data modeler. Maybe the natural primary key is many columns ”financial applications that deal with financial instruments and trading of those instruments have so many attributes that the natural primary key of a table is many columns. That doesn't mean those columns can't be a primary key. An excessive number of columns in a primary key means large indexes and possible foreign keys that, if we have indexes on those, means larger indexes. Many columns in the primary key may not be desirable. Secondly, maybe the modeler has doubts as to whether, in the future, those columns will remain unique. Sometimes end users say the data is unique; the data modeler creates a primary key only to find out later that the real data does, in fact, contain duplicates. So, reverting to a concatenated primary key may not be the first choice. Suppose you stick with the pseudo key, such as VEHICLE_ID. Given this approach, we can still consider the business rule that, within the STUDENT_VEHICLES table, the STATE and TAG_NO are still unique and you can construct a UNIQUE constraint on those columns. The UNIQUE constraint does generate an index and consequently the query following will have the potential to perform at optimal levels with the use of the index generated through the unique constraint. SELECT * FROM student_vehicles WHERE state = 'CA' AND tag_no = 'CD 2348'; The DDL for the scenario to implement the pseudo sequence plus a concatenated unique constraint on STATE and TAG_NO is the following. CREATE TABLE student_vehicles (vehicle_id NUMBER, state VARCHAR2(2), tag_no VARCHAR2(10), vehicle_desc VARCHAR2(20), student_id VARCHAR2(10), parking_sticker VARCHAR2(10)) TABLESPACE student_data; ALTER TABLE student_vehicles ADD CONSTRAINT pk_student_vehicles PRIMARY KEY (vehicle_id) USING INDEX TABLESPACE student_index; CREATE SEQUENCE student_vehicles_pk_seq; ALTER TABLE student_vehicles ADD CONSTRAINT uk_student_vehicles_state_tag UNIQUE (state, tag_no) USING INDEX TABLESPACE student_index; If you do not expect state and license number to be or remain unique, you can always create a nonunique index on these columns. So, rather than add a unique constraint as earlier, replace that with the following, which only creates an index. CREATE INDEX student_vehicles_state_tag ON student_license(state, tag_no) TABLESPACE student_index; In summary, when your approach to primary keys is, by default, to always use sequence-generated primary keys, consider how you query the table. Are there attributes within the table to which a unique constraint can be applied? If so, create a unique constraint. For all other columns, look at how the table is accessed and add additional indexes to optimize performance. 3.1.8 Enable, Disable, and DropYou can drop a primary key constraint with the following. ALTER TABLE <table_name> DROP CONSTRAINT <constraint_name>; This drops the constraint and drops the index associated with that constraint. Oracle will not allow you to drop a primary key to which there is a referencing foreign key. If a table has a referencing foreign key you will get this error. ORA-02273: this unique/primary key is referenced by some foreign keys You can DROP CASCADE the constraint. This will drop the primary key constraint and all foreign keys that reference that parent. This does not require that the foreign key constraint be declared with the CASCADE option. You can always drop a primary key with the CASCADE option, but it is permanent. The primary key and foreign key constraints are deleted from the data dictionary. The CASCADE option is: ALTER TABLE state_lookup DROP CONSTRAINT state_lookup CASCADE; You can check all referencing foreign keys to a primary key constraint with the script MY_CHILDREN_ARE in Chapter 5, Section 5.6.5. A less drastic measure is to disable a constraint. The CASCADE restriction applies as well to disabling constraints. If there are referencing foreign keys, you can disable a primary key constraint with: ALTER TABLE state_lookup DISABLE CONSTRAINT state_lookup CASCADE; A disabled constraint is still defined in the data dictionary, it is just not being enforced. Furthermore, the status is set to DISABLED. With the CASCADE option, the status of the primary key and all referencing foreign key constraints are set to a DISABLED status. The index, after a primary key constraint is disabled, is gone ”deleted from the data dictionary; however, it is immediately rebuilt when the constraint is enabled. ALTER TABLE state_lookup ENABLE CONSTRAINT pk_state_lookup; This ALTER TABLE statement recreates the index and sets the primary key constraint to ENABLED. The foreign key constraints are still disabled. Each of these has to be enabled with: ALTER TABLE students ENABLE CONSTRAINT fk_students_state; An index on a primary key column cannot be dropped ”it would be dropped with a DROP or DISABLE constraint. Indexes can be rebuilt with no affect on the constraint with: ALTER INDEX pk_state_lookup REBUILD; The ENABLE and DISABLE keywords can be appended to any constraint declaration. Attaching DISABLE creates the constraint and sets the state to DISABLED. It can be enabled any time. The ENABLE keyword is the default for all constraint clauses. 3.1.9 Deferrable OptionA primary key constraint can be created with the option DEFERRABLE. This option permits an application program to disable the constraint during a load. The assumption is that manipulation of the table will occur, but when the load is complete the data will conform to the rules of the primary key constraint. The application will COMMIT the loaded data. At that COMMIT, the constraint is enforced. Should the program have left invalid data in the table ”data that violates the constraint ”the transaction is rolled back.
The DEFERRABLE option is created with the following syntax ”a sample table PARENT is created with two columns. CREATE TABLE parent (parent_id NUMBER(2), parent_desc VARCHAR2(10)); ALTER TABLE parent ADD CONSTRAINT pk_parent PRIMARY KEY (parent_id) DEFERRABLE; The aforementioned constraint definition is the same as this next statement: ALTER TABLE parent ADD CONSTRAINT pk_parent PRIMARY KEY (parent_id) DEFERRABLE INITIALLY DEFERRED; Scenario 1 is shown with the following code. This PL/SQL block loads duplicate data. After the load, but before the COMMIT, a duplicate is DELETED. This block completes with success because the duplicate was removed prior to the commit. DECLARE BEGIN EXECUTE IMMEDIATE 'SET CONSTRAINTS ALL DEFERRED'; INSERT INTO parent values (1,'A'); INSERT INTO parent values (1,'B'); INSERT INTO parent values (3,'C'); INSERT INTO parent values (4,'D'); DELETE FROM parent WHERE parent_desc = 'B'; COMMIT; END; If this block had not removed the duplicate, an Oracle error would occur. That error is the same error as a primary key constraint violation. The following block accomplishes the same task, loading the same data. This scenario does not use the deferrable option. Rather, all good data remains in the PARENT table. BEGIN BEGIN INSERT INTO parent values (1, 'A'); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN null; END; BEGIN INSERT INTO parent values (1, 'B'); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN null; END; BEGIN INSERT INTO parent values (3, 'C'); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN null; END; BEGIN INSERT INTO parent values (4, 'D'); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN null; END; COMMIT; END; The data remaining in the table is the first, third, and fourth row. The DEFERRABLE option loads everything. Then the constraint is applied and the transaction is committed or rolled back. The second scenario provides the option to roll back a duplicate insert. Alternatively, the application can skip the duplicate insert and continue processing good data. The deferrable option should not be used to permit the table to be a work area. Scenario 1 seems to permit the application to load data, and then manipulate that data, cleaning out bogus records. Hopefully, the data will be good upon a commit. Alternatives exist. Temporary tables can be created for loading data for manipulation. After manipulation and cleansing of the data, the rows are inserted into the production table. Temporary tables can persist for the life of a session or transaction. The following DDL creates a temporary table that can be used as a private in-memory table that persists for the duration of a transaction. CREATE GLOBAL TEMPORARY TABLE parent_temp (parent_id NUMBER(2), parent_desc VARCHAR2(10)) ON COMMIT DELETE ROWS; Scenario 1 can now use the temporary table for massaging the raw data. When data is moved into the permanent table, the commit deletes rows from the temporary table. The temporary table is private to this transaction. The following PL/SQL block is Scenario 1 using a temporary table with no deferrable option. BEGIN INSERT INTO parent_temp values (1,'A'); INSERT INTO parent_temp values (1,'B'); INSERT INTO parent_temp values (3,'C'); INSERT INTO parent_temp values (4,'D'); DELETE FROM parent_temp WHERE parent_desc = 'B'; INSERT INTO parent SELECT * FROM parent_temp; COMMIT; END; The DEFERRABLE option enforces the constraint with each DML statement. This is the default behavior of the constraint without the DEFERRABLE option; however, the DEFERRABLE does provide for the following:
The DEFERRABLE option can be declared with the following attribute: DEFERRABLE INITIALLY DEFERRED The INITIALLY DEFERRED feature defaults to enforcing the constraint only when a transaction commits. When this is set, each DML statement is not individually enforced. Enforcement takes place only when the transaction completes. This option provides the following:
The following summarizes DEFERRABLE option.
As always, you can disable the constraint and then enable it with ALTER TABLE commands. 3.1.10 NOVALIDATEThe NOVALIDATE option allows nonconforming data to be loaded and left in the table while the rule of the constraint is enabled only for future inserts. This option can be used in data warehouse systems where management must have historical data for analysis. Historical data will frequently violate present day business rules. To load noncompliant data, the constraint must be initially created with the deferrable option. Prior to loading historical data, the constraint must be disabled. The following creates a table with a deferrable primary key constraint. Prior to the load, the constraint is disabled. Afterward, the constraint is enabled with the NOVALIDATE option. From this point forward, the historical data remains but all new inserts will be constrained to the rules of the primary key. CREATE TABLE parent (parent_id NUMBER(2), parent_desc VARCHAR2(10)); ALTER TABLE parent ADD CONSTRAINT pk_parent PRIMARY KEY (parent_id) DEFERRABLE; ALTER TABLE parent DISABLE CONSTRAINT pk_parent; BEGIN INSERT INTO parent values (1,'A'); INSERT INTO parent values (1,'B'); INSERT INTO parent values (3,'C'); INSERT INTO parent values (4,'D'); END; ALTER TABLE parent ENABLE NOVALIDATE CONSTRAINT pk_parent; After the aforementioned PL/SQL block executes, duplicates exist in the tables; however, all new inserts must conform to the primary key. 3.1.11 Error Handling in PL/SQLA duplicate insert in PL/SQL is easily captured with the PL/SQL built-in exception. The exception name is DUP_VAL_ON_INDEX Including an exception handler will allow an application to handle the rare case of a duplicate. The following stored procedure returns a Boolean, indicating a failure if the insert is a duplicate. CREATE OR REPLACE FUNCTION insert_parent (v_id NUMBER, v_desc VARCHAR2) RETURN BOOLEAN IS BEGIN INSERT INTO parent VALUES (v_id, v_desc); return TRUE; EXCEPTION WHEN DUP_VAL_ON_INDEX THEN return FALSE; END; |
EAN: 2147483647
Pages: 111