• Oracle Insert Primary Key
• Create Table Sql Primary Key
• Auto Increment Primary Key Oracle Sql
• Generate Primary Key Sql Developer Free
• Insert Primary Key Sql Oracle

In this chapter, you will create and use the types of database objects that were discussed in 'Querying and Manipulating Data'.

In this tutorial, you will use SQL Developer to create objects for a simplified library database, which will include tables for books, patrons (people who have library cards), and transactions (checking a book out, returning a book, and so on). The tables are deliberately oversimplified for this tutorial. Oracle Sql Developer Create Table Primary Key. By Erika Dwi Posted on February 27, 2020 Category: Developer; Sql developer migrating third party oracle sql developer reports new features pl sql 13 0 allround oracle sql developer modeler oracle base administration archives. To add a primary key constraint using the SQL Developer interface: You will add a primary key constraint to the performanceparts table using the SQL Developer graphical interface. You could also use the Edit Table window, as in the NOT NULL constraint, to accomplish this task.

Note that the statements CREATE TABLE,ALTER TABLE, DROP TABLE, and so on, use an implicit commit, and cannot be rolled back.

This chapter contains the following sections:

Using Data Types

Data types associate a set of properties with values so you can use these values in the database. Depending on the data type, Oracle Database can perform different kinds of operations on the information in the database. For example, it is possible to calculate a sum of numeric values but not characters.

Oracle Database supports many kinds of data types, including the most common VARCHAR2(length), NUMBER(precision, scale), DATE, and also CHAR(length), CLOB, TIMESTAMP, and others. As you create a table, you must specify data types for each of its columns and (optionally) indicate the longest value that can be placed in the column.

Some of the data types and their properties you will use here include the following:

• The VARCHAR2 stores variable-length character literals, and is the most efficient option for storing character data. When creating a VARCHAR2 column in a table, you must specify the maximum number of characters in a column, which is a length between 1 and 4,000. In the employeestable, the first_name column has a VARCHAR(20) data type and the LAST_NAME column has a VARCHAR2(25) data type.

• An option to the VARCHAR2 data type, NVARCHAR2 stores Unicode variable-length character literals.

• The CHAR data type stores fixed-length character literals; it uses blanks to pad the value to the specified string length, which is between 1 and 2,000.

  An option to the CHAR2 data type, NCHAR stores Unicode fixed-length character literals.

• The CLOB data type is a character large object data type that contains single-byte or multibyte characters. The maximum size of a CLOB is (4 gigabytes - 1) x (database block size).

• The NUMBER data type stores zero, and integers and real numbers as positive and negative fixed numbers with absolute values between 1.0 x 10^-130 and 1.0 x 10¹²⁶ using a fixed-point or floating-point format, with decimal-point precision. Oracle guarantees that NUMBER data types are portable between different operating systems, and recommends it for most cases where you need to store numeric data.

  You can use the precision option to set the maximum number of digits in the number, and the scale option to define how many of the digits are to the right of the decimal separator. In the employeestable, the salary column is defined as NUMBER(8,2), providing 6 digits for the primary unit of currency (dollars, pounds, marks, and so on) and 2 digits for the secondary unit of currency (cents, pennies, pfennigs, and so on).

• For floating-point numbers, Oracle Database provides the numeric BINARY_FLOAT and BINARY_DOUBLE data types as enhancements to the basic NUMBER data type. BINARY_FLOAT (32-bit IEEE 754 format) ranges in absolute value between 1.17549 x e^-38F and 3.40282 x e^38Fand BINARY_DOUBLE (64-bit IEEE 754 format) ranges in absolute value between 2.22507485850720 x e^-308 and 1.79769313486231 x e³⁰⁸. Both use binary precision that enables faster arithmetic calculations and often reduces storage requirements.

• The DATE data type stores point-in-time values, dates and times; this includes the century, year, month, day, hours, minutes, and seconds. The valid date range is from January 1, 4712 BC to December 31, 9999 AD. Oracle Database supports many different formats for displaying date and time values. In the employeestable, the hire_date column is defined as a DATE.

• The TIMESTAMP data type stores values that are precise to fractional seconds, and is therefore useful in applications that must track event order.

• The TIMESTAMP WITH TIME ZONE data type stores time zone information, and can therefore record date information that must be coordinated across several geographic regions.

Creating and Using Tables

Tables are the basic unit of data storage in an Oracle database, and hold all user-accessible data. Tables are two-dimensional objects made up of vertical columns that represent the fields of the table and horizontal rows that represent the values for each record in the table.

In this section, you will create all the necessary tables and other schema objects to implement an employee performance evaluation process for the existing hr schema.

Creating a Table

To implement the employee evaluation process, you will need to establish three tables, performance_parts, evaluations, and scores.

• The performance_partstable lists the categories of performance measurements, and the relative weight for each item.

• The evaluationstable will contain the employee's information, evaluation date, and the job, manager and department at the time of evaluation. You must preserve this information in this table because at any point in the future, the employee may change job designation, manager, or department.

• The scorestable contains the scores assigned to each performance category for each evaluation.

To create a table using SQL Developer interface:

You will create the performance_partstable using the SQL Developer graphical interface.

1. In the Connections navigation hierarchy, click the plus sign (+) next to hr_conn to expand the list of schema objects.

2. Right-click Tables.

3. Select New Table.

   
   Description of the illustration table_create_1.gif
   

4. In the Create Table window, enter the following information:

   • For Schema, select HR.

   • For Name, enter PERFORMANCE_PARTS.

   
   Description of the illustration table_create_2.gif
   

5. Click the default column that was created with the table.

6. Enter the information for the first column in the table as follows:

   • For Column Name, enter PERFORMANCE_ID.

   • For Type, enter VARCHAR2.

   • For Size, enter 2.

   Leave the value of Not Null and Primary Key properties. You will come back to this later, in 'Ensuring Data Integrity'.

   
   Description of the illustration table_create_3.gif
   

