Do you prefer to give all constraints meaningful names, even not nulls or other checks?

Tom,

What is the most acceptable naming convention for constraints. For example if empno is the primary key for emp table, I generally name the constraint as pk_emp_empno. But I have found some books following conventions like emp_empno_pk etc. Which is the most accepted one and why?

Sri

I wrote this for Oracle 9i release 2..

begin
for l_rec in ( select constraint_name, table_name
from user_constraints
where constraint_type = 'P')
loop
execute immediate
'alter table l_rec.table_name rename constraint l_rec.constraint_name to ''pk_''||table_name';
end loop;
end;
/

I get this error
begin
*
ERROR at line 1:
ORA-14155: missing PARTITION or SUBPARTITION keyword
ORA-06512: at line 6

What am I doing wrong here.. I want to change the name of PK constraint for all the tables in the schema.
Thanks

why do you prefer not to name not null >|?

is it just your preference ? or there is some reason ?

doesn't seem meaningful?  primary keys, foreign keys, check constraints -- it seems to help but:

ops$tkyte@ORA9IR2> insert into t values ( null );
insert into t values ( null )
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("OPS$TKYTE"."T"."X")


it very meaningful by itself whereas:

ops$tkyte@ORA9IR2> ops$tkyte@ORA9IR2> insert into t values ( 1 );
  
1 row created.
  
ops$tkyte@ORA9IR2> /
insert into t values ( 1 )
*
ERROR at line 1:
ORA-00001: unique constraint (OPS$TKYTE.SYS_C005926) violated

seems to be more meaningful with:

ops$tkyte@ORA9IR2> insert into t values ( 1 );
insert into t values ( 1 )
*
ERROR at line 1:
ORA-00001: unique constraint (OPS$TKYTE.T_PK) violated



 

I see your point .. thanks.

cant I just update user_constraints.constraint_name ?

I relialize that. Don't fall off of your chair..

we need you advice..

but in my inherited db no pk,fk,uk named everything is sysc0000... and to debug the error it's a problem,

I am afraid to touch the pk-fk s
as there are atleast 100 of them. and I don't kown what might go wrong if I chage them ;(

hi tom,
I want to "re-create" all the fk constraint with proper names. I got the cons.sql but I am havin issue
how can I generate the fk name ? any ideas ?

I want to create fk_<parent_col_name>_<child_column>

This script renames all pks and fk's in the given schema.
(Thanks to Tom as I have used his idea and code
to write this script.)

To run this sciprt make sure you dont' have same column name
in two table.

I have tested this on the tables where the
column names are like,...

tbl_col_name varchar2(..)
tbl_col_name number...

type of conventions.


---------------------------------------------------------------

nydev168>SELECT nvl( uc.constraint_type,'Total') cons_type,
2 COUNT (*) cnt
3 FROM user_constraints uc
4 GROUP BY rollup(uc.constraint_type);

nydev168>

CREATE TABLE cons_def (ID NUMBER,table_name VARCHAR2(30),DDL VARCHAR2(4000));

truncate table cons_def;

CREATE SEQUENCE seq_cons START WITH 1 INCREMENT BY 1 NOCYCLE NOCACHE NOMAXVALUE;


SELECT nvl( uc.constraint_type,'Total') cons_type,
COUNT (*) cnt
FROM user_constraints uc
GROUP BY rollup(uc.constraint_type);




