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);
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?
whats the best way to modify a table via a procedure for example if i have a table with records: id, fn, ln, address, tel, dob
how would i go about creating a procedure that modifies a record. The problem is what param needs to go in? i could certainly have different procedures that modify different things like one for modifying tel, one for modifying address, but is there a way to combine it all into ONE procedure so that if user wanted he could put in exec modifyTable(2, 099898787) to modify tel and then modifyTable(2, 'a new address') to modify address... ?
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)
primary key constraint on transaction_dtl_bk is affecting the insertion of next correct rows.
CREATE OR REPLACE PROCEDURE NP_DB.san_po_nt_wnpg_1 ( dt DATE ) IS v_sql_error VARCHAR2 (100); -- added by sanjiv v_sqlcode VARCHAR2 (100); ---- added by sanjiv added by sanjiv
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';
We have the following case: an application modifies a table in an Oracle db (10.2.0.3.0).
Unfortunately the update SQL statements from the application always use the condition "where Column1 = 'some given value'" which is wrong (never mind why).
It should be instead "where Column1 = 'some value' and Column2 = 'val for Column2'. The 'val for Column2 will be taken from the very SQL query being issued (we can make the application do an update for Column2 even if the value in it never changes).
So all the update queries from the application look at the moment like that:
"update my_table set Column2 = 'val for Column2', Column3 = 'some other values', Column4 = 'some other value' where Column1 = 'some given value'".
We would like to capture them and somehow on the fly modify them to look like that:
"update my_table set Column2 = 'val for Column2', Column3 = 'some other values', Column4 = 'some other value' where Column1 = 'some given value' and Column2 = 'val for Column2'".
Can a trigger "before update" do it? For some reason we cannot at the moment ask the vendor to change the hard code of the application so we are looking for a temporary workaround.
In chapter 17 of the 'Oracle Database 10g, The Complete Reference' it says "You can increase or decrease the number of decimal places in a NUMBER column at any time". When trying to modify a column from NUMBER(38) to NUMBER(38,3) I get the ORA-01440 error. Is there a way around this when you have the maximum number of digits (precision = 38).
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 .
There was one constraint define on the column of table that the value of column should be in range of 100 to 200. Some one has deleted the constraint. Is it possible to get the exact name of constraint from any system table.
I use Oracle 10.0.2.0.1.If I create a table with constraint key; after that I create an unique index key, I got an error. Does it mean when I create a table with constraint the unique index are automatically created and I could not create index key as I did as below?
create table test_const(ename varchar2(50) not null, key_num number not null, descr varchar2(100), constraint constraint_test_const unique (ename, key_num)); create unique index test_const_idx on test_const ( "ENAME","KEY_NUM" ) tablespace tmp_data;
Error report: SQL Error: ORA-01408: such column list already indexed 01408. 00000 - "such column list already indexed" added [pre] tags by Sriram
I have a table as below. This table is not partitioned.
create table t1 ( d1 date, n1 number not null );
[Code]....
I took an export dump of the above table and after that I renamed the table t1 to t1_old. Then I recreated the table as below with a default constraint on d1 field.
create table t1 ( d1 date default to_date('01/01/1100','DD/MM/YYYY','NLS_CALENDAR=GREGORIAN'), n1 number not null
[Code]....
But the problem here is the data import is taking too much time than what I expected. I can't afford a maxvalue partition here as of my DBA team mentioned if you add maxvalue partition adding partition later in a stage is difficult on this table
apply in this scenario and make the import faster. I am using oracle 10.2.0.1.0 version.
I am a junior DBA. I want to check how many columns are under PRIMARY KEY constraint.
I used dba_constraints view. Please find below details.
SQL> select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from dba_constraints 2 where TABLE_NAME='DSET_PRODUCT_S'; OWNER CONSTRAINT_NAME C TABLE_NAME ------------------------------ ------------------------------ - ---------------------------------------- SCOTT SYS_C10202456 C EMPLOYEE SCOTT SYS_C234576 C DEPT
I have 2 questions, because they can be inter-related I am posting it in a single post. These queries are related to Oracle(PL\SQL).
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.
I am creating a table from another existing table in another schema. The existing table contains data. When I am using the query- create table m _voucher as select * from ipm.m_voucher,I am getting the whole data of m_voucher but I want empty m_voucher table, so what will be the query to get the empty m_voucher table?
I have created a table below, my TL asked me to create a local unique constraint for the below table.
I went through all sites and could not find the correct solution, how to create LOCAL UNIQUE CONSTRAINT ON SUB PARTITION TABLE and LOCAL UNIQUE INDEX ON PARTITION TABLE. Creating Local Unique constraint should take care of creating local unique index creation.
Unique key columns are DET,GDS,ARRIVE_DT
CREATE TABLE SUB_PAR_TAB ( ID VARCHAR2(100) NOT NULL, REGION VARCHAR2(40) NOT NULL, SOURCE VARCHAR2(80) NOT NULL, DET VARCHAR2(80) NOT NULL, GDS VARCHAR2(40) NOT NULL, ARRIVE_DTDATE, SYS_SOURCE VARCHAR2(25) , [code]........
i'm new to oracle environment.how can i specify NONCLUSTERD INDEX on Primary cloumn during table creation.By default it will create clusterd index but i need non-clusterd index on it.
I'm using following stmt to create normal primary constarint during table creation,
CONSTRAINT PKFORM_PROPS PRIMARY KEY (FORM_PROPS_PK) USING INDEX TABLESPACE DB123_INDEX
how can i change the above query, so that it should create NONCLUSTERED INDEX on Primary key column.
We have a transaction table and has 30 million rows. The table is not partitioned till date. We need to create partition on this table. We had an idea of moving this data to a temporary table and create partition[range]on the original table and move the data back.
