Oracle SQL - Add Primary Key to Table

How to add primary key constraint on oracle?

You haven't added a primary key, you've added a unique constraint. While a unique constraint and a not-null constraint are effectively the same, they are not actually the same as an actual primary key.

As @GurwinderSingh said, the SYS_C00403053 is a system-generated name for the not-null constraint. It is possible, but unusual, to name a not-null constraint:

-- just to clean up what you have in the question, remove the unique constraint
ALTER TABLE buses DROP CONSTRAINT PK_BUSES;

ALTER TABLE buses MODIFY Bus_no NULL;

ALTER TABLE buses MODIFY Bus_no CONSTRAINT BUS_NO_NOT_NULL NOT NULL;

desc buses

Name        Null?    Type         
----------- -------- ------------ 
BUS_NO      NOT NULL NUMBER(11)   
BUS_NAME             VARCHAR2(20) 
TYPE                 VARCHAR2(20) 
TOTAL_SEATS          NUMBER(11)   
AVAIL_SEATS          NUMBER(11)   

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

CONSTRAINT_NAME                C SEARCH_CONDITION                                                                
------------------------------ - --------------------------------------------------------------------------------
BUS_NO_NOT_NULL                C "BUS_NO" IS NOT NULL                                                            

But as you want a primary key anyway, you can drop the separate not-null check, as it's implied by a (proper) primary key:

ALTER TABLE buses MODIFY Bus_no NULL;

ALTER TABLE buses ADD CONSTRAINT PK_BUSES PRIMARY KEY (Bus_no);

desc buses

Name        Null?    Type         
----------- -------- ------------ 
BUS_NO      NOT NULL NUMBER(11)   
BUS_NAME             VARCHAR2(20) 
TYPE                 VARCHAR2(20) 
TOTAL_SEATS          NUMBER(11)   
AVAIL_SEATS          NUMBER(11)   

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

CONSTRAINT_NAME                C SEARCH_CONDITION                                                                
------------------------------ - --------------------------------------------------------------------------------
PK_BUSES                       P                                                                                 

You now only see the primary key constraint listed, but the column is still marked as not nullable, and you get the same error if you try to insert null:

insert into buses (bus_no) values (null);

ORA-01400: cannot insert NULL into ("MY_SCHEMA"."BUSES"."BUS_NO")

Add a primary key column to an old table

(From 12.1) You can add a new auto-incremented surrogate key to a table with either:

alter table t
  add ( t_id integer generated by default as identity );

Or

create sequence s;
alter table t
  add ( t_id integer default s.nextval );

These set the value for all the existing rows. So may take a while on large tables!

You should also look to add a unique constraint on the business keys too though. To do that, take the steps Marmite Bomber suggests.

Oracle SQL - Add Primary Key to table

Update person set id = rownum;

Is it possible to add a primary key to a table from a PL/SQL block?

Possible? Yes, with dynamic SQL.

SQL> create table test (id number);

Table created.

SQL> begin
  2    execute immediate 'alter table test add constraint pk_test primary key (id)';
  3  end;
  4  /

PL/SQL procedure successfully completed.

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

CONSTRAINT_NAME
------------------------------
PK_TEST

SQL>

How to add composite primary key to table

In Oracle, you could do this:

create table D (
  ID numeric(1),
  CODE varchar(2),
  constraint PK_D primary key (ID, CODE)
);

Oracle 10g Add primary key as sequence for existing table

Try updating PAYMENT table with the following

update payment p set p.mem_id  = (select mem_id from member where 
memberid = p.memberid);

How to add a primary key to a View - Oracle

Building on Ivan's comment, you could build your view like this:

SELECT rownum, *
  FROM (your_current_view_query)

---

Related Topics