-- create a list of refrences.. fk- ddl s
DECLARE
my_stmt VARCHAR2 (4000);
BEGIN
FOR x IN (SELECT UPPER (table_name) NAME
FROM user_tables
ORDER BY 1)
LOOP
my_stmt := NULL;
for y in( select
'alter table "' || child_tname || '"' || chr(10) ||
'add constraint "' || child_cons_name || '"' || chr(10) ||
'foreign key ( ' || child_columns || ' ) ' || chr(10) ||
'references "' || parent_tname || '" ( ' || parent_columns ||
') '|| decode(DEFERRED ,'DEFERRED','DEFERRABLE INITIALLY DEFERRED',' ') fkey , x.name
from
( select a.table_name child_tname, a.constraint_name child_cons_name,
b.r_constraint_name parent_cons_name, b.deferred,
max(decode(position, 1, '"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 2,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 3,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 4,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 5,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 6,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 7,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 8,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 9,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,10,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,11,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,12,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,13,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,14,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,15,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,16,', '||'"'||
substr(column_name,1,30)||'"',NULL)) child_columns
from user_cons_columns a, user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = 'R'
group by a.table_name, a.constraint_name, b.r_constraint_name,b.deferred ) child,
( select a.constraint_name parent_cons_name, a.table_name parent_tname,
max(decode(position, 1, '"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 2,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 3,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 4,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 5,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 6,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 7,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 8,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 9,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,10,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,11,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,12,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,13,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,14,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,15,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,16,', '||'"'||
substr(column_name,1,30)||'"',NULL))
parent_columns
from user_cons_columns a, user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type in ( 'P', 'U' )
group by a.table_name, a.constraint_name ) parent
where child.parent_cons_name = parent.parent_cons_name
and parent.parent_tname = x.name)
loop

INSERT INTO cons_def
(ID,
table_name,
DDL
)
VALUES (seq_cons.NEXTVAL,
y.NAME,
y.fkey
);
end loop;
END LOOP;

commit;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line ('Error in creating DDL: ' || SQLERRM);
RAISE;
END;
/



select count(*) from cons_def;




----------------------------------------------------------------------------------------------------

-- disable all the fk constraints. for pk

BEGIN
FOR x IN (SELECT uc.constraint_name,
uc.constraint_type,
uc.table_name
FROM user_constraints uc
WHERE uc.constraint_type = 'R'
ORDER BY 2)
LOOP
EXECUTE IMMEDIATE ' alter table '
|| x.table_name
|| ' disable constraint '
|| x.constraint_name;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line ('Error in Disabling the FK: ' || SQLERRM);
RAISE;
END;
/

SELECT nvl( uc.constraint_type,'Total') cons_type,
COUNT (*) cnt
FROM user_constraints uc
GROUP BY rollup(uc.constraint_type);




--- recreate the pks and unique constriants...
BEGIN
FOR x IN ( select uc.constraint_name,decode(uc.DEFERRED ,'DEFERRED','DEFERRABLE INITIALLY DEFERRED',' ') dfrd,
uc.table_name,uc.constraint_type,
max(decode(position, 1, '"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 2,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 3,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 4,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 5,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 6,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 7,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 8,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 9,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,10,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,11,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,12,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,13,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,14,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,15,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,16,', '||'"'||
substr(column_name,1,30)||'"',NULL)) col_list
FROM user_constraints uc,
user_cons_columns ucc
WHERE uc.constraint_type IN ('P', 'U')
AND uc.constraint_name = ucc.constraint_name
GROUP BY uc.constraint_name,decode(uc.DEFERRED ,'DEFERRED','DEFERRABLE INITIALLY DEFERRED',' '),
uc.table_name,uc.constraint_type
ORDER BY 2)
LOOP
IF (x.constraint_type = 'P') THEN
EXECUTE IMMEDIATE ' Alter table '
|| x.table_name
|| ' Drop primary key cascade';


EXECUTE IMMEDIATE ' Alter table '
|| x.table_name
|| ' Add Constraint PK_'
|| TRIM (SUBSTR (x.table_name, 1, 27))
|| ' PRIMARY KEY ('
|| x.col_list
|| ') '|| x.dfrd ;

ELSE
EXECUTE IMMEDIATE ' Alter table '
|| x.table_name
|| ' Drop constraint '
|| x.constraint_name;


EXECUTE IMMEDIATE ' Alter table '
|| x.table_name
|| ' Add CONSTRAINT UNQ_'
|| trim(substr(x.table_name,1,26)) || ' UNIQUE( '
|| x.col_list
||' ) '|| x.dfrd ;
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line ('Error in creating the PK/unq: ' || SQLERRM);
RAISE;
END;
/


