Aug 18, 2019 You can create a composite primary key just as you would create a single primary key, except that instead of specifying just one column, you provide the name of two or more columns, separated by a comma. In this article, we will learn about composite primary keys and how to create them in SQL Server. Composite primary key. Primary keys are special types of constraint that uniquely identify all rows in a table. Usually, we choose a single column as the primary key in our table to maintain data integrity. Question: To Generate A Composite Key On A Table In MySQL You Would: Use The AUTO-INCREMENT COMPOSITE Constraint Use The AUTOGENCOMPOSITE Constraint Create A Surrogate Key Create A Primary Key On Multiple Columns.
When creating a composite partitioned table, you use the
PARTITION
and SUBPARTITION
clauses of the CREATE TABLE
SQL statement. To create a composite partitioned table, you start by using the
PARTITION
BY
{HASH
| RANGE
[INTERVAL
]| LIST
} clause of a CREATE TABLE
statement. Next, you specify a SUBPARTITION BY
clause that follows similar syntax and rules as the PARTITION BY
clause. The following topics are discussed:
4.2.1 Creating Composite Hash-* Partitioned Tables
Composite hash-* partitioning enables hash partitioning along two dimensions.
The composite hash-hash partitioning strategy has the most business value of the composite hash-* partitioned tables. This technique is beneficial to enable partition-wise joins along two dimensions.
In the following example, the number of subpartitions is specified when creating a composite hash-hash partitioned table; however, names are not specified. System generated names are assigned to partitions and subpartitions, which are stored in the default tablespace of the table.
Live SQL:
View and run a related example on Oracle Live SQL at Oracle Live SQL: Creating a Composite Hash-Hash Partition Table.
Example 4-17 Creating a composite hash-hash partitioned table
See Also:
Specifying Subpartition Templates to Describe Composite Partitioned Tables to learn how using a subpartition template can simplify the specification of a composite partitioned table
4.2.2 Creating Composite Interval-* Partitioned Tables
The concepts of interval-* composite partitioning are similar to the concepts for range-* partitioning.
However, you extend the
PARTITION BY RANGE
clause to include the INTERVAL
definition. You must specify at least one range partition using the PARTITION
clause. The range partitioning key value determines the high value of the range partitions, which is called the transition point, and the database automatically creates interval partitions for data beyond that transition point. Sql Composite Key Definition
The subpartitions for intervals in an interval-* partitioned table are created when the database creates the interval. You can specify the definition of future subpartitions only with a subpartition template.
The following topics show examples for the different interval-* composite partitioning methods.
See Also:
Specifying Subpartition Templates to Describe Composite Partitioned Tables to learn how using a subpartition template can simplify the specification of a composite partitioned table
4.2.2.1 Creating Composite Interval-Hash Partitioned Tables
You can create an interval-hash partitioned table with multiple hash partitions by specifying multiple hash partitions in the
PARTITION
clause or by using a subpartition template. If you do not use either of these methods, then future interval partitions get only a single hash subpartition.
The following example shows the
sales
table, interval partitioned using monthly intervals on time_id
, with hash subpartitions by cust_id
. This example specifies multiple hash partitions, without any specific tablespace assignment to the individual hash partitions. Live SQL:
View and run a related example on Oracle Live SQL at Oracle Live SQL: Creating a Composite Interval-Hash Partitioned Table.
This next example shows the same
sales
table, interval partitioned using monthly intervals on time_id
, again with hash subpartitions by cust_id
. This time, however, individual hash partitions are stored in separate tablespaces. The subpartition template is used to define the tablespace assignment for future hash subpartitions. 4.2.2.2 Creating Composite Interval-List Partitioned Tables
To define list subpartitions for future interval-list partitions, you must use the subpartition template.
If you do not use the subpartitioning template, then the only subpartition that are created for every interval partition is a
DEFAULT
subpartition. Example 4-18 shows the
sales
table, interval partitioned using daily intervals on time_id
, with list subpartitions by channel_id
. Example 4-18 Creating a composite interval-list partitioned table
4.2.2.3 Creating Composite Interval-Range Partitioned Tables
To define range subpartitions for future interval-range partitions, you must use the subpartition template.
If you do not use the subpartition template, then the only subpartition that is created for every interval partition is a range subpartition with the
MAXVALUE
upper boundary. Example 4-19 shows the
sales
table, interval partitioned using daily intervals on time_id
, with range subpartitions by amount_sold
. Example 4-19 Creating a composite interval-range partitioned table
4.2.3 Creating Composite List-* Partitioned Tables
The concepts of list-hash, list-list, and list-range composite partitioning are similar to the concepts for range-hash, range-list, and range-range partitioning.
However, for list-* composite partitioning you specify
PARTITION BY LIST
to define the partitioning strategy. The list partitions of a list-* composite partitioned table are similar to non-composite range partitioned tables. This organization enables optional subclauses of a
PARTITION
clause to specify physical and other attributes, including tablespace, specific to a partition segment. If not overridden at the partition level, then partitions inherit the attributes of their underlying table. The subpartition descriptions, in the
SUBPARTITION
or SUBPARTITIONS
clauses, are similar to range-* composite partitioning methods. The following topics show examples for the different list-* composite partitioning methods.
See Also:
- Specifying Subpartition Templates to Describe Composite Partitioned Tables to learn how using a subpartition template can simplify the specification of a composite partitioned table
- About Creating Composite Range-Hash Partitioned Tables for more information about the subpartition definition of a list-hash composite partitioning method
- About Creating Composite Range-List Partitioned Tables for more information about the subpartition definition of a list-list composite partitioning method
- Creating Composite Range-Range Partitioned Tables for more information about the subpartition definition of a list-range composite partitioning method
4.2.3.1 Creating Composite List-Hash Partitioned Tables
The example in this topic shows how to create a composite list-hash partitioned table.
Example 4-20 shows an
accounts
table that is list partitioned by region and subpartitioned using hash by customer identifier. Example 4-20 Creating a composite list-hash partitioned table
4.2.3.2 Creating Composite List-List Partitioned Tables
The example in this topic shows how to create a composite list-list partitioned table.
Example 4-21 shows an
accounts
table that is list partitioned by region and subpartitioned using list by account status. Live SQL:
View and run a related example on Oracle Live SQL at Oracle Live SQL: Creating a Composite List-List Partitioned Table.
Example 4-21 Creating a composite list-list partitioned table
4.2.3.3 Creating Composite List-Range Partitioned Tables
The example in this topic shows how to create a composite list-range partitioned table.
Example 4-22 shows an
accounts
table that is list partitioned by region and subpartitioned using range by account balance, and row movement is enabled. Subpartitions for different list partitions could have different ranges specified. Example 4-22 Creating a composite list-range partitioned table
4.2.4 Creating Composite Range-* Partitioned Tables
The methods for creating composite range-* partitioned tables are introduced in this topic.
The following topics show examples of the different range-* composite partitioning methods.
To Generate A Composite Key On A Table Lyrics
See Also:
Specifying Subpartition Templates to Describe Composite Partitioned Tables to learn how using a subpartition template can simplify the specification of a composite partitioned table
4.2.4.1 About Creating Composite Range-Hash Partitioned Tables
The partitions of a range-hash partitioned table are logical structures only, because their data is stored in the segments of their subpartitions.
As with partitions, these subpartitions share the same logical attributes. Unlike range partitions in a range-partitioned table, the subpartitions cannot have different physical attributes from the owning partition, although they are not required to reside in the same tablespace.
The following topics are discussed:
See Also:
Specifying Subpartition Templates to Describe Composite Partitioned Tables to learn how using a subpartition template can simplify the specification of a composite partitioned table
4.2.4.1.1 Creating a Composite Range-Hash Partitioned Table With the Same Tablespaces
The example in this topic shows how to create a composite range-hash partitioned table using the same tablespaces.
The statement in Example 4-23 creates a range-hash partitioned table. Four range partitions are created, each containing eight subpartitions. Because the subpartitions are not named, system generated names are assigned, but the
STORE IN
clause distributes them across the 4 specified tablespaces (ts1
, ts2
, ts3
,ts4
). Example 4-23 Creating a composite range-hash partitioned table using one STORE IN clause
4.2.4.1.2 Creating a Composite Range-Hash Partitioned Table With Varying Tablespaces
The example in this topic shows how to create a composite range-hash partitioned table using varying tablespaces.
Attributes specified for a range partition apply to all subpartitions of that partition. You can specify different attributes for each range partition, and you can specify a
STORE IN
clause at the partition level if the list of tablespaces across which the subpartitions of that partition should be spread is different from those of other partitions. This is illustrated in the following example. 4.2.4.1.3 Creating a Local Index Across Multiple Tablespaces
The example in this topic shows how to create a local index across multiple tablespaces.
The following statement is an example of creating a local index on a table where the index segments are spread across tablespaces
ts7
, ts8
, and ts9
. This local index is equipartitioned with the base table so that it consists of as many partitions as the base table. Each index partition consists of as many subpartitions as the corresponding base table partition. Index entries for rows in a given subpartition of the base table are stored in the corresponding subpartition of the index.
4.2.4.2 About Creating Composite Range-List Partitioned Tables
The range partitions of a range-list composite partitioned table are described as the same for non-composite range partitioned tables.
This organization enables optional subclauses of a
PARTITION
clause to specify physical and other attributes, including tablespace, specific to a partition segment. If not overridden at the partition level, partitions inherit the attributes of their underlying table. The list subpartition descriptions, in the
SUBPARTITION
clauses, are described as for non-composite list partitions, except the only physical attribute that can be specified is a tablespace (optional). Subpartitions inherit all other physical attributes from the partition description. The following topics are discussed:
See Also:
Specifying Subpartition Templates to Describe Composite Partitioned Tables to learn how using a subpartition template can simplify the specification of a composite partitioned table
4.2.4.2.1 Creating a Composite Range-List Partitioned Table
The example in this topic shows how to create a composite range-list partitioned table.
Example 4-24 illustrates how range-list partitioning might be used. The example tracks sales data of products by quarters and within each quarter, groups it by specified states.
A row is mapped to a partition by checking whether the value of the partitioning column for a row falls within a specific partition range. The row is then mapped to a subpartition within that partition by identifying the subpartition whose descriptor value list contains a value matching the subpartition column value. For example, the following list describes how some sample rows are inserted.
- (10, 4532130, '23-Jan-1999', 8934.10, 'WA') maps to subpartition
q1_1999_northwest
- (20, 5671621, '15-May-1999', 49021.21, 'OR') maps to subpartition
q2_1999_northwest
- (30, 9977612, '07-Sep-1999', 30987.90, 'FL') maps to subpartition
q3_1999_southeast
- (40, 9977612, '29-Nov-1999', 67891.45, 'TX') maps to subpartition
q4_1999_southcentral
- (40, 4532130, '5-Jan-2000', 897231.55, 'TX') does not map to any partition in the table and displays an error
- (50, 5671621, '17-Dec-1999', 76123.35, 'CA') does not map to any subpartition in the table and displays an error
Live SQL:
View and run a related example on Oracle Live SQL at Oracle Live SQL: Creating a Composite Range-List Partitioned Table.
Example 4-24 Creating a composite range-list partitioned table
4.2.4.2.2 Creating a Composite Range-List Partitioned Table Specifying Tablespaces
The example in this topic shows how to create a composite range-list partitioned table while specifying tablespaces.
The partitions of a range-list partitioned table are logical structures only, because their data is stored in the segments of their subpartitions. The list subpartitions have the same characteristics as list partitions. You can specify a default subpartition, just as you specify a default partition for list partitioning.
The following example creates a table that specifies a tablespace at the partition and subpartition levels. The number of subpartitions within each partition varies, and default subpartitions are specified. This example results in the following subpartition descriptions:
- All subpartitions inherit their physical attributes, other than tablespace, from tablespace level defaults. This is because the only physical attribute that has been specified for partitions or subpartitions is tablespace. There are no table level physical attributes specified, thus tablespace level defaults are inherited at all levels.
- The first 4 subpartitions of partition
q1_1999
are all contained intbs_1
, except for the subpartitionq1_others
, which is stored intbs_4
and contains all rows that do not map to any of the other partitions. - The 6 subpartitions of partition
q2_1999
are all stored intbs_2
. - The first 2 subpartitions of partition
q3_1999
are all contained intbs_3
, except for the subpartitionq3_others
, which is stored intbs_4
and contains all rows that do not map to any of the other partitions. - There is no subpartition description for partition
q4_1999
. This results in one default subpartition being created and stored intbs_4
. The subpartition name is system generated in the formSYS_SUBP
n
.
4.2.4.3 Creating Composite Range-Range Partitioned Tables
The range partitions of a range-range composite partitioned table are similar to non-composite range partitioned tables.
This organization enables optional subclauses of a
PARTITION
clause to specify physical and other attributes, including tablespace, specific to a partition segment. If not overridden at the partition level, then partitions inherit the attributes of their underlying table. The range subpartition descriptions, in the
SUBPARTITION
clauses, are similar to non-composite range partitions, except the only physical attribute that can be specified is an optional tablespace. Subpartitions inherit all other physical attributes from the partition description. The following example illustrates how range-range partitioning might be used. The example tracks shipments. The service level agreement with the customer states that every order is delivered in the calendar month after the order was placed. The following types of orders are identified:
A row is mapped to a partition by checking whether the value of the partitioning column for a row falls within a specific partition range. The row is then mapped to a subpartition within that partition by identifying whether the value of the subpartitioning column falls within a specific range. For example, a shipment with an order date in September 2006 and a delivery date of October 28, 2006 falls in partition
p06_oct_a
. - E (EARLY): orders that are delivered before the middle of the next month after the order was placed. These orders likely exceed customers' expectations.
- A (AGREED): orders that are delivered in the calendar month after the order was placed (but not early orders).
- L (LATE): orders that were only delivered starting the second calendar month after the order was placed.
See Also:
Specifying Subpartition Templates to Describe Composite Partitioned Tables to learn how using a subpartition template can simplify the specification of a composite partitioned table
4.2.5 Specifying Subpartition Templates to Describe Composite Partitioned Tables
You can create subpartitions in a composite partitioned table using a subpartition template.
A subpartition template simplifies the specification of subpartitions by not requiring that a subpartition descriptor be specified for every partition in the table. Instead, you describe subpartitions only one time in a template, then apply that subpartition template to every partition in the table. For interval-* composite partitioned tables, the subpartition template is the only way to define subpartitions for interval partitions.
The subpartition template is used whenever a subpartition descriptor is not specified for a partition. If a subpartition descriptor is specified, then it is used instead of the subpartition template for that partition. If no subpartition template is specified, and no subpartition descriptor is supplied for a partition, then a single default subpartition is created.
The following topics are discussed:
4.2.5.1 Specifying a Subpartition Template for a *-Hash Partitioned Table
For range-hash, interval-hash, and list-hash partitioned tables, the subpartition template can describe the subpartitions in detail, or it can specify just the number of hash subpartitions.
Example 4-25 creates a range-hash partitioned table using a subpartition template and displays the subpartition names and tablespaces.
The example produces a table with the following description.
- Every partition has four subpartitions as described in the subpartition template.
- Each subpartition has a tablespace specified. It is required that if a tablespace is specified for one subpartition in a subpartition template, then one must be specified for all.
- The names of the subpartitions, unless you use interval-* subpartitioning, are generated by concatenating the partition name with the subpartition name in the form:partition name_subpartition nameFor interval-* subpartitioning, the subpartition names are system-generated in the form:
SYS_SUBP
n
Example 4-25 Creating a range-hash partitioned table with a subpartition template
4.2.5.2 Specifying a Subpartition Template for a *-List Partitioned Table
For -list partitioned tables, the subpartition template can describe the subpartitions in detail.
Example 4-26, for a range-list partitioned table, illustrates how using a subpartition template can help you stripe data across tablespaces. In this example, a table is created where the table subpartitions are vertically striped, meaning that subpartition n from every partition is in the same tablespace.
If you specified the tablespaces at the partition level (for example,
tbs_1
for partition q1_1999
, tbs_2
for partition q2_1999
, tbs_3
for partition q3_1999
, and tbs_4
for partition q4_1999
) and not in the subpartition template, then the table would be horizontally striped. All subpartitions would be in the tablespace of the owning partition. Example 4-26 Creating a range-list partitioned table with a subpartition template
-->A key serves as a unique identifier for each entity instance. Most entities in EF have a single key, which maps to the concept of a primary key in relational databases (for entities without keys, see Keyless entities). Entities can have additional keys beyond the primary key (see Alternate Keys for more information).
By convention, a property named
Id
or <type name>Id
will be configured as the primary key of an entity.Note
Owned entity types use different rules to define keys.
You can configure a single property to be the primary key of an entity as follows:
You can also configure multiple properties to be the key of an entity - this is known as a composite key. Composite keys can only be configured using the Fluent API; conventions will never setup a composite key, and you can not use Data Annotations to configure one.
Primary key name
By convention, on relational databases primary keys are created with the name
PK_<type name>
. You can configure the name of the primary key constraint as follows:Key types and values
While EF Core supports using properties of any primitive type as the primary key, including
string
, Guid
, byte[]
and others, not all databases support all types as keys. In some cases the key values can be converted to a supported type automatically, otherwise the conversion should be specified manually.Key properties must always have a non-default value when adding a new entity to the context, but some types will be generated by the database. In that case EF will try to generate a temporary value when the entity is added for tracking purposes. After SaveChanges is called the temporary value will be replaced by the value generated by the database.
Important
If a key property has its value generated by the database and a non-default value is specified when an entity is added, then EF will assume that the entity already exists in the database and will try to update it instead of inserting a new one. To avoid this turn off value generation or see how to specify explicit values for generated properties.
Alternate Keys
An alternate key serves as an alternate unique identifier for each entity instance in addition to the primary key; it can be used as the target of a relationship. When using a relational database this maps to the concept of a unique index/constraint on the alternate key column(s) and one or more foreign key constraints that reference the column(s).
Tip
If you just want to enforce uniqueness on a column, define a unique index rather than an alternate key (see Indexes). In EF, alternate keys are read-only and provide additional semantics over unique indexes because they can be used as the target of a foreign key.
Alternate keys are typically introduced for you when needed and you do not need to manually configure them. By convention, an alternate key is introduced for you when you identify a property which isn't the primary key as the target of a relationship.
You can also configure a single property to be an alternate key:
You can also configure multiple properties to be an alternate key (known as a composite alternate key):
Finally, by convention, the index and constraint that are introduced for an alternate key will be named
AK_<type name>_<property name>
(for composite alternate keys <property name>
becomes an underscore separated list of property names). You can configure the name of the alternate key's index and unique constraint: