1

I had created the table by specifying the columns as primary key and not null without giving them any constraint names and I have populated the data. I want to give names to them now.

How do I do this in oracle or mysql ?

If I can't do this what's the easiest and next best solution?

  • I think all those unnamed objects get system names. They show up in the corresponding info views. In case of Constraints/Keys it might even be multiple (PK and Index) – eckes Jan 12 at 3:33
  • 1
    Is the question about MySQL or Oracle? Please use the appropriat etag only. – ypercubeᵀᴹ Jan 12 at 11:00
4

When working with Oracle, you can find the constraint names (and other details about constraints) by querying USER_CONSTRAINTS.

Example (Oracle 12c)

-- table with PK and NN constraints
create table test ( 
  id number 
    generated always as identity start with 2000 
    primary key
, description varchar2( 100 ) not null
);

-- populate the table (optional)
insert into test ( description )
select 'description_' || to_char( level )
from dual
connect by level <= 5 ;


select * from test ;
ID    DESCRIPTION    
2000  description_1  
2001  description_2  
2002  description_3  
2003  description_4  
2004  description_5 

Find constraints

select table_name, constraint_name, constraint_type, search_condition
from user_constraints
where table_name = 'TEST' ;  -- table_name: use upper case!

TABLE_NAME  CONSTRAINT_NAME  CONSTRAINT_TYPE  SEARCH_CONDITION           
TEST        SYS_C0021091     P                NULL                       
TEST        SYS_C0021089     C                "ID" IS NOT NULL           
TEST        SYS_C0021090     C                "DESCRIPTION" IS NOT NULL 

Rename the constraints

alter table test
rename constraint SYS_C0021091 to test_pk ;

alter table test
rename constraint SYS_C0021089 to id_nn ;

alter table test
rename  constraint SYS_C0021090 to description_nn ;

Do the constraints have new names now? Yes.

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

TABLE_NAME  CONSTRAINT_NAME  CONSTRAINT_TYPE  SEARCH_CONDITION           
TEST        TEST_PK          P                NULL                       
TEST        ID_NN            C                "ID" IS NOT NULL           
TEST        DESCRIPTION_NN   C                "DESCRIPTION" IS NOT NULL 

Data still intact? Yes.

SQL> select * from test;
ID    DESCRIPTION    
2000  description_1  
2001  description_2  
2002  description_3  
2003  description_4  
2004  description_5
2

If you do this, you can get the names in MySQL:

 SHOW CREATE TABLE table_name;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.