Oracle удалить primary key
Summary: in this tutorial, you will learn how to use the Oracle foreign key to establish the relationship between tables.
Introduction to Oracle foreign key constraint
Suppose, we have two tables supplier_groups and suppliers :
The supplier_groups table stores supplier groups e.g., one-time supplier, third-party supplier, and inter-co supplier. Each supplier group may have zero, one, or many suppliers.
The suppliers table stores the supplier information. Each supplier must belong to a supplier group.
The relationship between supplier_groups and suppliers table is one-to-many. In other words, one supplier group has many suppliers while each supplier must belong to a supplier group.
The group_id in the suppliers table is used for establishing the relationship between rows in the suppliers and supplier_groups tables.
Before inserting a row into the suppliers table, you have to look up an existing group_id in the supplier_groups table and use this value for insertion.
Assuming that the supplier_groups table contains the following data:
To insert a new third-party supplier, you have to use the group_id 2 as follows:
It works perfectly fine. However, the following statement also works:
The supplier_groups table has no row with group id 4 but nothing prevents you from inserting it into the suppliers table, which is an issue.
For example, the following query fails to get all suppliers and their groups:
As you can see, the WD supplier is missing in the result set.
A solution to fix this problem is to use the Oracle foreign key constraint to enforce the relationship between rows from the supplier_groups and suppliers tables.
First, drop the suppliers table:
Second, recreate the suppliers table with a foreign key constraint:
In this statement, the following clause was newly added:
This clause defines the group_id column in the suppliers table as a foreign key that references to the group_id column of the supplier_groups table.
This way, the constraint is enforced by Oracle. In other words, attempting to insert a row into the suppliers table that does not correspond to any row in the supplier_groups table will fail, as attempting to delete a row from the supplier_groups table where there exist dependent rows in the suppliers table.
By the way, the concept of referential integrity is all about maintaining and enforcing this parent-child relationship.
Oracle foreign key constraint in actions
The following statement works because the supplier_groups table has a row with group_id 1:
However, the following statement will fail:
Because the supplier_groups has no row with id 4. The following is the error message:
Similarly, attempting to delete a row with group_id 1 in the supplier_groups table will fail:
Oracle issued the following error message:
Because the suppliers table (child table) has a row that references to the row which is being deleted.
Oracle foreign key constraint syntax
Oracle allows you to create, add, drop, disable and enable a foreign key constraint.
Create a foreign key constraint
The following statement illustrates the syntax of creating a foreign key constraint when you create a table:
First, to explicitly assign the foreign key constraint a name, you use the CONSTRAINT clause followed by the name. The CONSTRAINT clause is optional. If you omit it, Oracle will assign a system-generated name to the foreign key constraint.
Second, specify the FOREIGN KEY clause to defines one or more column as a foreign key and parent table with columns to which the foreign key columns reference.
Third, use the ON DELETE clause to specify consequence when the rows in the parent table are deleted.
- ON DELETE CASCADE : if a row in the parent is deleted, then all the rows in the child table that reference the removed row will be deleted.
- ON DELETE SET NULL : if a row in the parent is deleted, then all the rows in the child table reference the removed row will be set to NULL for the foreign key columns.
Unlike the primary key constraint, a table may have more than one foreign key constraint.
Add a foreign key constraint to a table
If you want to add a foreign key constraint to an existing table, you use the ALTER TABLE statement as follows:
Drop a foreign key constraint
To drop a foreign key constraint, you use the ALTER TABLE statement below:
Disable a foreign key constraint
To temporarily disable a foreign constraint, you use the following ALTER TABLE statement:
Enable a foreign constraint
Similarly, you use also use the ALTER TABLE statement to enable a disabled foreign key constraint:
In this tutorial, you have learned how to use the Oracle foreign key constraint to enforce the relationship between tables.
Теперь, когда я пытался вставить записи, я получил ORA-00001: unique constraint pk_composit_key violated .
- Почему я получаю эту ошибку?
- Когда ключ был удален, почему уникальный индекс не был удален автоматически?
2 ответа
Вы упомянули об экспорте и импорте схемы, и если это произойдет в среде, которая демонстрирует такое поведение, это объяснит, что вы видите; по крайней мере, если вы использовали устаревшую версию imp , а не насос данных impdp .
В исходной документации по импорту указано, что объекты заказа импортировано:
- Определения типов
- Определения таблиц
- Табличные данные
- Индексы таблиц
- Ограничения целостности, представления, процедуры и триггеры
- Растровые, функциональные и доменные индексы
Таким образом, уникальный индекс был бы импортирован, а затем было бы создано ограничение.
- Если первичный ключ был создан с использованием существующего индекса, то индекс не удаляется.
- Если первичный ключ был создан с использованием индекса, сгенерированного системой, индекс удаляется.
Из-за порядка импорта ограничение использует существующий индекс, поэтому применяется первый маркер; и индекс сохраняется, когда ограничение сбрасывается.
См. Также My Oracle Support note 370633.1; и 1455492.1 предполагает, что аналогичное поведение будет происходить и при импорте перекачки данных. Я не знаю, как проверить, связан ли индекс с ограничением на этом уровне; нет никакой разницы в представлениях dba_constraints или dba_indexes при создании индекса вручную или автоматически. Однако включение drop index сделает его последовательным.
Это зависит от того, как был создан уникальный индекс . ниже приведены различные способы и поведение
1) сначала создайте уникальный индекс (в столбце, для которого должен быть определен первичный ключ), а затем добавьте ограничение первичного ключа. В этой ситуации ваш DDL для добавления первичного ключа будет использовать существующий уникальный индекс. Поэтому, когда вы отбрасываете первичный ключ, он удаляет не индекс, а только первичный ключ. ==> Думаю, это ваша ситуация .
2) При создании таблицы вы определяете первичный ключ ИЛИ при добавлении первичного ключа, когда не существовало уникального индекса для столбца (столбцов), для которого должен быть определен первичный ключ, поэтому система создаст уникальный индекс и будет использовать его для основной ключ. Таким образом, в этом случае, когда вы отбрасываете первичный ключ, уникальный индекс также будет удален.
This Oracle tutorial explains how to create, drop, disable, and enable a primary key in Oracle with syntax and examples.
What is a primary key in Oracle?
In Oracle, a primary key is a single field or combination of fields that uniquely defines a record. None of the fields that are part of the primary key can contain a null value. A table can have only one primary key.
- In Oracle, a primary key can not contain more than 32 columns.
- A primary key can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.
Create Primary Key - Using CREATE TABLE statement
You can create a primary key in Oracle with the CREATE TABLE statement.
Syntax
The syntax to create a primary key using the CREATE TABLE statement in Oracle/PLSQL is:
Example
Let's look at an example of how to create a primary key using the CREATE TABLE statement in Oracle:
In this example, we've created a primary key on the supplier table called supplier_pk. It consists of only one field - the supplier_id field.
We could also create a primary key with more than one field as in the example below:
Create Primary Key - Using ALTER TABLE statement
You can create a primary key in Oracle with the ALTER TABLE statement.
Syntax
The syntax to create a primary key using the ALTER TABLE statement in Oracle/PLSQL is:
Example
Let's look at an example of how to create a primary key using the ALTER TABLE statement in Oracle.
In this example, we've created a primary key on the existing supplier table called supplier_pk. It consists of the field called supplier_id.
We could also create a primary key with more than one field as in the example below:
Drop Primary Key
You can drop a primary key in Oracle using the ALTER TABLE statement.
Syntax
The syntax to drop a primary key using the ALTER TABLE statement in Oracle/PLSQL is:
Example
Let's look at an example of how to drop a primary key using the ALTER TABLE statement in Oracle.
In this example, we're dropping a primary key on the supplier table called supplier_pk.
Disable Primary Key
You can disable a primary key in Oracle using the ALTER TABLE statement.
Syntax
The syntax to disable a primary key using the ALTER TABLE statement in Oracle/PLSQL is:
Example
Let's look at an example of how to disable a primary using the ALTER TABLE statement in Oracle.
In this example, we're disabling a primary key on the supplier table called supplier_pk.
Enable Primary Key
You can enable a primary key in Oracle using the ALTER TABLE statement.
Syntax
The syntax to enable a primary key using the ALTER TABLE statement in Oracle/PLSQL is:
Example
Let's look at an example of how to enable a primary key using the ALTER TABLE statement in Oracle.
In this example, we're enabling a primary key on the supplier table called supplier_pk.
Summary: in this tutorial, you will learn how to use Oracle PRIMARY KEY constraint to manage the primary key of a table.
Introduction to the primary key
A primary key is a column of a combination of columns in a table that uniquely identifies a row in the table.
The following are rules that make a column a primary key:
- A primary key column cannot contain a NULL value or an empty string.
- A primary key value must be unique within the entire table.
- A primary key value should not be changed over time.
According to these rules, the following are the recommendations for the primary keys:
It is considered a best practice have a primary key in every table though it is not mandatory in Oracle.To create a primary key in a table, you use the PRIMARY KEY constraint.
Oracle PRIMARY KEY constraint examples
Typically, you create a primary key for a table when you create that table. In addition, you can add a primary key to a table after the fact by using the ALTER TABLE statement.
Creating a primary key that consists of one column
The following CREATE TABLE statement creates the purchase_orders table:
The purchase_orders table has four columns purchase order number ( po_nr ), vendor id ( vendor_id ), purchase order status ( po_status ), and the timestamp ( created_at ) of which the purchase order is created.
In this table, defined the po_nr column as the primary key by using the PRIMARY KEY clause.
The PRIMARY KEY constraint in this example is an inline constraint because it is on the same line as the po_nr column.
Consider the following statement.
This example used the PRIMARY KEY constraint as the table constraint. Notice the following clause:
In addition, we explicitly assigned the PRIMARY KEY constraint a name pk_purchase_orders .
Creating a primary key that consists of multiple columns
The following statement creates the purchase order line items table:
In this example, the primary key of the purchase_order_items table consists of two columns: po_nr and item_nr . It means that the combination of values of these columns uniquely identifies a purchase order line item.
This example did not use the CONSTRAINT clause to explicitly assign the PRIMARY KEY constraint a name. Therefore, Oracle implicitly assigned the primary key constraint a system-generated name such as SYS_C0010617 .
Adding a primary key to a table
Sometimes, you may want to add a primary key constraint to an existing table. To do it, you use the ALTER TABLE statement as follows:
The following example creates the vendors table first and then adds a primary key constraint to it:
Dropping an Oracle PRIMARY KEY constraint
You will rarely drop a PRIMARY KEY constraint from a table. If you have to do so, you use the following ALTER TABLE statement.
For example, you can drop the primary key constraint of the vendors table as follows:
It is possible to use the following statement to drop the primary key of a table:
Enable / Disable an Oracle PRIMARY KEY constraint
To improve the performance when loading a large amount of data into a table or updating mass data, you can temporarily disable the PRIMARY KEY constraint.
To disable a PRIMARY KEY constraint of a table, you use the ALTER TABLE statement:
For example, to disable the primary key constraint of the purchase_orders table, you use the following statement:
To enable a primary key constraint, you use the following ALTER TABLE statement:
The following example enables the PRIMARY KEY constraint of the purchase_orders table:
In this tutorial, you have learned how to use Oracle PRIMARY KEY constraint to create, add, disable, enable, and drop a primary key of a table.
Читайте также: