0

I have created my TEST_TABLE table using below query in oracle

  CREATE TABLE "PK"."TEST_TABLE" 
   (    "MYNAME" VARCHAR2(50),  
      "MYVAL1" NUMBER(12,0),
      "MYVAL2" NUMBER(12,0),
      "MYVAL3" NUMBER(12,0) NOT NULL,
     CHECK ("MYVAL1" IS NOT NULL) DEFERRABLE ENABLE NOVALIDATE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM" ;

After this, I want to drop the check constraints applied on column MYVAL1.

For this, first I need to fetch the check constraint name on column MYVAL1. The I can run the alter command to drop that constraint.

So how can I fetch the exact system generated check constraint name on column MYVAL1.

i tried to fetch the data using below query but as search condition is long data type column, it was trowing below error

select * from user_constraints where TABLE_NAME = 'TEST_TABLE'; WHERE TABLE_NAME='TEST_TABLE' AND TO_LOB(search_condition) LIKE '%"MYVAL1" IS NOT NULL%'

ERROR: ORA-00932: inconsistent datatypes: expected - got LONG 00932. 00000 - "inconsistent datatypes: expected %s got %s" *Cause:
*Action: Error at Line: 23 Column: 6 Any clue?

Piyush
  • 5,145
  • 16
  • 49
  • 71
  • Why are you tagging 3 different databases when you know it is only relevant to Oracle? – Sean Lange Jan 21 '16 at 19:20
  • Is there a reason this is tagged for `MySQL` and `SQL Server` in addition to `Oracle`? It looks like this is an Oracle-specific question. – Justin Cave Jan 21 '16 at 19:20
  • Duplicate of: http://stackoverflow.com/questions/11879217/display-names-of-all-constraints-for-a-table-in-oracle-sql perhaps? – xQbert Jan 21 '16 at 19:29
  • @xQbert - here i am looking for the particular column check constraint name which was generated by oracle itself after running alter script. – Piyush Jan 21 '16 at 19:46

1 Answers1

3

There are two ways. First (recommended) - to give name to constraints when creating it. Second - to search in ALL_CONS_COLUMNS (or USER_CONS_COLUMNS) system view.
You need something like this:

select constraint_name
  from all_cons_columns
 where table_name = 'TEST_TABLE'
   and owner = 'PK'
   and column_name = 'MYVAL1'

See documentation: https://docs.oracle.com/cloud/latest/db121/REFRN/refrn20045.htm#REFRN20045

Dmitriy
  • 5,525
  • 12
  • 25
  • 38