You create a CUSTOMERS table in which CUSTOMERID is designated as a primary key. You want the values that are entered into the CUSTOMERID column to be generated automatically. Which of the following actions should you perform? Mark for Review. Should primary key be autoincrement? Just a reminder that these are all randomly generated values.) Although this is often a popular 'unqiue' value in personnel.

Primary Key Generation Using Oracle's Sequence

Oracle provides the sequence utility to automatically generate unique primary keys. To use this utility to auto-generate primary keys for a CMP entity bean, you must create a sequence table and use the @AutomaticKeyGeneration annotation to point to this table.

In your Oracle database, you must create a sequence table that will create the primary keys, as shown in the following example:

• By convention, non-composite primary keys of type short, int, long, or Guid are set up to have values generated for inserted entities, if a value isn't provided by the application. Your database provider typically takes care of the necessary configuration; for example, a numeric primary key in SQL Server is automatically set up to be an.
• Aug 23, 2013  The above code should give us auto generated primary key value. The one thing to note here is method prepareStatement. We passed two arguments first the insert query string and second an array of column name. The column name should be the primary key column name of table where you inserting the record.
• The PlantID attribute is the primary key for the relation below. The values for the PlantID attribute are generated automatically by the database. What is the term for this? A relation contains three columns that individually could be the primary key. One column is selected as the primary key.

This creates a sequences of primary key values, starting with 1, followed by 2, 3, and so forth. The sequence table in the example uses the default increment 1, but you can change this by specifying the increment keyword, such as increment by 3. When you do the latter, you must specify the exact same value in the cacheSize attribute of the @AutomaticKeyGeneration annotation:

If you have specified automatic table creation in the CMP bean's project settings, the sequence table will be created automatically when the entity bean is deployed. For more information, see @JarSettings Annotation. For more information on the definition of a CMP entity bean, see below.

Primary Key Generation Using SQL Server's IDENTITY

In SQL Server you can use the IDENTITY keyword to indicate that a primary-key needs to be auto-generated. The following example shows a common scenario where the first primary key value is 1, and the increment is 1:

In the CMP entity bean definition you need to specify SQLServer(2000) as the type of automatic key generator you are using. You can also provide a cache size:

If you have specified automatic table creation in the CMP bean's project settings, the sequence table will be created automatically when the entity bean is deployed. For more information, see @JarSettings Annotation. For more information on the definition of a CMP entity bean, see below.

Primary Key Generation Using a Named Sequence Table

A named sequence table is similar to the Oracle sequence functionality in that a dedicated table is used to generate primary keys. However, the named sequence table approach is vendor-neutral. To auto-generate primary keys this way, create a named sequence table using the two SQL statements shown in the example:

In the CMP entity bean definition you need to specify the named sequence table as the type of automatic key generator you are using. You can also provide a cache size:

If you have specified automatic table creation in the CMP bean's project settings, the sequence table will be created automatically when the entity bean is deployed. For more information, see @JarSettings Annotation. For more information on the definition of a CMP entity bean, see the next section.

Note. When you specify a cacheSize value for a named sequence table, a series of unique values are reserved for entity bean creation. When a new cache is necessary, a second series of unique values is reserved, under the assumption that the first series of unique values was entirely used. This guarantees that primary key values are always unique, although it leaves open the possibility that primary key values are not necessarily sequential. For instance, when the first series of values is 10...20, the second series of values is 21-30, even if not all values in the first series were actually used to create entity beans.

Defining the CMP Entity Bean

When defining a CMP entity bean that uses one of the primary key generators, you use the @AutomaticKeyGeneration annotation to point to the name of the primary key generator table to obtain primary keys. Also, you must define a primary key field of type Integer or Long to set and get the auto-generated primary key. However, the ejbCreate method does not take a primary key value as an argument. Instead the EJB container adds the correct primary key to the entity bean record.

The following example shows what the entity bean might look like. Notice that the bean uses the named sequence option described above, and that ejbCreate method does not take a primary key:

Related Topics

Primary Key Constraint is defined by creating a primary key on a table. The value in primary key mustuniquely identify each row in the table. Primary Key Constraint enforces row integrity.

Consider the following facts when defining a primary key:

• When you create a primary key on a table, a unique index (and hence unique constraint) is automatically and implicitly created which enforces uniqueness of data in this column.