7. Enter information for the second column as follows:

   Click Add Column.

   • For Column Name, enter NAME.

   • For Type, enter VARCHAR2.

   • For Size, enter 80.

8. Enter information for the third column as follows:

   Click Add Column.

   • For Column Name, enter WEIGHT.

   • For Type, enter NUMBER.

9. Click OK.

   SQL Developer generates the new table, performance_parts.

10. In the Connections navigation hierarchy, click the plus sign (+) next to Tables to expand the list of tables.

    performance_parts is a new table in the hr schema, listed between locations and regions.

You just created a new table, performance_parts. If you click the table, the table will appear on the right side of the SQL Developer window, showing its new columns. If you click the SQL tab, you will see the script that created this table.

In Example 3-1, you will create the evaluations table by entering the information directly in the SQL Worksheet pane.

Example 3-1 Creating a Table in SQL Script

The results of the script follow.

You created a new table, evaluations. If you click the table, the table will appear on the right side of the SQL Developer window, showing its new columns. If you click the SQL tab, you will see the script that created this table. You may need to click the Refresh icon.

In Example 3-2, you will create another table, scores, by entering the information in the SQL Worksheet pane.

Example 3-2 Creating the SCORES Table

The results of the statement follow.

You created a new table, scores. If you click the table, the table will appear on the right side of the SQL Developer window, showing its new columns. If you click the SQL tab, you will see the script that created this table. You may need to click the Refresh icon.

See Also:

• Oracle Database SQL Language Reference for information on the CREATE TABLE statement

Ensuring Data Integrity

The data in the table must satisfy the business rules that are modeled in the application. Many of these rules can be implemented throughintegrityconstraints that use the SQL language to explicitly state what type of data values are valid for each column.

When an integrity constraint applies to a table, all data in the table must conform to the corresponding rule, so when your application includes a SQL statement that inserts or modifies data in the table, Oracle Database automatically ensures that the constraint is satisfied. If you attempt to insert, update, or remove a row that violates a constraint, the system generates an error, and the statement is rolled back. If you attempt to apply a new constraint to a populated table, the system may generate an error if any existing row violates the new constraint.

Because Oracle Database checks that all the data in a table obeys an integrity constraint much faster than an application can, you can enforce the business rules defined by integrity constraints more reliably than by including this type of checking in your application logic.

See Also:

• Oracle Database SQL Language Reference for information about integrity constraints

Understanding Types of Data Integrity Constraints

There are five basic types of integrity constraints:

• A NOT NULL constraint ensures that the column contains data (it is not null).

• Aunique constraint ensures that multiple rows do not have the same value in the same column. This type of constraint can also be used on combination of columns, as a composite unique constraint. This constraint ignores null values.

• Aprimary keyconstraint combines NOT NULL and UNIQUE constraints in a single declaration; it prevents multiple rows from having the same value in the same column or combination of columns, and prevents null values.

• A foreign key constraint requires that for each value in the column on which the constraint is defined, there must be a matching value in a specified other table and column.

• A checkconstraint ensures that a value satisfies a specified condition. Use check constraints when you need to enforce integrity rules based on logical expressions, such as comparisons. Oracle recommends that you never use check constraints when other types of constraints can provide the necessary checking.

Adding Integrity Constraints

You will now add different types of constraints to the tables you created in 'Creating a Table'.

To Add a NOT NULL Constraint Using the SQL Developer Interface:

You will add a NOT NULL constraint to the table using the SQL Developer graphical interface.

1. In the Connections navigation hierarchy, click the plus sign (+) next to Tables to expand the list of tables.

2. Right-click the performance_parts table.

3. Select Edit.

   
   Description of the illustration constraint_create_5.gif
   

4. In the Edit Table window, follow these steps:

   • In the Edit Table window, click Columns.

   • In the Columns list, select NAME.

   • In the Column Properties section, check Cannot be NULL.

     Click OK.

     
     Description of the illustration constraint_create_6.gif
     

5. In the Confirmation window, click OK.

   You have now created a NOT NULL constraint for the name column of the performance_partstable.

The definition of the name column in the performance_parts table is changed to the following; note that the constraint is automatically enabled.

Example 3-3 shows how you can add another NOT NULL constraint to the performance_partstable by entering the required information directly in the SQL Statement window.

Example 3-3 Adding a NOT NULL Constraint in SQL Script

The results of the script follow.

You just created a NOT NULL constraint for column weight of the performance_parts table. If you click the SQL tab, you will see that the definition of the weight column changed. You may need to click the Refresh icon.

To add a unique constraint using the SQL Developer interface:

You will add a unique constraint to the scorestable using the SQL Developer graphical interface. You could also use the Edit Table window, as in the NOT NULL constraint, to accomplish this task.

1. In the Connections navigation hierarchy, click the plus sign (+) next to Tables to expand the list of tables.

2. Right-click the scorestable.

3. Select Constraint, and then select Add Unique.

   
   Description of the illustration constraint_create_3.gif
   

4. In the Add Unique window, enter the following information:

   • Set the constraint name to SCORES_EVAL_PERF_UNIQUE.

   • Set Column 1 to EVALUATION_ID.

   • Set Column 2 to PERFORMANCE _ID.

   Click Apply.

   
   Description of the illustration constraint_create_4.gif
   

5. In the Confirmation window, click OK.

   You have now created a unique constraint for the scorestable.

   The following SQL statement was added to your table definition:

To add a primary key constraint using the SQL Developer interface:

You will add a primary key constraint to the performance_partstable using the SQL Developer graphical interface. You could also use the Edit Table window, as in the NOT NULL constraint, to accomplish this task.

1. In the Connections navigation hierarchy, click the plus sign (+) next to Tables to expand the list of tables.

2. Right-click the performance_parts table.

3. Select Constraint, and then select Add Primary Key.

   
   Description of the illustration constraint_create_1.gif
   

