What is a Primary Key or distinctive Constraint?
Being able to find one specific row in a very table may be a elementary feature of SQL. really it is a demand of first traditional type.Take the username for accounts in your application. to make sure somebody has entered the right countersign and pull up their details on login you would like to seek out the row for that username. And take care every username seems at the most once within the accounts table.
Declaring the username unique enforces this second property. And raises a slip if anyone tries to insert a reproduction name:
******************************************
create table accounts (
username varchar2(10)
not null
unique,
given_name varchar2(512)
not null
);
insert into accounts ( username, given_name )
values ( 'chrissaxon', 'Chris' );
insert into accounts ( username, given_name )
values ( 'chrissaxon', 'Christopher' );
ORA-00001: unique constraint (CHRIS.SYS_C0018134) violated
******************************************
✅This guarantees that every username seems at the most once.
✅A primary key's a special case of a singular constraint. it's these further properties:
✅All its columns square measure necessary (not null)
✅Each table will have at the most one primary key.
✅So however does one make a choice from employing a primary key or distinctive constraint?
Some tables will have several distinctive identifiers. a standard reason for this is often surrogate keys – sequence appointed or GUID values that don't have any which means outside of the appliance. for instance you will add an account_id column to the accounts table:
******************************************
insert into accounts ( username, given_name )
select username, given_name
from accounts_stage acst
where not exists (
select * from accounts acct
where acct.username = acst.username
);
******************************************
The table will solely have one primary key. therefore a minimum of one of account_id and username must be a singular constraint. however that ought to be the primary key?
The accounts table is probably going to possess several kid tables that check with a specific row in it. like orders, invoices, and payments. to make sure these kid rows purpose to a legitimate account, produce a far off key from the kid table to the parent.
The columns foreign keys purpose to ought to be immutable . Unchanging. This avoids issues with having to cascade updates of key values from parent to kids. for instance, if somebody needs to alter their username.
Both primary keys and distinctive constraints may be the target of foreign keys. however as a result of you'll solely have one primary key, by convention foreign keys purpose to the current constraint.
So by making account_id the primary key, you are telling different developers:
"This is that the immutable price. Use this because the target for foreign keys."
Creating distinctive constraints improves information quality. sometimes once somebody tries to insert associate degree existing price – like making a replacement account with associate degree existing username – you wish to prevent this happening.
But typically you will need to skip this error. Say once loading keys that exist already.
Skip Duplicate Rows with a Subquery
If the supply information square measure seemingly to contain key values that square measure already within the table, it is best to avoid loading these utterly.
Do this with a not exists subquery:
******************************************
insert /*+ ignore_row_on_dupkey_index ( acct ( username ) ) */
select username, given_name from accounts_stage;
******************************************
Ignore Duplicate Rows with the ignore_row_on_dupkey_index Hint
******************************************
insert /*+ ignore_row_on_dupkey_index ( acct ( username ) ) */
select username, given_name from accounts_stage;
******************************************
Store Duplicate Values with DML Error Logging
*****************************************
exec dbms_errlog.create_error_log ( 'accounts' );
insert into accounts ( username, given_name )
values ( 'chrissaxon', 'Chris' )
insert into accounts ( username, given_name )
values ( 'chrissaxon', 'Christopher' )
log errors into err$_accounts reject limit unlimited;
insert into accounts ( username, given_name )
select username, given_name from accounts_stage
-------------------------------------------------------------------
insert into accounts ( username, given_name )
values ( 'chrissaxon', 'Christopher' )
select * from err$_accounts
where ora_err_tag$ = 'load name';
*****************************************
[HOW] wow duplicate unique key error fixed permanently (Solved)?
What is wow duplicate unique key error?
If you get this error then first of all You can check the ErrorCategory of the error inside the MongoWriteException and you must be confirm it was due to a duplicate key using getCategory(): Given below
-------------------------------------------------------------------
catch(MongoWriteException ex) {
if(ex.getError().getCategory() == ErrorCategory.DUPLICATE_KEY) {
//handle duplicate key error
} else {
//do something else...
}
}
✅Now,you must be double check Your task is adding the column, tha causes duplication in the unique constraint.
EmoticonEmoticon