-----------------------------------------------------------------------------------------------------------

-- recreate the fks

BEGIN
FOR x IN (SELECT *
FROM cons_def
ORDER BY 1)
LOOP
BEGIN
EXECUTE IMMEDIATE x.DDL;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line ('recreating ****'|| sqlerrm ||' ----:-----'|| x.DDL);
END;
END LOOP;
END;
/

SELECT nvl( uc.constraint_type,'Total') cons_type,
COUNT (*) cnt
FROM user_constraints uc
GROUP BY rollup(uc.constraint_type);



---------------------------------------------------------------------------------------------------------
-- change the name of fks

DECLARE
my_stmt VARCHAR2 (4000);
BEGIN
FOR x IN (SELECT UPPER (table_name) NAME
FROM user_tables
where table_name not like ('%_STAGE')
ORDER BY 1)
LOOP
my_stmt := NULL;
for y in( select
'alter table "' || child_tname || '"' || chr(10) ||
'add constraint "' || 'FK_' || substr(trim('"' from child_columns),1,16)||'_'|| substr(trim('"' from parent_columns),1,7 + 14 -length(substr(trim('"' from child_columns),1,14)))|| '"' || chr(10) ||
'foreign key ( ' || child_columns || ' ) ' || chr(10) ||
'references "' || parent_tname || '" ( ' || parent_columns ||
') '|| decode(DEFERRED ,'DEFERRED','DEFERRABLE INITIALLY DEFERRED',' ') fkey , x.name,child.child_cons_name ,child_tname
from
( select a.table_name child_tname, a.constraint_name child_cons_name,
b.r_constraint_name parent_cons_name,b.deferred,
max(decode(position, 1, '"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 2,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 3,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 4,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 5,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 6,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 7,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 8,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 9,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,10,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,11,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,12,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,13,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,14,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,15,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,16,', '||'"'||
substr(column_name,1,30)||'"',NULL)) child_columns
from user_cons_columns a, user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = 'R'
group by a.table_name, a.constraint_name, b.r_constraint_name,b.deferred ) child,
( select a.constraint_name parent_cons_name, a.table_name parent_tname,
max(decode(position, 1, '"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 2,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 3,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 4,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 5,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 6,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 7,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 8,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 9,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,10,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,11,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,12,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,13,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,14,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,15,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,16,', '||'"'||
substr(column_name,1,30)||'"',NULL))
parent_columns
from user_cons_columns a, user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type in ( 'P','U' )
group by a.table_name, a.constraint_name ) parent
where child.parent_cons_name = parent.parent_cons_name
and parent.parent_tname = x.name)
loop

begin
EXECUTE IMMEDIATE ' Alter table '
|| y.child_tname
|| ' Drop constraint '
|| y.child_cons_name;
exception
when others then
dbms_output.put_line(
'Error with stmt : '|| sqlerrm || ' :'
||' Alter table '
||y.child_tname
|| ' Drop constraint '
|| y.child_cons_name
);
end;


begin
EXECUTE IMMEDIATE y.fkey;
exception
when others then
dbms_output.put_line('**Error : '|| sqlerrm || ' :'||y.fkey);
end;


end loop;
end loop;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line ('Error in creating DDL: ' || SQLERRM);
RAISE;
END;
/


SELECT nvl( uc.constraint_type,'Total') cons_type,
COUNT (*) cnt
FROM user_constraints uc
GROUP BY rollup(uc.constraint_type) ;

Can you alter a table/index to add another column to the primary key? Or the only way to do it is to drop the existing primary key and create a new one?

Hi Tom,

I want a report like Table Name, Column Name,
constraint_type, constraint type

here i want to decode constraint type to description like if it is P - Primary Key, R - Foreign key but i could not differentiate Check and Not Null Constraints.. I have a written a query but it is giving error ...