4. In the Add Primary Key window, enter the following information:

   • Set the primary key name to PERF_PERF_ID_PK.

   • Set Column 1 to PERFORMANCE_ID.

   Click Apply.

   
   Description of the illustration constraint_create_2.gif
   

5. In the Confirmation window, click OK.

   You have now created a primary key constraint for the performance_partstable.

The following SQL statement was added to your table definition:

In Example 3-4, you will create a primary key constraint on the evaluationstable by entering the required information directly in the SQL Statement window.

Example 3-4 Adding a Primary Key Constraint in SQL Script

The results of the script follow.

You just created a primary key eval_eval_id_pk on the evaluationstable. If you click the SQL tab, you will see the following SQL statement was added to your table definition. You may need to click the Refresh icon.

To add a foreign key constraint using the SQL Developer interface:

You will add two foreign key constraints to the scorestable using the SQL Developer graphical interface. You could also use the Edit Table window, as in the NOT NULL constraint, to accomplish this task.

1. In the Connections navigation hierarchy, the plus sign (+) next to Tables to expand the list of tables.

2. Right-click the scorestable.

3. Select Constraint, and then select Add Foreign Key.

   
   Description of the illustration constraint_create_7.gif
   

4. In the Add Foreign Key window, enter the following information:

   • Set the foreign key name to SCORES_EVAL_FK.

   • Set Column Name to EVALUATION_ID.

   • Set Reference Table Name to EVALUATIONS.

   • Set Referencing Column to EVALUATION_ID.

   Click Apply.

   
   Description of the illustration constraint_create_8.gif
   

5. In the Confirmation window, click OK.

   You have now created a foreign key constraint on the evalution_id column from the evaluationstable.

6. Add another foreign key constraint by repeating steps 2 through 5, with the following parameters:

   • Set the foreign key name to SCORES_PERF_FK.

   • Set Column Name to PERFORMANCE_ID.

   • Set Reference Table Name to PERFORMANCE_PARTS.

   • Set Referencing Column to PERFORMANCE_ID.

   Click Apply.

The following SQL statements were added to your table definition:

In Example 3-5, you will create a foreign key constraint on the evaluationstable by entering the required information directly in the SQL Statement window.

Example 3-5 Adding a Foreign Key Constraint in SQL Script

The results of the script follow.

You have now created a foreign key constraint on the employee_id column from the employeestable. If you click the SQL tab, you will see the following SQL statement was added to your table definition. You may need to click the Refresh icon.

To add a check constraint using the SQL Developer interface:

You will add a check constraint to the scorestable using the SQL Developer graphical interface. You could also use the Edit Table window, as in the NOT NULL constraint, to accomplish this task.

1. In the Connections navigation hierarchy, the plus sign (+) next to Tables to expand the list of tables.

2. Right-click the scorestable.

3. Select Constraint, and then select Add Check.

   
   Description of the illustration constraint_create_9.gif
   

4. In the Add Check window, enter the following information:

   • Set the Constraint Name to SCORE_VALID.

   • Set Check Condition to score >=0 and score <=9.

   • Set Status to ENABLE.

   Click Apply.

   
   Description of the illustration constraint_create_10.gif
   

5. In the Confirmation window, click OK.

   You have now created a check constraint on the score column of the scorestable.

The following SQL statement was added to your table definition:

Adding Data to a Table, Modifying, and Deleting

You can use SQL Developer to enter data into tables, to edit, and to delete existing data. The following tasks will show these processes for the performance_parts table.

To add data to a table using the SQL Developer interface:

Follow these steps to add rows of data to the performance_partstable

1. In the Connections navigation hierarchy, double-click the performance_parts table.

2. Click the Data tab in the performance_parts table display.

3. In the Data pane, click the New Record icon.

   
   Description of the illustration data_add_1.gif
   

4. In the new row, add the following information; you can click directly into the column, or tab between columns:

   • Set PERFORMANCE_ID to 'WM'

   • Set NAME to 'Workload Management'

   • Set WEIGHT to 0.2

   Press the Enter key.

   
   Description of the illustration data_add_2.gif
   

5. Add a second row with the following information: set PERFORMANCE_ID to 'BR, set NAME to 'Building Relationships, and set WEIGHT to 0.2.

   Press the Enter key.

6. Add a third row with the following information: set PERFORMANCE_ID to 'CF', set NAME to 'Customer Focus', and set WEIGHT to 0.2.

   Press the Enter key.

7. Add a fourth row with the following information: set PERFORMANCE_ID to 'CM', set NAME to 'Communication', and set WEIGHT to 0.2.

   Press the Enter key.

8. Add a fifth row with the following information: set PERFORMANCE_ID to 'TW', set NAME to 'Teamwork', and set WEIGHT to 0.2.

   Press the Enter key.

9. Add a sixth row with the following information: set PERFORMANCE_ID to 'RD', set NAME to 'Results Orientation', and set WEIGHT to 0.2.

   Press the Enter key.

10. Click the Commit Changes icon.

11. Review and close the Data Editor Log window.

    
    Description of the illustration data_add_3.gif
    

12. Review the new data in the table performance_parts.

    
    Description of the illustration data_add_4.gif
    

You have added 6 rows to the performance_parts table.

To modify table data using the SQL Developer interface:

Follow these steps to change data to the performance_partstable.

1. In the Connections navigation hierarchy, double-click the performance_parts table.

2. Click the Data tab in the performance_partstable display.

3. In the Data pane, in the 'Workload Management' row, click the weight value, and enter a new value for '0.3'.

   In the 'Building Relationships' row, click the weight value, and enter a new value for '0.15'.

   In the 'Customer Focus' row, click the weight value, and enter a new value for '0.15'.

   
   Description of the illustration data_add_5.gif
   

4. Press the Enter key.

5. Click the Commit Changes icon.

