sql - Oracle find a constraint

ID : 20440

viewed : 16

Tags : sqloracleconstraintssql

Top 5 Answer for sql - Oracle find a constraint

vote vote

90

select * from all_constraints where owner = '<NAME>' and constraint_name = 'SYS_C00381400' / 

Like all data dictionary views, this a USER_CONSTRAINTS view if you just want to check your current schema and a DBA_CONSTRAINTS view for administration users.

The construction of the constraint name indicates a system generated constraint name. For instance, if we specify NOT NULL in a table declaration. Or indeed a primary or unique key. For example:

SQL> create table t23 (id number not null primary key)   2  /  Table created.  SQL> select constraint_name, constraint_type   2  from user_constraints   3  where table_name = 'T23'   4  /  CONSTRAINT_NAME                C ------------------------------ - SYS_C00935190                  C SYS_C00935191                  P  SQL> 

'C' for check, 'P' for primary.

Generally it's a good idea to give relational constraints an explicit name. For instance, if the database creates an index for the primary key (which it will do if that column is not already indexed) it will use the constraint name oo name the index. You don't want a database full of indexes named like SYS_C00935191.

To be honest most people don't bother naming NOT NULL constraints.

vote vote

80

To get a more detailed description (which table/column references which table/column) you can run the following query:

SELECT   uc.constraint_name||CHR(10)    ||      '('||ucc1.TABLE_NAME||'.'||ucc1.column_name||')' constraint_source    ,       'REFERENCES'||CHR(10)    ||      '('||ucc2.TABLE_NAME||'.'||ucc2.column_name||')' references_column FROM user_constraints uc ,   user_cons_columns ucc1 ,   user_cons_columns ucc2 WHERE uc.constraint_name = ucc1.constraint_name AND uc.r_constraint_name = ucc2.constraint_name AND ucc1.POSITION        = ucc2.POSITION -- Correction for multiple column primary keys. AND uc.constraint_type   = 'R' AND uc.constraint_name   = 'SYS_C00381400' ORDER BY ucc1.TABLE_NAME ,   uc.constraint_name; 

From here.

vote vote

74

maybe this can help..

SELECT constraint_name, constraint_type, column_name from user_constraints natural join user_cons_columns where table_name = "my_table_name"; 
vote vote

64

How about

wc -l file.txt | cut -d' ' -f1 

i.e. pipe the output of wc into cut (where delimiters are spaces and pick just the first field)

vote vote

52

Comparison of Techniques

I had a similar issue attempting to get a character count without the leading whitespace provided by wc, which led me to this page. After trying out the answers here, the following are the results from my personal testing on Mac (BSD Bash). Again, this is for character count; for line count you'd do wc -l. echo -n omits the trailing line break.

FOO="bar" echo -n "$FOO" | wc -c                          # "       3"    (x) echo -n "$FOO" | wc -c | bc                     # "3"           (√) echo -n "$FOO" | wc -c | tr -d ' '              # "3"           (√) echo -n "$FOO" | wc -c | awk '{print $1}'       # "3"           (√) echo -n "$FOO" | wc -c | cut -d ' ' -f1         # "" for -f < 8 (x) echo -n "$FOO" | wc -c | cut -d ' ' -f8         # "3"           (√) echo -n "$FOO" | wc -c | perl -pe 's/^\s+//'    # "3"           (√) echo -n "$FOO" | wc -c | grep -ch '^'           # "1"           (x) echo $( printf '%s' "$FOO" | wc -c )            # "3"           (√) 

I wouldn't rely on the cut -f* method in general since it requires that you know the exact number of leading spaces that any given output may have. And the grep one works for counting lines, but not characters.

bc is the most concise, and awk and perl seem a bit overkill, but they should all be relatively fast and portable enough.

Also note that some of these can be adapted to trim surrounding whitespace from general strings, as well (along with echo `echo $FOO`, another neat trick).

Top 3 video Explaining sql - Oracle find a constraint

Related QUESTION?