SQL & PL/SQL :: Modification Of Existing Check Constraint

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


ADVERTISEMENT

Server Administration :: How To Check Last Modification Date Of Resource_name In Profile

Feb 9, 2010

we have a few profiles in dba_profile. How to check what is the last modification date of the the resources inside each profile.

View 6 Replies View Related

SQL & PL/SQL :: Modifying Existing Constraint On Table

Apr 1, 2013

Can we modifying the existing constraint on table?I have table level UNIQUE constraint on 3 columns of table.I need to modify the UNIQUE constraint to 2 columns? Instead of dropping/recreating the constraint, is there any option to modify the existing constraint

Ex

CREATE TABLE TEST_CONST(NUM1 NUMBER , NUM2 NUMBER , NUM3 NUMBER , UNIQUE (NUM1 ,NUM2,NUM3)); ;

SELECT * FROM USER_CONS_COLUMNS UCC WHERE UCC.TABLE_NAME LIKE 'TEST_CONST';

ALTER TABLE TEST_CONST MODIFY CONSTRAINT SYS_C0025132 UNIQUE(NUM1,NUM2);

ORA-00933: SQL command not properly ended

Third statement is not executing fine .

View 1 Replies View Related

SQL & PL/SQL :: Difference Between Assertions And Check Constraint?

Feb 8, 2013

what is the difference between assertions and check constraint?

View 9 Replies View Related

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 View Related

How To Create Check Constraint That Act As Foreign Key

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

SQL & PL/SQL :: How To Add A CHECK CONSTRAINT To Compare Info In 2 Tables

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

SQL & PL/SQL :: Is NOT NULL Needed If A CHECK Constraint Is On Column

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

SQL & PL/SQL :: Check Constraint To Disallow Number In Varchar2 Column

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

SQL & PL/SQL :: Check Existing Entries In Table

Apr 3, 2012

I am abolute new in PL SQL and I am working on following topic:

I have to check if an entry exists in a table.

- if it exists ...... do something
- if not ...... simply go on

I tried the following:

select Count(*)from products p
where p.productNbr = temp_Nbr
Group By t.produkt;

I fetched the result into a variable entries

if entries > 0 .....

MY problem is the following:

If there are entries (entries>0) --> it works
If there are no entries, entries is not 0 but 'no data found' --> crash

I cannot work with Exceptions because this all happens in a Loop which must go on in both cases!

View 14 Replies View Related

Replication :: Check The Refresh Rate Of Existing MView?

Jan 28, 2010

hw can i check the refresh rate of existing Mview..

View 4 Replies View Related

SQL & PL/SQL :: Modification On Trigger?

Jul 4, 2012

I have an table where are three cols:

ID, DECRIPTION and HOX, are nullable columns.When inserted ID on table, description will automatically updated by the trigger as 'NOT VALID', now I need modification on the trigger that if the IDchanges from 1000, (the HOX-value stays at it was) to some other value like 1001 description willupdated to 'VALID'. But if the ID is not 1000, like 999,description will stay as NOT VALID.

View 1 Replies View Related

SQL & PL/SQL :: Modification Of Fields With Huge Data?

Aug 26, 2011

I have 2 questions, because they can be inter-related I am posting it in a single post. These queries are related to Oracle(PLSQL).

1. I am trying to increase the size of a field in a table which has almost 2 million records and the query for alteration runs for almost and hour and rollsback, wondering is there a better way of doing it.

2. I have modified the size of a field in a table from Varchar2(10) to Varchar2(20), now when I tried to rollback the modification it is not letting me to change the size from Varchar2(20) to Varchar2(10). No data has been inserted after the modification.

View 2 Replies View Related

Datatype Modification For Table With Huge Data

Aug 11, 2011

Below is my requirement.

Need to change the precision of a column in a existing table. Statistics about the table

* has over 130 columns
* More than 300 million records
* Column to modify is #121 which has data
* No primary key defined

Since the column has data, it is not possible to modify with a simple Alter.

Second option - create temp column in same table, update from original, put null in original, alter, update back from temp, drop the temp column. This approach is very expensive and time consuming.

Also the Column ID needs to preserved as #121.

View 3 Replies View Related

Forms :: How To Get Date And Time Of File Modification

Mar 1, 2010

I need get access a property (time of last modification) file winword.

View 3 Replies View Related

Replication :: Table Data Modification From Source To Destination Database

Jan 11, 2011

I want to replicate single table data/modification from source db to destination DB.procedure or steps how to create replication between source and dest db for single table.

View 18 Replies View Related

Get Table Name / Constraint Name / Constraint Type With Join Processes In String Type

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

Forms :: Put One Check Box To Check All The Check Boxes?

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

SQL & PL/SQL :: Existing Role Claimed To Be Not Existing?

Dec 19, 2011

I'm am getting an error that says that a role doesn't exist when I can cleary see it in DBA_ROLES. I can successfully grant other roles in this session. I am using Oracle 11.1 on RAC.

GRANT "CONNECT" TO BAKERD
Error at line 1
ORA-01919: role 'CONNECT' does not exist
select * from dba_roles where role = 'CONNECT'

[code]....

View 5 Replies View Related

SQL & PL/SQL :: How To Delete Constraint

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

SQL & PL/SQL :: Constraint - Getting Error?

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

SQL & PL/SQL :: Foreign Key Constraint

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

Creating Table With A Constraint?

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

Referential Integrity Constraint?

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

Unique Constraint Violated?

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

Constraint Specification Not Allowed Here

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

SQL & PL/SQL :: Find Constraint Name Of A Table

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

SQL & PL/SQL :: How To Add Oracle Constraint To Tables

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

SQL & PL/SQL :: Novalidate Constraint Method

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

PL/SQL :: Unique Constraint Violated

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







Copyrights 2005-15 www.BigResource.com, All rights reserved