6. Review and close the Data Editor Log window.

   
   Description of the illustration data_add_6.gif
   

You have now changed values in three rows of the performance_partstable.

To delete table data using the SQL Developer interface:

Imagine that in the company modeled by the hr schema, management decided that the categories Workload Management and Results Orientation had too much overlap. You will now remove the row 'Results Orientation' from the performance_partstable.

1. In the Connections navigation hierarchy, double-click the performance_parts table.

2. Click the Data tab in the performance_partstable display.

3. In the Data pane, click the 'Results Orientation' row.

4. Click the Delete Selected Row(s) icon.

   
   Description of the illustration data_add_7.gif
   

5. Click the Commit Changes icon.

6. Review and close the Data Editor Log window.

   
   Description of the illustration data_add_8.gif
   

You have now removed a row from the performance_partstable.

See Also:

Indexing Tables

When you define a primary key on a table, Oracle Database implicitly creates an index on the column that contains the primary key. For example, you can confirm that an index was created for the evaluationstable on its primary key, by looking at its Indexes pane.

Description of the illustration index_1.gif

In this section, you will learn how to add different types of indexes to the tables you created earlier.

To create an index using the SQL Developer interface:

Follow these steps to create a new index for the evaluationstable.

1. In the Connections navigation hierarchy, right-click the evaluations table.

2. Select Index and then select Create Index.

   Alternatively, in the Connections navigation hierarchy, you can right-click Indexes and select New Index.

   
   Description of the illustration index_2.gif
   

3. In the Create Index window, enter the following parameters:

   • Ensure that the Schema is set to HR.

   • Set the Name to EVAL_JOB_IX.

   Click the Add Column Expression icon, which looks like a 'plus' sign.

   • Set the Column Name or Expression to JOB_ID.

   • Set the Order to ASC.

   Click OK.

   
   Description of the illustration index_3.gif
   

You have now created a new index EVAL_JOB_IX on the column JOB_ID in the evaluationstable. You can see this index by finding it in the list of Indexes in the Connections navigation hierarchy, or by opening the evaluations table and browsing to the Indexes tab. The following script is the equivalent SQL statement for creating this index.

To modify an index using SQL Developer interface:

Follow these steps to reverse the sort order of the EVAL_JOB_IXindex.

1. In the Connections navigation hierarchy, the plus sign (+) to expand Indexes.

2. Right-click EVAL_JOB_IX, and select Edit.

   
   Description of the illustration index_4.gif
   

3. In the Edit Index window, change Order to DESC.

   Click OK.

You changed the index. The following script is the equivalent SQL statement for creating this index:

To delete an index using SQL Developer interface:

Following steps to delete the EVAL_JOB_IXindex.

1. In the Connections navigation hierarchy, the plus sign (+) to expand Indexes.

2. Right-click EVAL_JOB_IX, and select Drop.

   
   Description of the illustration index_5.gif
   

3. In the Drop window, click Apply.

4. In the Confirmation window, click OK.

You deleted the index EVAL_JOB_IX. The following script is the equivalent SQL statement for dropping this index.

See Also:

• Oracle Database SQL Language Reference for information on the CREATE INDEX statement

• Oracle Database SQL Language Reference for information on the ALTER INDEX statement

• Oracle Database SQL Language Reference for information on the DROP INDEX statement

Dropping Tables

Sometimes it becomes necessary to delete a table and all its contents from your schema. To accomplish this, you must use the SQL statement DROP TABLE. You will use the tables that you already created to learn other concepts, so create a simple table that you can subsequently delete by running the following script in the SQL Statement window:

To delete a table using the SQL Developer interface:

Follow these steps to delete TEMP_TABLE from the hr schema.

1. In the Connections navigation hierarchy, right-click TEMP_TABLE.

2. Select Table, and then select Drop.

   
   Description of the illustration index_6.gif
   

3. In the Drop window, click Apply.

4. In the Confirmation window, click OK.

You deleted the table TEMP_TABLE. The following script is the equivalent SQL statement for dropping this table.

See Also:

• Oracle Database SQL Language Reference for information on the DROP TABLE statement

Using Views

Views are logical tables based on one or more tables or views. Views are particularly useful if your business needs include frequent access to information that is stored in several different tables.

Oracle Insert Primary Key

Creating a View

The standard syntax for creating a view follows:

To create a view using the SQL Developer interface:

Follow these steps to delete create a new view from the hr schema.

1. In the Connections navigation hierarchy, right-click Views.

2. Select New View.

   
   Description of the illustration view_1.gif
   

3. In the Create View window, enter the following parameters:

   • Ensure that Schema is set toHR.

   • Set Name to SALESFORCE.

   • Set the SQL Query to the following:

4. In SQL Parse Results, click Test Syntax.

   
   Description of the illustration view_2.gif
   

5. Click OK.

You created a new view. The equivalent SQL statement for creating this view follows:

In Example 3-6, you will create a view of all employees in the company and their work location, similar to the query you used in 'Using Character Functions'.

Example 3-6 Creating a View in SQL Script

The results of the script follow.

You have now created new view that relies on information in 4 separate tables, or a 4-wayJOIN. In the Connections navigation hierarchy, if you click the 'plus' sign next to Views, you will see emp_locations.

Updating a View

To change the properties of a view in SQL Developer interface:

You will change the salesforce view by adding to it the employees in the Marketing department, and then rename the view to sales_marketing.

1. In the Connections navigation hierarchy, right-click the salesforce view.

2. Select Edit.

   
   Description of the illustration view_3.gif
   

3. In the Edit View window, change the SQL Query by adding the following to the last line: 'OR department_id = 20'.

   Click Test Syntax.

   Click OK.

   
   Description of the illustration view_4.gif
   

4. To rename the view, right-click salesforce and select Rename.

   
   Description of the illustration view_5.gif
   

5. In the Rename window, set New View Name to sales_marketing.

   Click Apply.

   
   Description of the illustration view_6.gif
   

