vendor_id varchar2(6) primary key check(vendor_id like 'v%') i want to change check constr-CodePudding

vendor_id varchar2(6) primary key check(vendor_id like 'v%') i want to change check constraint.if not give constraint name how to change this.

CodePudding user response：

Depending on what you want to change, for example the check constraint.

You first need to know the name.

The fiorst select shows you all constraints on your table.

Then you can drop it and add another new constraint, or alter the table as you need.

(when you try the fiddle, you will get an error as teh constraints name changes)

CREATE tABLE A(

vendor_id varchar2(6) primary key check(vendor_id like 'v%') )

select constraint_name, constraint_type, search_condition
from user_constraints where table_name = 'A';

CONSTRAINT_NAME | CONSTRAINT_TYPE | SEARCH_CONDITION   
:-------------- | :-------------- | :------------------
SYS_C00104121   | C               | vendor_id like 'v%'
SYS_C00104122   | P               | null

alter table A drop constraint SYS_C00104121;

ALTER TABLE A MODIFY vendor_id varchar2(10) check(vendor_id like 'va%')

select DBMS_METADATA.GET_DDL('TABLE','A') from DUAL

| DBMS_METADATA.GET_DDL('TABLE','A')                                                                                                                                                                                                                                                                                                                                                                                  |
| :------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| <br>  CREATE TABLE "FIDDLE_XHFUWJCJVRSCWQMWAQVX"."A" <br>   (        "VENDOR_ID" VARCHAR2(10), <br>         PRIMARY KEY ("VENDOR_ID")<br>  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 <br>  TABLESPACE "USERS"  ENABLE, <br>         CHECK (vendor_id like 'va%') ENABLE<br>   ) SEGMENT CREATION DEFERRED <br>  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 <br> NOCOMPRESS LOGGING<br>  TABLESPACE "USERS"  |

db<>fiddle here

CodePudding user response：

Oh, yes - that's how bad habits kick, and they kick hard.

This is what you have:

SQL> create table test
  2    (vendor_id varchar2(6) primary key
  3                           check (vendor_id like 'v%')
  4    );

Table created.

As you didn't name constraints (that's a bad habit), you'll first have to find them by querying user_constraints:

SQL> select constraint_name, constraint_type
  2  from user_constraints
  3  where table_name = 'TEST';

CONSTRAINT_NAME                C
------------------------------ -
SYS_C008774                    C   --> C stands for check constraint
SYS_C008775                    P   --> P stands for primary key constraint

SQL>

When we're here, we'll change both. Drop them first:

SQL> alter table test drop constraint sys_c008774;

Table altered.

SQL> alter table test drop constraint sys_c008775;

Table altered.

SQL>

Now recreate them, but this time give name to those constraints (for future benefits):

SQL> alter table test add
  2    (constraint pk_test primary key (vendor_id),
  3     constraint ch_test check (vendor_id like 'a%')
  4    );

Table altered.

SQL> select constraint_name, constraint_type
  2  from user_constraints
  3  where table_name = 'TEST';

CONSTRAINT_NAME                C
------------------------------ -
PK_TEST                        P
CH_TEST                        C

SQL>

Next time, do it (name the constraints, I mean) when creating a table:

SQL> create table test (vendor_id varchar2(6) constraint pk_test primary key,
  2                                           constraint ch_test check (vendor_id like 'a%'));

Table created.

SQL>