• Primary key column cannot contain NULL values. Database engine implicitly creates a NOT NULL constraint on the primary key column.

• A table can only have one primary key defined on it.

• A primary key can be formed by a single column or a composition of multiple columns (i.e. composite primary key).

• If you insert a duplicated record in primary key, you'll get an error.

What is artificial primary key?

There are two types of primary key: artificial primary key and natural primary key.

Artificial primary key is an integer number that is system auto-generated, auto-incremented, and maintained by MySQL database engine.It is the preferred way of defining and creating primary key. Artificial primary key is also known as surrogate primary key.

In MySQL, when we define a primary key as auto_increment, it will automatically increment by 1 every time when we adda new record into the table. This number is just an integer number and have no meanings whatsoever - you can call it stupid key.

Defining primary key:

Data in primary key column CategoryID:

What is natural primary key?

As its name suggests, natural primary key takes the natural format of the data and often has business meanings associated with it.Natural primary key is also known as intelligent primary key.

For example, primary key CustomerID in customers table is a natural primary key. It uses a string of 5 characters which is abbreviated from the customer's company name.

Natural primary key:

Why we should use an artificial primary key

From the example showing above for CustomerID, the drawback of using natural primary key is obvious. First, the primary key is created by shortening the company name. It's rather cumbersome to come up with a unique short name for a customer. Second, it's not flexible and is subject to business requirement changes.

• Integer values are independent of the business requirements which often change from time to time.

  This is actually the disadvantage of using a natural primary key. When an organization goes thru functional changes, so do the primary key values. Artificial primary key does not suffer from this problem because integer values don't have business meanings and are not associated business changes.

• Using integer values as primary key can often improve query performance.

To optimize the Customers table, we can create a new integer type CustomerID column in customers table as artificial primary key and remove the previous varchar type CustomerID. We then renamed the old varchar type CustomerID column to CustomerAbbr (if we still want to keep this column) which is short for customer abbreviation. We also added unique index to CustomerAbbr column.

Artificial primary key:

Dis-advantages of using artificial primary key:

The only disadvantage I can think of is when we retrieve data from tables by using joins, we'll have tojoin more tables than we need. The extra joins come from the lack of natural key in the foreign key tables.

The following two sets of screenshots illustrate how extra joins can occur.

Table 1: Use extra join to get CustomerID

Values For The Primary Key Should Be Generated Automatically Lyrics

CustomerID below is integer data type and so it's artificial primary key. To retrieve order information and CustomerAbbr data, we need to join the two tables together. select b.OrderID, a.CustomerAbbr, b.OrderDate, b.RequiredDate from customers as a inner join orders as b on a.CustomerID=b.CustomerID

Customers table:

Orders table:

Foreign Key

Table 2: No join is used to get CustomerAbbr

Here natural key Customer abbreviation is used as CustomerID and because it's used as foreign key in orders table, when we retrieve order information and Customer abbreviation data, we don't need to join the two tables together. select OrderID, CustomerID as CustomerAbbr, OrderDate, RequiredDate from orders

The queries in Table 1 and Table 2 return exactly the same result but Table 1 used JOIN but Table 2 didn't.

Unique Constraint

Unique Constraint defines that values in a column must be unique. No duplicate values are allowed in the column. Unique Constraint enforces row integrity.

Unique Constraint is created on a column when you want to guarantee that data in this column must be unique. For example, a unique constraint is created on CategoryName column in categories table. This is achieved by creating a unique index on CategoryName column.

Considerations when creating unique constraint:

• Unique constraint is automatically created when you define a column as primary key.

• Unique constraint can be created on one or more columns.

When on multiple columns, the combination of data on these columns must be unique. For example, we created a unique index on OrderID and ProductIDcolumn in order_details table.

• Unique constraint is created implicitly by defining a unique index on the column(s).

• NULL value is allowed by unique constraint.

• One table can have more than one unique constraint.

On next page, we will look into details of Creating foreign key relationships and considerations.

Other tutorials in this category

1. How to Design Relational Database
2. Enforce Data Integrity by Database Constraints
3. How to Enforce Data Type Constraint
4. How to Enforce Defualt Constraint and Nullability Constraint
5. Foreign Key Relationships and Considerations