6. In the Confirmation window, click OK.

You changed the view. The equivalent SQL statements for changing and renaming the view are:

Dropping a View

To drop a view using the SQL Developer interface:

You will use the DROP VIEW statement to delete thesales_marketing view.

1. In the Connections navigation hierarchy, right-click the sales_marketing view.

2. Select Drop.

   
   Description of the illustration view_7.gif
   

3. In the Drop window, click Apply.

4. In the Confirmation window, click OK.

You deleted the view. The equivalent SQL statement for dropping the view is:

See Also:

• Oracle Database SQL Language Reference for information on the CREATE VIEW statement

• Oracle Database SQL Language Reference for information on the DROP VIEW statement

Using Sequences

Sequences are database objects that generate unique sequential values, which are very useful when you need unique primary keys. The hr schema already has three such sequences: departments_seq, employees_seq, and locations_seq.

The sequences are used through these pseudocolumns:

• The CURRVAL pseudocolumn returns the current value of a sequence. CURRVAL can only be used after an initial call to NEXTVAL initializes the sequence.

• The NEXTVAL pseudocolumn increments the sequence and returns the next value. The first time that NEXTVAL is used, it returns the initial value of the sequence. Subsequent references to NEXTVAL increment the sequence value by the defined increment, and return the new value.

Note that a sequence is not connected to any other object, except for conventions of use. When you plan to use a sequence to populate the primary key of a table, Oracle recommends that you use a naming convention to link the sequence to that table. Throughout this discussion, the naming convention for such sequences is table_name_seq.

Creating a Sequence

You can create a sequence in the SQL Developer Interface, or using the SQL Statement window.

To create a sequence using the SQL Developer interface:

The following steps will create a sequence, evaluations_seq, that you can use for the primary key of the evaluationstable.

1. In the Connections navigation hierarchy, right-click Sequences.

2. Select New Sequence.

   
   Description of the illustration sequence_1.gif
   

3. In the New Sequence window, enter the following parameters:

   • Ensure that Schema is set to HR.

   • Set Name to EVALUATIONS_SEQ.

   In the Properties tab:

   • Set Increment to 1.

   • Set Start with to 1.

   • Check Order.

   Click OK.

   
   Description of the illustration sequence_2.gif
   

You have now created a sequence that can be used for the primary key of the evaluationstable. If you click '+' to expand the Sequence tree, you can see new sequence. The equivalent SQL statement is:

In Example 3-7, you will create another sequence by entering the required information directly in the SQL Statement window.

Example 3-7 Creating a Sequence Using SQL Script

The results of the script follow.

See Also:

• Oracle Database SQL Language Reference for information on the CREATE SEQUENCE statement

Dropping a Sequence

To delete a sequence, you must use the SQL statement DROP SEQUENCE. To see how a sequence can be deleted in SQL Developer, you can use the test_seq sequence you created earlier. If the new sequence does not appear in the Connections hierarchy navigator, click the refresh icon.

To drop a sequence:

Follow these steps to drop a sequence.

1. In the Connections navigator, right-click the test_seq sequence.

   
   Description of the illustration sequence_3.gif
   

2. In the Drop window, click Apply.

   
   Description of the illustration sequence_4.gif
   

3. In the Confirmation window, click OK.

You have now deleted the sequence test_seq. The equivalent SQL statement follows:

See Also:

• Oracle Database SQL Language Reference for information on the DROP SEQUENCE statement

Using Synonyms

A synonym is an alias for any schema object and can be used to simplify SQL statements or even obscure the names of actual database objects for security purposes. Additionally, if a table is renamed in the database (departments to divisions), you could create a departments synonym and continue using your application code as before.

To create a synonym using the SQL Developer interface:

The following steps will create a synonym, positions, that you can use in place of the jobs schema object.

1. In the Connections navigation hierarchy, right-click Synonyms.

2. Select New Synonym.

   
   Description of the illustration synonym_1.gif
   

3. In the New Synonym window, set the following parameters:

   • Ensure that Schema is set to HR.

   • Set Name to POSITIONS.

   In the Properties tab:

   • Select Object Based. This means that the synonym refers to a specific schema object, such as a table, a view, a sequence, and so on.

   • Set Object Based to JOBS.

   Click OK.

   
   Description of the illustration synonym_2.gif
   

You created a synonympositions for the jobstable. The equivalent SQL statement follows:

In Example 3-8, you use the new positions synonym in place of the jobstable name.

Example 3-8 Using a Synonym

The results of the query appear.

To drop a synonym:

Follow these steps to drop the positions synonym.

1. In the Connections navigator, right-click the positions synonym.

2. Select Drop.

   
   Description of the illustration synonym_4.gif
   

3. In the Drop window, click Apply.

   
   Description of the illustration synonym_5.gif
   

4. In the Confirmation window, click OK.

You deleted synonym positions. The equivalent SQL statement follows:

See Also:

• Oracle Database SQL Language Reference for information on the CREATE SYNONYM statement

• Oracle Database SQL Language Reference for information on the DROP SYNONYM statement

In this tutorial, you will use SQL Developer to create objects for a simplified library database, which will include tables for books, patrons (people who have library cards), and transactions (checking a book out, returning a book, and so on).

Note:

Other SQL Developer tutorials, including Oracle By Example (OBE) lessons, are available from the Start Page. If the tab for that page is not visible, click Help, then Start Page.

The tables are deliberately oversimplified for this tutorial. They would not be adequate for any actual public or organizational library. For example, this library contains only books (not magazines, journals, or other document formats), and it can contain no more than one copy of any book.

You will perform the following major steps:

1. Create a Table (BOOKS).

2. Create a Table (PATRONS).

3. Create a Table (TRANSACTIONS).

4. Create a Sequence.

5. Insert Data into the Tables.

6. Create a View.

7. Create a PL/SQL Procedure.

