Oracle Create Table Primary Key

Need to verify the primary key syntax and usage.

CREATE TABLE customer  
     (  customer_no            NUMBER
      CONSTRAINT        customer_no_pk  PRIMARY KEY (customer_no)

When rows are inserted, will the customer_no increment by 1?

Does the insert statement need to specify the customer_no column name?

INSERT INTO customer(customer_no, cust_name, cust_phone)
VALUES( ???, 'Mike', '385-367-1234');

Thanks,
DovbermanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Haris DulicCommented:
Oracle databases before 12c do not support the autoincrement. In version 12c it was introduced:

http://docs.oracle.com/database/121/DRDAA/migr_tools_feat.htm#DRDAA109

For purpose of the autoincrement columns you need to create sequence and on insert triger will populate the column with newxt val from sequence...

as part of the creation of the table you should do it

CREATE TABLE customer  (
  customer_no            NUMBER);

ALTER TABLE customer  ADD (
  CONSTRAINT customer_no_pk  PRIMARY KEY (customer_no ));

Open in new window

0
DovbermanAuthor Commented:
Yes, I should have said that I am using 10g.
Can I still include the constraint in the Create Table syntax?

CREATE TABLE customer  
      (  customer_no            NUMBER
       CONSTRAINT        customer_no_pk  PRIMARY KEY (customer_no)

I will find out more about insert triggers and new value for incrementing the next number in a sequence.

Thanks,
0
Haris DulicCommented:
Here is the code

CREATE TABLE customer  
      (  customer_no            NUMBER 
       CONSTRAINT        customer_no_pk  PRIMARY KEY )

Open in new window

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

DovbermanAuthor Commented:
10g is definitely different.

CREATE TABLE customer  
       (  customer_no            NUMBER
        CONSTRAINT        customer_no_pk  PRIMARY KEY (customer_no)
Should be:

CREATE TABLE customer  
       (  customer_no            NUMBER
        CONSTRAINT        customer_no_pk  PRIMARY KEY )
       CREATE SEQUENCE customer_no_seq start with 1
     ,  birth_date          DATE
     , etc....

Is the sequence defined within the primary key column definition as shown above?

CREATE TABLE customer  
       (  customer_no            NUMBER
        CONSTRAINT        customer_no_pk  PRIMARY KEY )
       CREATE SEQUENCE customer_no_seq start with 1
     ,  birth_date          DATE
     , etc....

OR is it defined within the CREATE TABLE block as shown below?

CREATE TABLE customer  
       (  customer_no            NUMBER
        CONSTRAINT        customer_no_pk  PRIMARY KEY )
           ,  birth_date          DATE
     , etc....);

CREATE TABLE customer  
       (  customer_no            NUMBER
        CONSTRAINT        customer_no_pk  PRIMARY KEY )
       ,  birth_date          DATE
     , etc....);

     CREATE SEQUENCE customer_no_seq start with 1 -- Where is the table name referenced?

Thanks,
0
DovbermanAuthor Commented:
I found out that the created sequence can be shared and does not need to reference a specific table.
0
Haris DulicCommented:
Sequence is created separably and represents independent database object and can be used in other queries but take not that when you use the sequence it increases the counter..

basically, you create trigger and then check for the value in the customer column and then if null replace it with next_val from sequence...


create sequence
CREATE SEQUENCE cust_SEQ;

Create before insert trigger
CREATE OR REPLACE TRIGGER   CUST_TRI
BEFORE INSERT ON CUSTOMER
FOR EACH ROW
WHEN (new.customer IS NULL)
BEGIN
  SELECT cust_seq.NEXTVAL
  INTO   :new.customer
  FROM   dual;
END;

do not forget to enable trigger

ALTER TRIGGER CUST_TRI ENABLE ;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DovbermanAuthor Commented:
This is new to me as a MS SQL Server developer.

MS SQL Server is similar to Oracle 12. You specify in the table creation that the primary key is self  increments by one.

This helps.

Thank you.
0
DovbermanAuthor Commented:
Excellent
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.