Select decode(constraint_type,'P','Primary Key','R','Foregin Key','C','Not Null','') Con_Type,
search_condition from user_constraints
where constraint_type in ('C','P','R') and search_condition like '%NOT NULL%'
and table_name='TEST'
Union all
Select decode(constraint_type,'C','Check Cons','') con_type,search_condition from user_constraints
where constraint_type in ('C') and search_condition not like '%NOT NULL%'
and table_name='TEST'
/
ERROR at line 3:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG

Is there any way to get this...

Thanx in adv.

Sanjeev Vibuthi

Hi Tom:

Thanx tom.... I got the result

SCOTT@ testdb 03-FEB-06>create or replace function get_search_condition(
2 p_cons_name in varchar2 ) return varchar2
3 authid current_user
4 is
5 l_search_condition user_constraints.search_condition%type;
6 begin
7 select search_condition into l_search_condition
8 from user_constraints
9 where constraint_name = p_cons_name;
10 return l_search_condition;
11 end;
12 /

Function created.

SCOTT@ testdb 03-FEB-06>l
SELECT b.column_name, a.constraint_name,
DECODE(a.constraint_type,'P','Primary Key','R','Foregin Key','C','Not Null','') Con_Type,
' ' Search_Condition, r.table_name Reference_Table
FROM
user_constraints a,
user_cons_columns b,
user_constraints r
WHERE a.constraint_name=b.constraint_name AND
a.r_constraint_name=r.constraint_name AND
a.constraint_type in ('P','R') AND
a.table_name='TEST'
UNION ALL
SELECT b.column_name,a.constraint_name,
DECODE(a.constraint_type,'P','Primary Key','R','Foregin Key','C','Not Null','') Con_Type,
' ' Search_Condition,' '
FROM
user_constraints a,
user_cons_columns b
WHERE a.constraint_name=b.constraint_name AND
a.constraint_type in ('C') AND
get_search_condition(a.constraint_name) LIKE '%NOT NULL%' AND
a.table_name='TEST'
UNION ALL
SELECT b.column_name,a.constraint_name,
DECODE(a.constraint_type,'C','Check Cons','') con_type,
get_search_condition(a.constraint_name) Search_Condition,' '
FROM
user_constraints a,
user_cons_columns b
WHERE a.constraint_name=b.constraint_name AND
a.constraint_type in ('C') AND
get_search_condition(a.constraint_name) not like '%NOT NULL%' AND
a.table_name='TEST'
/

SCOTT@ testdb 03-FEB-06>

COLUMN_NAME
------------------------------------------------------------------------------------------------------------------------
cons_name CON_TYPE Condition REFERENCE_TABLE
------------------------------ ----------- ------------------------------ ------------------------------
D
SYS_C007909 Foregin Key EMP

C
SYS_C007906 Not Null

B
SYS_C007907 Check Cons b in ('A','B')
Sanjeev Vibuthi

In First select stmt I will get the result of only Primary and Foreign Keys. In Second select I will get the result of only Not Null and Third select I will get the result of Check Constraints finally i clubbing the result by using Union All....
In a single select stmt...? I tried it but i am not geeting any idea... is there any other method to do it...?

thanks in Adv.
Sanjeev Vibuthi

SCOTT@ testdb 07-FEB-06>l
1 SELECT b.column_name, a.constraint_name,
2 DECODE(a.constraint_type,'P','Primary Key','R','Foregin Key','C','Not Null','') Con_Type,
3 a.Search_Condition, r.table_name Reference_Table
4 FROM
5 user_constraints a,
6 user_cons_columns b,
7 user_constraints r
8 WHERE a.constraint_name=b.constraint_name AND
9 a.r_constraint_name=r.constraint_name(+) AND
10 a.constraint_type in ('P','R','C') AND
11* a.table_name='TEST'

SCOTT@ testdb 07-FEB-06>/