8. Debug a PL/SQL Procedure (optional).

9. Use the SQL Worksheet for Queries (optional).

Note:

To delete the objects that you create for this tutorial, you can use the DROP statements at the beginning of the script in Section 4.10, 'Script for Creating and Using the Library Tutorial Objects'.

Related Topics

4.1 Create a Table (BOOKS)

The BOOKS table contains a row for each book in the library. It includes columns of character and number types, a primary key, a unique constraint, and a check constraint. You will use the Create Table dialog box to create the table declaratively; the table that you create will be essentially the same as if you had entered the following statement using the SQL Worksheet:

To create the BOOKS table, connect to the database as the user in the schema you want to use for this tutorial. Right-click the Tables node in the schema hierarchy on the left side, select New Table, and enter the following information. (If a tab or field is not mentioned, do not enter anything for it. Be sure that the Advanced box is not checked when you start creating the table.)

For detailed information about the table dialog box and its tabs, see Section 5.52, 'Create Table (quick creation)' and Section 5.53, 'Create/Edit Table (with advanced options)'.

Schema: Specify your current schema as the schema in which to create the table.

Name: BOOKS

Create the table columns using the following information. After creating each column except the last one (rating), click Add Column to add the next column. (If you accidentally click OK instead of Add Column, right-click the BOOKS table in the Connections navigator display, select Edit, and continue to add columns.)

