Chracter set at database level

1.Character Set at Database Level:

SELECT value$ FROM sys.props$ WHERE name = ‘NLS_CHARACTERSET’ ;

this characterset is for Western European data.

WE8MSWIN1252

2.Constraints Validation:

I have a foreign constraint as below. This was created like three years ago. All the data that are created after this constraint are valid but the backlog data (data before three years) are not valid. Toad shows this constraint as ‘Not Validated’

ALTER TABLE ISSUES ADD (
CONSTRAINT FK_CLIENT
FOREIGN KEY (CLIENT)
REFERENCES REPORTERS(USERID)
DEFERRABLE INITIALLY DEFERRED);

Today i fixed all the backlog data and now this constraint will hold good both for existing as well as new data. But how will i make the constraint as ‘Validated’.

Is there any way to ‘re-validate’ a foreign constraint?

ALTER TABLE <table_name> ENABLE VALIDATE CONSTRAINT fk_client;

(OR)

By default constraints are created as NON DEFERRABLE but this can be overidden using the DEFERRABLE keyword. If a constraint is created with the DEFERRABLE keyword it can act in one of two ways (INITIALLY IMMEDIATE, INITIALLY DEFERRED). The default, INITIALLY IMMEDIATE, keyword causes constraint validation to happen immediate unless deferred is specifically requested. The INITIALLY DEFERRED keyword causes constraint validation to defer until commit, unless immediate is secifically requested. The following code creates two tables with a deferred constraint.

CREATE TABLE tab1 (id NUMBER(10), tab2_id NUMBER(10));
CREATE TABLE tab2 (id NUMBER(10));

ALTER TABLE tab2 ADD PRIMARY KEY (id);

ALTER TABLE tab1 ADD CONSTRAINT fk_tab1_tab2
FOREIGN KEY (tab2_id)
REFERENCES tab2 (id)
DEFERRABLE
INITIALLY IMMEDIATE;

ALTER SESSION SET CONSTRAINTS = DEFERRED;
ALTER SESSION SET CONSTRAINTS = IMMEDIATE;

(OR)

Constraint States
Table constraints can be enabled and disabled using the CREATE TABLE or ALTER TABLE statement. In addition the VALIDATE or NOVALIDATE keywords can be used to alter the action of the state.

ENABLE VALIDATE is the same as ENABLE. The constraint is checked and is guaranteed to hold for all rows.
ENABLE NOVALIDATE means the constraint is checked for new or modified rows, but existing data may violate the constraint.
DISABLE NOVALIDATE is the same as DISABLE. The constraint is not checked so data may violate the constraint.
DISABLE VALIDATE means the constraint is not checked but disallows any modification of the constrained columns.
ALTER TABLE tab1 ADD CONSTRAINT fk_tab1_tab2
FOREIGN KEY (tab2_id)
REFERENCES tab2 (id)
ENABLE NOVALIDATE;

ALTER TABLE tab1 MODIFY CONSTRAINTS fk_tab1_tab2 ENABLE VALIDATE;

3. Removing Duplicates data / De-duplication from Oracle Tables/Columns :

create tables with unique key constraints

Add a unique constraint to prevent new invalid rows

1. First identify the duplicates using select.
2. Double verify those are actual duplicates or not
3. Take backup if necessary
4. Apply commit only if you are sure.
   1. Using rowid
      

Share this post: on Twitter on Facebook on Google+