PL/SQL :: ORA-02293- / Check Constraint Violated
Jul 5, 2012
i have a table for example :
CREATE TABLE RMD_2
(
"RMD_ID" NUMBER(10,0) NOT NULL ENABLE,
"ABB" VARCHAR2(16 BYTE),
"ACT_IND" VARCHAR2(1 BYTE) NOT NULL DISABLE,
CONSTRAINT "RMD_2_C1" CHECK (act_ind IN ('Y', 'N')) ENABLE
)
for column ACT_IND their is a check constraint RMD_2_C1 it should only accept either Y or N.
but, when ever i am inserting other values other than Y or N it is accepting it should not accept and it should have to give error message ORA-02293- : check constraint violated.
but it is not happening,it is accepting all other values even NULL value also.my requirement is this column should only accept either Y or N and it should not accept other values.
View 4 Replies
ADVERTISEMENT
Mar 26, 2007
From a step by step instructions I'm asked to put the following into sql*plus:
CREATE TABLE Lab2Lecturer
(staffNO VarCHAR2(10) NOT NULL,
title VARCHAR2(3),
fName VARCHAR2(30),
[code]...
Then the following:
INSERT INTO Lab2Lecturer
(staffNO, title, fName, lName, streetAddress, suburb, city, postCode, country, lecturerLevel, bankNO, bankName, salary, workLoad, researchArea)
VALUES
('1000', 'Dr', 'Johanna','Santoso',
'3 Robinson Av', 'Kew', 'Melbourne', '3080', 'Australia', 'C', '1000567237', 'CommBank', 65000.00,1.0, 'O-R DB');
and finally,
INSERT INTO Lab2Lecturer
(staffNO, title, fName, lName, streetAddress, suburb, city, postCode,country, lecturerLevel, BankNO,bankName, salary, workLoad, researchArea)
VALUES
('1000', 'Dr', 'Justine', 'Martin', '6 Algorithm AV', 'Montmorency', 'Melbourne', '3089', 'Australia', 'D', '1000123456', 'CommBank', 89000.00, 1.0, 'CBR');
when I try entering in the second one I get an error 'unique constraint violated'.So whats wrong exactly?
View 1 Replies
View Related
Oct 4, 2013
My DB version is
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
These error codes I'm getting in production.First of all, I've no duplicates present in that table for which this error has been raised.I've checked the index and related columns as well. NO DATA is there.So NO CHANCE for unique constraint violation.
SELECT * FROM ORDER_OCC_REQUISITION_X_REF WHERE LAB_ORDER_OCC_TEST_ID IN(SELECT LAB_ORDER_OCC_TEST_ID FROM LAB_ORDER_OCC_TEST WHERE LAB_ORDER_OCC_ID = 7944858);
no rows selected
Now when I'm trying to insert one row inside this table I'm getting this error, as you are seeing no records for this occurrence_id.
SELECT * FROM USER_INDEXES WHERE INDEX_NAME = 'ORD_OCC_REQ_UQ_TEST_IX_04';
--ORDER_OCC_REQUISITION_X_REF (Table name)
--MERGE_DT, LAB_ORDER_OCC_TEST_ID, TEST_ID, ACTIVE_YN (columns for the index 'ORD_OCC_REQ_UQ_TEST_IX_04')
As you can see there is no data then this error should not be raised. Update procedure.
/*******************************************************************************************************************
* Name : UPDATE_REQUISITION_X_REF
* Description : This Procedure update ORDER_OCC_REQUISITION_X_REF table with requisition_id
* that was generated due to merge process.
* In parameters : IN_merge_id NUMBER The order_ref_no of the orders to be merged (comma seperated)
***********************************************************************************************************************/
PROCEDURE UPDATE_REQUISITION_X_REF ( IN_merge_id IN TT_ORD_REQUISITION_WORK_AREA.merge_id%TYPE)
IS
[Code]....
View 8 Replies
View Related
May 14, 2010
I have faced an error as below:-
1 : 23000 : java.sql.BatchUpdateException: ORA-00001: unique constraint (SDS1.PK_EXP_TXT) violated
1 : 23000 : java.sql.SQLException: ORA-00001: unique constraint (SDS1.PK_EXP_TXT) violated
java.sql.BatchUpdateException: ORA-00001: unique constraint (SDS1.PK_EXP_TXT) violated
at oracle.jdbc.driver.DatabaseError.throwBatchUpdateException(DatabaseError.java:367)
at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:9119)
[Code] ......
After i check data in all_indexes table, i have found some old data which are belongs to 2008 and 2009. The sample as below:-
OWNER INDEX_NAME LAST_ANALYZED
SDS1PK_ACTION 31-OCT-09 07:04:49
SDS1AK_ACTION 31-OCT-09 07:04:49
SDS1PK_COND 31-OCT-09 07:04:50
SDS1AK_COND 31-OCT-09 07:04:50
SDS1COND_FK1 31-OCT-09 07:04:50
SDS1COND_FK2 31-OCT-09 07:04:50
is it the problem due to the old data not remove from the all_indexes table ?.. if YES is it I have to delete the old data manually from the all_indexes table ?
View 7 Replies
View Related
Nov 4, 2012
I also put in the relevant code in case it's needed.
SQL> @lab_05_01.sql
SQL> -- Oracle Database 10g: Administration Workshop II
SQL> -- Oracle Server Technologies - Curriculum Development
SQL> --
SQL> -- ***Training purposes only***
SQL> -- ***Not appropriate for production use***
SQL> --
SQL> -- This script performs a batch promotion update.
SQL> -- The logic of the updates is not important -
[code]....
View 2 Replies
View Related
Aug 3, 2008
I got this error message in my replication environment.
ORA-12012: error on auto execute of job 2182370
ORA-12008: error in materialized view refresh path
ORA-00001: unique constraint (TE.S_TE_MTH_DBASE_SALES_INFO_A_U1) violated
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 189
Materialized view and Base table having only one unique index. There are no referable constraints in my base table. Because source table not refer any other tables.
Even; materialized view log created using rowid because there are no primary key constraints in my base table.When I manually refresh materialized view I got the below error message
ORA-00001: unique constraint (TE.S_TE_MTH_DBASE_SALES_INFO_A_U1) violated
How it's possible? Because there is parent/child relationship in my source table and there is no way to enter duplicate records it's unique index.
View 8 Replies
View Related
May 31, 2013
i am running the query from which i am getting below mentioned error how can i find the record which is not there in parent table ora02291 integrety constrain violated and parent key not found.
View 11 Replies
View Related
Oct 13, 2010
I have Oracle 9i and yesterday I wanted to create new DB(datebase) but I couldn't did it.
In Database Configuration Assistant chose Create database then New database. Used configuration by default. On 92% I got this error
Quote:ORA-02291: integrity constraint (SH. COSTS_PRODUCT_FK) violated - parent key not found
View 2 Replies
View Related
Oct 17, 2011
I am trying to import the dump using the impdp utility and followed the below steps I have disabled all the constraints and executed the below command
impdp DIRECTORY=EXPDP_DAILY_BACKUP_DIR DUMPFILE=expdp_QA_full_11102011_214501.dmp
logfile=imp_EXPDP_QA_full_12102011_203254.log remap_schema=prod:prod remap_tablespace=prod:prod
schemas=prod TABLE_EXISTS_ACTION=truncate content=data_only.
but I am getting the below error like this for 1 or 2 tables . and If I import those tables seperately its getting imported successfully. i am not getting the below error always .
ORA-31693: Table data object "PROD"."DAS_ID_GENERATOR" failed to load/unload and is being skipped due to error:
ORA-00001: unique constraint (PROD.DAS_ID_GENERATOR_P) violated
ORA-31693: Table data object "PROD"."TKT_DIST_SRV_STAT" failed to load/unload and is being skipped due to error:
ORA-00001: unique constraint (PROD.SERVER_STATS_P) violated
View 8 Replies
View Related
Nov 4, 2013
Each time when I import an application into my workspace in local 10g XE using the APEX 4.2 Application Builder, I get the "ORA-02291, constraint wwv_flows_fk violated..". The application I whant to import was originally exported from [URL]. I found out, that it has to to with wwv_flow_application_install.get_workspace_id in the installation script, which obviously returns NULL and not my current workspace id as I suppose it should. I replaced wwv_flow_application_install.get_workspace_id by '<my worskspace id>' and then the import does work.
View 0 Replies
View Related
Feb 8, 2013
what is the difference between assertions and check constraint?
View 9 Replies
View Related
May 9, 2012
How can I modify the check constraint without dropping the already existing check constraint.
Example: alter table table_1 add constraint const_chk check (ATTRIBUTE7 in
('READ','UNREAD');
Want to add some TENTATIVE value for the already existing one. I could do that by dropping the constraint
ALTER TABLE table_1 DROP CONSTRAINT const_chk and modifying it later as
alter table table_1 add constraint const_chk check (ATTRIBUTE7 in
('READ','UNREAD','TENTATIVE');
Is it possible to do that with out dropping the constraint?
View 2 Replies
View Related
Jul 10, 2012
I have table EMP & DEPT.
Can you give me sample "check" constraint so that when I enter an EMP (with status='active') without matching DEPT entry, the process will fail?
It will allow to enter EMP with status='inactive' only even if no matching DEPT entry. So foreign_key will not apply.
View 21 Replies
View Related
Jan 27, 2011
I need to alter a table to check that the data in a column is contained in a similar column in another table.
I have a STORES table and a STORE_REP table. Both tables have a column REP_ID.
I need to add a CHECK constraint into the STORES table to make sure that the info entered into its REP_ID column matches an entry in the STORE_REP table.
Both have a NUMBER(5) data type.
Will it make any difference if the REP_ID column in the STORE_REP table was originally created with a VARCHAR2(5) data type and was later converted to NUMBER(5), while the REP_ID column in the STORES table was created as NUMBER(5) when that table was created?
View 2 Replies
View Related
Dec 13, 2010
In the below code, do I need the 'NOT NULL' after the 'state char(2)'? I am guessing that I do not need it since I have the CHECK constraint on the column.
CREATE TABLE employee(
id PRIMARY KEY,
first varchar(20) NOT NULL,
middle varchar(20),
[code]....
View 10 Replies
View Related
Apr 21, 2010
I have table customer which contains a column CUSTOMER_FIRST_NAME
CUSTOMER_FIRST_NAME VARCHAR2(50)
What will be sql statement to add a constraint on the CUSTOMER_FIRST_NAME column of the CUSTOMERS table so that the value inserted in the column does not have numbers ?
View 34 Replies
View Related
Dec 25, 2007
i want to get table name, constraint name, constraint type with join processes in string type. this is what i want: alter table tablename add constraint constraintname constrainttype(columnname)
View 1 Replies
View Related
Jul 30, 2011
I want to put one check box to check all the check boxes.how can i do this?
View 5 Replies
View Related
Apr 26, 2012
i have a problem that i have created a table named PROJECT and structure is like-
CREATE TABLE PROJECT
(
id VARCHAR2(250 BYTE) NOT NULL,
NAME VARCHAR2(250 BYTE) NOT NULL,
COMPID NUMBER NOT NULL,
[code]...
and this table is created in some number of different database then i want to delete the not null constraint of "id" column and there is 3 not null constraint in my table and i didnot gave name at the time of creation, so we cant delete it by its name in the script because there will be different name in another database for the same constraint. i want to make a script to delete the not null constraint because it is the part of my script.
how should i get proper information for deleting the constraint. and i used "user_constraints" but it shows same information for the all three "not null" constraint.
View 2 Replies
View Related
Aug 14, 2013
Create table students
(
Sid number(3),
Sname varchar(20) not null,
Email varchar(20) not null,
Doj Timestamp default sysdate not null,
Dob date not null,
[code]....
when i run the above code i m getting error like this...I dont understand wat error it is...Because i didnt reference any column in the check constraint...Thn why it says that i m referencing some column..
Error:
SQL>
SQL> Create table students
2 (
3 Sid number(3),
4 Sname varchar(20) not null,
[code]...
View 2 Replies
View Related
Feb 3, 2012
I have a parent table called dept and one child table emp which is referenced on deptno and now both have data . Now i want to truncate the dept table and i have truncated emp table after it when i am trying to truncate dept table then oracle throws a error that is-
"ORA-02266: unique/primary keys in table referenced by enabled foreign keys" ,
but when i tried to delete some records 1 by 1 then these have delete but truncate is not still working. why? if i have 10 millon records.
View 4 Replies
View Related
Oct 16, 2006
how to i insert a constraint of words into the table example below.I am new to this stuff man.
Create table Orders
(
orderID Number(8) Primary Key,
orderDate Date Not Null,
methPmt Varchar2(10),
custID Number(5),
orderSource Number(2),
Foreign Key(custID) Reference Customer(custID),
Foreign Key(ordersource) Reference OrderSource(ordersource)
);
The catch is I am required to enter a constraint of the methPmt will only take values of "CASH", "CREDIT" or "CHEQUE" only.
How am I suppose to enter this constraint value into the creation of this table?
View 3 Replies
View Related
Mar 23, 2011
Can I apply Referential Integrity to only selected rows of a particular column? This is the reference key to the primary key to another table. But, the issue is, this reference column is not having mandatory data for all the rows. So, whenever this is null, I dont want it to be referred by parent table.
View 4 Replies
View Related
Aug 4, 2011
SQL> create table emp_column_level_cons(empno number(4) constraint empcol_empno_pk primary key
2 ename varchar2(15) unique,
3 job varchar2(20) not null,
4 sal number(7,2) check (sal between 1500 and 25000),
5 deptno number(4) constraint empcol_deptno_fk foreign key re
ferences dept(deptno) on delete cascade);
deptno number(4) constraint empcol_deptno_fk foreign key references dept(deptno) on delete c
*
ERROR at line 5:
ORA-02253: constraint specification not allowed here
================================================
View 1 Replies
View Related
Dec 24, 2010
I want to know constraint name of a particular table if it exists.Here are the queries that i tried to find the constraint name of a particular table but the results were 'no rows selected'.
Queries:
select constraint_name from user_cons_columns where table_name='x';
select constraint_name from user_constraints where table_name='x';
View 5 Replies
View Related
Jan 4, 2013
I have three tables:
A {op_id, op_name, .}
B {b_id, op_id, supplier_id, .}
C {c_id, op_id, op_id2, supplier_id, relation, .}
Table B and C have op_id which is foreign key from table A. In table A op_id is primary key, in table B b_id is primary key and in table C c_id is primary key. In table B supplier_id and op_id may have duplicate records. Now I want to add constraint so that if I delete records from Table B for op_id and if a relationship record exists for op_id in table C then it should not allow me to delete. Is it possible through referential integrity constraint ?
View 5 Replies
View Related
Mar 4, 2011
NOVALIDATE CONSTRAINT METHOD.
Below is the step i gone through:
1) CREATE TABLE mutu (id NUMBER, text VARCHAR2(20));
2) INSERT INTO mutu SELECT rownum , 'mutu' FROM dual CONNECT BY LEVEL <= 10000;
3) Insert Duplicate row.
INSERT INTO mutu VALUES (42, 'DUPLICATE');
4) COMMIT;
5)Create a NON-UNIQUE INDEX's CONSTRAINT as below.
ALTER TABLE mutu ADD CONSTRAINT mutu_pk PRIMARY KEY(id)
USING INDEX(CREATE INDEX mutu_pk ON mutu(id)) ENABLE NOVALIDATE;
6) Insert Duplicate row.
INSERT INTO mutu VALUES (42, 'DUPLICATE');
7) ERROR at line 1:
ORA-00001: unique constraint (SYS.ZIGGY_PK) violated
Why it shows the error. Even though I had created a NON-UNIQUE INDEX's Constraint only.
View 2 Replies
View Related
Jul 31, 2010
I have a table say EMP with 2 fields ID and Name, with ID being the primary key. The name of the primary key constraint is ID_PK.Now my scenario is :
1. Rename the table EMP to EMP_TEMP;
2. Create an empty table EMP
3. If (data successfully loaded into EMP)
{
Drop EMP_TEMP;
}
else
{
Drop EMP;
Rename EMP_TEMP to EMP;
}
The problem here for me is the primary key constraint. When I am in 2, I cannot create the primary key constraint again since the old one is still in tact. If I go ahead to drop the original pk constraint and re-create a new one in 2, then in case of else part , I need to get the constraint again while renaming the original table.
View 6 Replies
View Related
Sep 27, 2013
I have folliwng table
CREATE TABLE abcd
(
trans_type VARCHAR2(10) nn,
original_tran_no NUMBER,
original_line_item NUMBER(20),
original_trans_dt DATE);
I have to create a constraint when trans_type = 'R'then (original_tran_no and original_trans_dt ) should be not null else can be null able; is the follwing stmt right?
alter table abcd add constraint t1_chk1
check (
( case when trans_type = 'R' then (original_tran_no is not null
and original_trans_dt is not null))
View 10 Replies
View Related
Mar 25, 2009
I'm trying to create a new table which has a date field (date of birth) and I want to include a constraint which will not allow me to add values if they are over 21 years old. I'm using SQLPlus .
View 3 Replies
View Related
