Which dba table do I need to look at to see my column constraints. I thought it would be dba_col_constraints. I don't have that table in my db. The one I found is dba_cons_columns. That table doesn't have information I am looking for. I know there is a 'default' constraint applied to some of my columns. How do I find that info?
01-10-2001, 02:01 PM
cant you see anything in dba_constraints...?
01-10-2001, 02:10 PM
YES I see data there but not the one I need. I just altered table and assign a DEFAULT constraint to one of the columns. I don't see anything for the table I altered in dba_constraints.
01-10-2001, 02:15 PM
i dont really understand what do you mean default constraint, I normally look constraint_tyoe column in dba_constraints and decode them to proper meaning since they are abbreaviated
01-10-2001, 02:26 PM
I issued command
'alter table /table name/ modify column /column name/ default blah..blah'. I thought I would see an entry in dba_constraints or some other data dictionary table for the action I just performed. There is nothing in dba_constraints for the table I just altered. It has to be recorded somewhere. I am confused...
Not a big issue anyway. I am sure the answer will come in time.
01-10-2001, 02:55 PM
Look at the DATA_DEFAULT column of DBA_TAB_COLUMNS. I think this might be what you're looking for.
01-10-2001, 03:08 PM
SQL> select * from DBA_CONS_COLUMNS where owner='XXXX' ;
SQL> select * from DBA_CONSTRAINTS where owner='YYYY' ;
[Edited by sreddy on 01-10-2001 at 02:32 PM]
01-10-2001, 03:13 PM
that was it. Yes, it was there.
Just a thought: Oracle conciders DEFAULT one of the constraints. To me there should be an entry made in datadictionary dba_tab_constraints or some view/table specificly collecting all constraints. If someone knows that view/table please let me know.
01-10-2001, 05:18 PM
DEFAULT can hardly be considered as a constraint, it is simply an atribute of a column. Constraints are used to *enforce* some rules that can not be overridden by users. DEFAULT does not enforce anything, it simply provides a default value if it was not explicitelly set in the insert. So I think they've put it in the data dictionary exactly in the view it belongs to - DBA_TAB_COLUMNS.
If you've actually found DEFAULT to be considered as a constraint somewhere in Oracle documentation, I'd call this more like a documentation bug...
01-11-2001, 01:51 PM
Just for information:
Oracle is confusing new DBA's like myself. Don't take me wrong I like Oracle.
Actually I found list of constraints in Oracle8 DBA handbook on page 11. It states DEFAULT is concidered one of the constraints. Strangely when you assign DEFAULT to a column you cannot find anything in dba_constraints table.
Thank you to everyone who replied. No need to reply it is just a couriousity which doesn't stop me from doing important things.