COLUMN_NAME
------------------------------------------------------------------------------------------------------------------------
CONSTRAINT_NAME CON_TYPE
------------------------------ -----------
SEARCH_CONDITION REFERENCE_TABLE
-------------------------------------------------------------------------------- ------------------------------
D
SYS_C007909 Foregin Key
EMP

A
SYS_C007908 Primary Key


B
SYS_C007907 Not Null
b in ('A','B')

C
SYS_C007906 Not Null
"C" IS NOT NULL


Here I am getting Constraint Type as "Not Null" for both NOT NULL and CHECK, i will know the actual constraint type
based on search condition only. But what i wanted is Con Type should be Not Null for NOT NULL and
Check for CHECK constraint.

Sanjeev Vibuthi

ops$tkyte@ORA9IR2> create table t
  2  ( x int primary key,
  3    y references t,
  4    a int check (a>5),
  5    b int not null,
  6    constraint table_check check (b>a)
  7  );

Table created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> SELECT constraint_name,
  2         search_condition,
  3         case when constraint_type = 'P'
  4              then 'Primary Key'
  5              when constraint_type = 'R'
  6              then 'Foreign Key'
  7              when constraint_type = 'C'
  8              then case when get_search_condition(constraint_name) like '"%" IS NOT NULL'
  9                        then 'Not Null'
 10                        else 'Check'
 11                    end
 12          end what,
 13          case when constraint_type = 'R'
 14               then (select table_name
 15                       from user_constraints
 16                      where constraint_name = UC.r_constraint_name)
 17           end referenced_table,
 18          (select rtrim(
 19                  max( decode(rownum,1,column_name) ) || ',' ||
 20                  max( decode(rownum,2,column_name) ) || ',' ||
 21                  max( decode(rownum,3,column_name) ) || ',' ||
 22                  max( decode(rownum,4,column_name) ), ',' )
 23             from user_cons_columns
 24            where table_name = UC.table_name
 25              and constraint_name = UC.constraint_name) cols
 26    from user_constraints uc
 27   where table_name = 'T'
 28  /

CONSTRAINT_ SEARCH_CONDITIO WHAT            RE COLS
----------- --------------- --------------- -- -----
SYS_C003244 "B" IS NOT NULL Not Null           B
SYS_C003245 a>5             Check              A
TABLE_CHECK b>a             Check              A,B
SYS_C003247                 Primary Key        X
SYS_C003248                 Foreign Key     T  Y

 

Thanks Tom,

With this query I understood how effectively we can write SQL Statements. Till now I have written simple (little complex) SQL Statements and used Union, but with this query I understood that without using Unions also we can get same result…

Yesterday I purchased two of your books (Expert One-on-Oracle and Effective Oracle by Design) …. But not yet started reading… Expert … is Fifth India Print Dec-2004 and Effective… is 2005 print … My doubt is whether 10g features or topics covered in these books or not… I think Expert… is covered only 8i …

Thanx in Adv.

Sanjeev Vibuthi

<
Expert Oracle: Database Architecture was written in 2005 and covers oracle up to
10.1

Expert Oracle: Database Development is being written in 2006 and will cover
oracle up to 10.2
>

Is this a separate book or edition of Expert one on Oracle?
If it is not covered in the book which i have purchased, whether i missed a major topics... or it will ok to understand Architecture....
one more thing is i didnt find any code in www.wrox.com site, in the book (expert) it is specified that you can download code from this site... even i didnt find book name .....
In our place only these edition are available ....

Sanjeev Vibuthi

I have found a PK index is corrupt (ora-1578). What is the procedure to drop and recreate PK index. If I try to drop PK index, I get ora error -- used for enforcement of unique/primary key.
Thanks

Tom,
 
   While you do rename of a table, doesn't the underlying index gets renamed too?

SQL>  select constraint_name, index_name from user_constraints where constraint_name = 'PK_TEMP1';

CONSTRAINT_NAME                INDEX_NAME
------------------------------ ------------------------------
PK_TEMP1                       PK_TEMP1

