COLUMN CONSTRAINTS

[mary]

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?
Thank you.

[pando]

cant you see anything in dba_constraints...?

[mary]

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.

[pando]

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

[mary]

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.

[ckolik]

Look at the DATA_DEFAULT column of DBA_TAB_COLUMNS. I think this might be what you're looking for.

[sreddy]

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]

[mary]

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.

[jmodic]

Mary,

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...

[mary]

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.