Column Name	Type	Size	Other Information and Notes
book_id	VARCHAR2	20	Primary Key (Automatically checks Not Null; an index is also created on the primary key column. This is the Dewey code or other book identifier.)
title	VARCHAR2	50	Not Null
author_last_name	VARCHAR2	30	Not Null
author_first_name	VARCHAR2	30
rating	NUMBER	(Librarian's personal rating of the book, from 1 (poor) to 10 (great))

After you have entered the last column (rating), check Advanced (next to Schema). This displays a pane for more table options. For this table, you will use the Unique Constraints and Check Constraints panes.

Unique Constraints pane

Click Add to add a unique constraint for the table, namely, that the combination of author_last_name and title must be unique within the table. (This is deliberately oversimplified, since most major libraries will have allow more than one copy of a book in their holdings. Also, the combination of last name and title is not always a 'foolproof' check for uniqueness, but it is sufficient for this simple scenario.)

Name: author_title_unique

In Available Columns, double-click TITLE and then AUTHOR_LAST_NAME to move them to Selected Columns.

Check Constraints pane

Click Add to add a check constraint for the table, namely, that the rating column value is optional (it can be null), but if a value is specified, it must be a number from 1 through 10. You must enter the condition using SQL syntax that is valid in a CHECK clause (but do not include the CHECK keyword or enclosing parentheses for the entire CHECK clause text).

Name: rating_1_to_10

Condition: rating is null or (rating >= 1 and rating <= 10)

Click OK to finish creating the table.

Go to Section 4.2, 'Create a Table (PATRONS)' to create the next table.

4.2 Create a Table (PATRONS)

The PATRONS table contains a row for each patron who can check books out of the library (that is, each person who has a library card). It includes an object type (MDSYS.SDO_GEOMETRY) column. You will use the Create Table dialog box to create the table declaratively; the table that you create will be essentially the same as if you had entered the following statement using the SQL Worksheet:

The use of single city_state_zip column for all that information is not good database design; it is done here merely to simplify your work in the tutorial.

The location column (Oracle Spatial geometry representing the patron's geocoded address) is merely to show the use of a complex (object) type.

To create the PATRONS table, if you are not already connected, connect to the database as the user for the schema you are using for this tutorial. Right-click the Tables node in the schema hierarchy on the left side, select New Table, and enter the following information. (If a tab or field is not mentioned, do not enter anything for it. Be sure that the Advanced box is not checked when you start creating the table.)

Schema: Specify your current schema as the schema in which to create the table.

Name: PATRONS

Create most of the table columns using the following information. After creating each column except the city_state_zip column, click Add Column to add the next column. (If you accidentally click OK instead of Add Column, right-click the PATRONS table in the Connections navigator display, select Edit, and continue to add columns.)

Column Name	Type	Size	Other Information and Notes
patron_id	NUMBER	Primary Key. (Unique patron ID number, with values to be created using a sequence that you will create)
last_name	VARCHAR2	30	Not Null
first_name	VARCHAR2	30
street_address	VARCHAR2	30
city_state_zip	VARCHAR2	30

The last column in the table (location) requires a complex data type, for which you must use the Columns tab with advanced options. Check Advanced (next to Schema). This displays a pane for selecting more table options.

In the Columns pane, click the city_state_zip column name, and click the Add Column (+) icon to add the following as the last column in the table.

Column Name	Type	Other Information and Notes
location	Complex type Schema: MDSYS Type: SDO_GEOMETRY	(Oracle Spatial geometry object representing the patron's geocoded address)

After you have entered the last column (location), click OK to finish creating the table.

Go to Section 4.3, 'Create a Table (TRANSACTIONS)' to create the next table.

4.3 Create a Table (TRANSACTIONS)

The TRANSACTIONS table contains a row for each transaction involving a patron and a book (for example, someone checking a book out or returning a book). It includes two foreign key columns. You will use the Create Table dialog box to create the table declaratively; the table that you create will be essentially the same as if you had entered the following statement using the SQL Worksheet:

To create the TRANSACTIONS table, if you are not already connected, connect to the database as the user for the schema you are using for this tutorial. Right-click the Tables node in the schema hierarchy on the left side, select New Table, and enter the following information. (If a tab or field is not mentioned, do not enter anything for it. Be sure that the Advanced box is not checked when you start creating the table.)

Schema: Specify your current schema as the schema in which to create the table.

Name: TRANSACTIONS

Create the table columns using the following information. After creating each column except the last one (transaction_type), click Add Column to add the next column. (If you accidentally click OK instead of Add Column, right-click the TRANSACTIONS table in the Connections navigator display, select Edit, and continue to add columns.)

Column Name	Type	Size	Other Information and Notes
transaction_id	NUMBER	Primary Key. (Unique transaction ID number, with values to be created using a trigger and sequence that will be created automatically)
patron_id	NUMBER	(Foreign key; must match a patron_id value in the PATRONS table)
book_id	VARCHAR2	20	(Foreign key; must match a book_id value in the BOOKS table)
transaction_date	DATE	(Date and time of the transaction)
transaction_type	NUMBER	(Numeric code indicating the type of transaction, such as 1 for checking out a book)

After you have entered the last column (transaction_type), check Advanced (next to Schema). This displays a pane for selecting more table options. For this table, you will use the Column Sequences and Foreign Keys panes.

Column Sequences pane

You have already specified TRANSACTION_ID as the primary key, and you will use this pane only to specify that the primary key column values are to be populated automatically. This convenient approach uses a trigger and a sequence (both created automatically by SQL Developer), and ensures that each transaction ID value is unique.

Column: TRANSACTION_ID

Sequence: New Sequence

Trigger: TRANSACTIONS_TRG (The default; a before-insert trigger with this name will be created automatically.)

/linux-generate-rsa-key-2048.html. Foreign Keys tab

1. Click Add to create the first of the two foreign keys for the TRANSACTIONS table.

Name: for_key_patron_id

Referenced Schema: Name of the schema containing the table with the primary key or unique constraint to which this foreign key refers. Use the schema you have been using for this tutorial.

Referenced Table: PATRONS

Referenced Constraint: PATRONS_PK (The name of the primary key constraint for the PATRONS table. Be sure that the Referenced Column on PATRONS displayed value is PATRON_ID.)

Associations: Local Column: PATRON_ID

Associations: Referenced Column on PATRONS: PATRON_ID

2. Click Add to create the second of the two foreign keys for the TRANSACTIONS table.

Name: for_key_book_id

Referenced Schema: Name of the schema containing the table with the primary key or unique constraint to which this foreign key refers. Use the schema you have been using for this tutorial.

Referenced Table: BOOKS

Referenced Constraint: BOOKS_PK (The name of the primary key constraint for the BOOKS table. Be sure that the Referenced Column on BOOKS displayed value is BOOK_ID.

Associations: Local Column: BOOK_ID

Associations: Referenced Column on BOOKS: BOOK_ID

3. Click OK to finish creating the table.

You have finished creating all the tables. To create a sequence for use in generating unique primary key values for the PATRONS table, go to Section 4.4, 'Create a Sequence'.

4.4 Create a Sequence

Create one sequence object, which will be used in INSERT statements to generate unique primary key values in the PATRONS table. (You do not need to create a sequence for the primary key in the TRANSACTIONS table, because you used the SQL Developer feature that enables automatic population of primary key values for that table.) You will use the Create Sequence dialog box to create the sequence declaratively; the sequence that you create will be essentially the same as if you had entered the following statements using the SQL Worksheet:

After creating the sequence, you can use it in INSERT statements to generate unique numeric values. The following example uses the patron_id_seq sequence in creating a row for a new patron (library user), assigning her a patron ID that is the next available value of the patron_id_seq sequence:

To create the sequence, if you are not already connected, connect to the database as the user for the schema you are using for this tutorial. Right-click the Sequences node in the schema hierarchy on the left side, select New Sequence, and enter information using the Create Sequence dialog box.

Schema: Specify your current schema as the schema in which to create the sequence.

Name: patron_id_seq

Increment: 1

Start with: 100

Min value: 100

Create Table Sql Primary Key

Click OK to finish creating the sequence.

To insert sample data into the tables, go to Section 4.5, 'Insert Data into the Tables'.

4.5 Insert Data into the Tables

For your convenience in using the view and the PL/SQL procedure that you will create, add some sample data to the BOOKS, PATRONS, and TRANSACTIONS tables. (If you do not add sample data, you can still create the remaining objects in this tutorial, but the view and the procedure will not return any results.)

Go to the SQL Worksheet window associated with the database connection you have been using. (For information about using the SQL Worksheet, see Section 1.7, 'Using the SQL Worksheet'.) Copy and paste the following INSERT statements into the Enter SQL Statement box:

Click the Run Script icon, or press the F5 key.

To create a view, go to Section 4.6, 'Create a View'.

4.6 Create a View

Create a view that returns information about patrons and their transactions. This view queries the PATRONS and TRANSACTIONS tables, and returns rows that contain a patron's ID, last name, and first name, along with a transaction and the transaction type. The rows are ordered by patron ID, and by transaction type within patron IDs.

To create the patrons_trans_view view, if you are not already connected, connect to the database as the user for the schema you are using for this tutorial. Right-click the Views node in the schema hierarchy on the left side, select New View, and enter the following information. (If a tab or field is not mentioned, do not enter anything for it.)

Schema: Specify your current schema as the schema in which to create the view.

Auto Increment Primary Key Oracle Sql

Name: patrons_trans_view

SQL Query tab

In the SQL Query box, enter (or copy and paste) the following statement:

Then click Test Syntax, and ensure that you have not made any syntax errors. If you made any errors, correct then and click Test Syntax again.

DDL

Review the SQL statement that SQL Developer will use to create the view. If you want to make any changes, go back to the SQL Query tab and make the changes there.

If you want to save the CREATE VIEW statement to a SQL script file, click Save and specify the location and file name.

Generate Primary Key Sql Developer Free

When you are finished, click OK.

You have finished creating the view. If you inserted data to the underlying tables, as described in Section 4.5, 'Insert Data into the Tables', you can see the data returned by this view as follows: in the Connections navigator, expand Views, and select PATRONS_TRANS_VIEW, then click the Data tab.

To create a procedure that lists all books with a specified rating, go to Section 4.7, 'Create a PL/SQL Procedure'.

4.7 Create a PL/SQL Procedure

Create a procedure that lists all books with a specified rating. You can then call this procedure with an input parameter (a number from 1 to 10), and the output will be all the titles of all books with that rating.

To create the procedure, if you are not already connected, connect to the database as the user for the schema you are using for this tutorial. Right-click the Procedures node in the schema hierarchy on the left side, select New Procedure, and enter the following information using the Create PL/SQL Procedure dialog box.

Object Name: list_a_rating

Insert Primary Key Sql Oracle

Click OK. A source window for the new procedure is opened. Enter (or copy and paste) the following procedure text, replacing any existing text:

This procedure uses a cursor (named the_cursor) to return only rows where the book has the specified rating (in_rating parameter), and uses a loop to output the title of each book with that rating.

Click the Save icon to save the procedure.

As a usage example, after creating the procedure named LIST_A_RATING, if you have inserted data into the BOOKS table (for example, using the INSERT statements in Section 4.5, 'Insert Data into the Tables'), you could use the following statement to return all books with a rating of 10:

To run this procedure within SQL Developer, right-click LIST_A_RATING in the Connections navigator hierarchy display and select Run. Under PL/SQL Block in the Run PL/SQL dialog box, change IN_RATING => IN_RATING to IN_RATING => 10, and click OK. The Log window display will now include the following output:

4.8 Debug a PL/SQL Procedure

If you want to practice debugging a PL/SQL procedure with SQL Developer, create a procedure that is like the list_a_rating procedure that you created in Section 4.7, 'Create a PL/SQL Procedure', but with a logic error. (The coding is also deliberately inefficient, to allow the display of the rating in a variable.)

Before you can debug the procedure, you must ensure that the user associated with the database connection has the DEBUG CONNECT SESSION and DEBUG ANY PROCEDURE privileges.

To create this procedure, if you are not already connected, connect to the database as the user for the schema you are using for this tutorial. Right-click the Procedures node in the schema hierarchy on the left side, select New Procedure, and enter the following information using the Create PL/SQL Procedure dialog box.

Object Name: list_a_rating2

Click OK. A source window for the new procedure is opened. Enter (or copy and paste) the following procedure text, replacing any existing text:

This procedure contains a logic error in the definition of the_cursor: it selects titles where the rating is less than or equal to a specified rating, whereas it should select titles only where the rating is equal to the specified rating.

Click the Save icon to save the procedure.

Assume that you wanted to run this procedure and list all books with a rating of 10. Right-click LIST_A_RATING2 in the Connections navigator hierarchy display and select Run. Under PL/SQL Block in the Run PL/SQL dialog box, change, change IN_RATING => IN_RATING to IN_RATING => 10, and click OK. In the Log window, however, you see unexpected output: many titles are listed, including some with ratings other than 10. So, you decide to debug the procedure.

To debug the procedure, follow these steps:

1. Click the Compile for Debug icon in the toolbar under the LIST_A_RATING2 tab.

2. Set two breakpoints by clicking in the left margin (left of the thin vertical line) beside each of these two lines:

   Clicking in the left margin toggles the setting and unsetting of breakpoints. Clicking beside these two lines will enable you to see the values of the matching_title and matching_rating variables as execution proceeds in debug mode.

3. Click the Debug icon, and in the Run PL/SQL dialog box change IN_RATING => IN_RATING to IN_RATING => 10; then click OK

4. Click View, then Debugger, then Data to display the Data pane. (Tip: Expand the Name column width so that you can see MATCHING_RATING.)

5. Press the F9 key (or click Debug, then Resume) to have execution proceed, stopping at the next breakpoint.

6. Repeatedly press the F9 key (or click Debug, then Resume), noticing especially the value of MATCHING_RATING as each row is processed. You will notice the first incorrect result when you see that the title Get Rich Really Fast is included, even though its rating is only 1 (obviously less than 10). (See the screen illustration with debugging information in Section 1.6, 'Running and Debugging Functions and Procedures'.)

7. When you have enough information to fix the problem, you can click the Terminate icon in the debugging toolbar.

From this debugging session, you know that to fix the logic error, you should change rating <= :in_rating to rating = :in_rating in the definition of the_cursor.

4.9 Use the SQL Worksheet for Queries

You can use the SQL Worksheet to test SQL statements using a database connection. To display the worksheet, from the Tools menu, select SQL Worksheet. In the Select Connection dialog box, select the database connection that you used to create the BOOKS, PATRONS, and TRANSACTIONS tables for the tutorial in Chapter 4, 'SQL Developer Tutorial: Creating Objects for a Small Database'.

The SQL Worksheet has the user interface shown in Section 1.7, 'Using the SQL Worksheet'.

In the Enter SQL Statement box, enter the following statement (the semicolon is optional for the SQL Worksheet):

Notice the automatic highlighting of SQL keywords (SELECT and FROM in this example).

Click the Execute SQL Statement icon in the SQL Worksheet toolbar. The results of the query are displayed on the Results tab under the area in which you entered the SQL statement.

In the Enter SQL Statement box, enter (or copy and paste) the following statement, which is the same as the SELECT statement in the view you created in Create a View:

Click the Execute SQL Statement icon in the SQL Worksheet toolbar, and view the results of the query.

Click the Execute Explain Plan icon in the SQL Worksheet toolbar to see the execution plan (displayed on the Explain tab) that Oracle Database follows to execute the SQL statement. The information includes the optimizer strategy and the cost of executing the statement. (For information about how to generate and interpret execution plans, see Oracle Database Performance Tuning Guide.)

4.10 Script for Creating and Using the Library Tutorial Objects

The following statements create and use the database objects that you have created (or will create) for the tutorial in Chapter 4, 'SQL Developer Tutorial: Creating Objects for a Small Database'. You can view these commands to help you understand the library database objects that are covered in the tutorial.