SQL> alter table temp1 rename constraint temp1_pk to pk_temp1;

Table altered.

SQL>  select constraint_name, index_name from user_constraints where constraint_name = 'PK_TEMP1';

CONSTRAINT_NAME                INDEX_NAME
------------------------------ ------------------------------
PK_TEMP1                       TEMP1_PK

SQL>

    I can drop the constraint and recreate it (with 'using index' construct) if that is the ONLY way. Also I was doing this drop and recreate, but, I noticed one thing. 

   When I dropped the constraint and recreated the constraint with new name with 'using index' construct, I think it still kept the old index name. Is this possible?

    That means when I dropped and recreated the index I got this:

SQL> alter table some_other_table drop constraint pk_some_other_table;

Table altered.

SQL> alter table some_other_table add constraint some_other_table_pk primary key (x) using index tablespace users;

Table altered.

SQL>  select constraint_name, index_name from user_constraints where constraint_name = 'PK_TEMP1';

CONSTRAINT_NAME                INDEX_NAME
------------------------------ ------------------------------
PK_some_other_table                       TEMP1_some_other_table

    Not for this particular example, but, another set of tables.

Rahul. 

Tom,

>> While you do rename of a table, doesn't the underlying index gets renamed
too?
That, as you guessed was a typo.

c/table/constraint


>> I would not expect a constraint to cause an index to change it's name.

Ok, i got your point, but, doesn't dropping the constraint drop the underlying index too? My example showed so too, but, I had a case where dropping the constraint (primary key constraint) didn't drop the underlying index.

Hi Tom,

We have emp table with empno, name, dob etc... what is the requirement is to get next birth date and in many days he will reach his birthday from today .......

Is it possible in a query? or we have to write a proc or function....
I tried with the following query but it is useful when the birthday is not over in the current month

select (Sysdate - to_date((to_char(emp.birth_date,'mm/dd')||to_char(sysdate,'/yyyy')),'mm/dd/yyyy'))
from emp

Please let me know the correct query ...

Thanks

Sanejev Vibuthi

ops$tkyte%ORA10GR2> select dob,
  2         add_months( to_date(to_char(dob,'dd-mon'),'dd-mon'),
  3                     case when trunc(sysdate) > to_date(to_char(dob,'dd-mon'),'dd-mon')
  4                                          then 12
  5                                                  else 0
  6                                          end ) next_bday,
  7         add_months( to_date(to_char(dob,'dd-mon'),'dd-mon'),
  8                     case when trunc(sysdate) > to_date(to_char(dob,'dd-mon'),'dd-mon')
  9                                          then 12
 10                                                  else 0
 11                                          end )
 12         -trunc(sysdate) days_till
 13    from t;

DOB       NEXT_BDAY  DAYS_TILL
--------- --------- ----------
01-JAN-65 01-JAN-08        292
15-MAR-65 15-MAR-07          0
15-DEC-65 15-DEC-07        275

3 rows selected.

Great, Thanks Tom

One new thing (to me) I learn from this query is if we dont pass Year or Month to to_date it will pickup corrent year or month.....

This site always helps me a lot....

Sanjeev Vibuthi

Why after renaming constraints, the original name in user_indexes didn't go away? Thanks!

scott@spring> create table test
  2  (id number(10), text varchar2(20), constraint pk_test primary key (id));

Table created.

scott@spring> select index_name, index_type
  2  from user_indexes
  3  where table_name='TEST';

PK_TEST                        NORMAL

scott@spring> select constraint_name
  2  from user_constraints where table_name='TEST';

PK_TEST

scott@spring> alter table test rename constraint pk_test to pk_test_1;

Table altered.

scott@spring> select index_name, index_type
  2  from user_indexes
  3  where table_name='TEST';

PK_TEST                        NORMAL

scott@spring> select constraint_name
  2   from user_constraints where table_name='TEST';

PK_TEST_1