Solved

Oracle Create Table Primary Key

Posted on 2014-10-05
8
465 Views
Last Modified: 2014-10-05
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,
0
Comment
Question by:Dovberman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
8 Comments
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40362176
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
 

Author Comment

by:Dovberman
ID: 40362208
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
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40362213
Here is the code

CREATE TABLE customer  
      (  customer_no            NUMBER 
       CONSTRAINT        customer_no_pk  PRIMARY KEY )

Open in new window

0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

Author Comment

by:Dovberman
ID: 40362225
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
 

Author Comment

by:Dovberman
ID: 40362240
I found out that the created sequence can be shared and does not need to reference a specific table.
0
 
LVL 15

Accepted Solution

by:
Haris Djulic earned 500 total points
ID: 40362258
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
 

Author Comment

by:Dovberman
ID: 40362473
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
 

Author Closing Comment

by:Dovberman
ID: 40362474
Excellent
0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to recover a database from a user managed backup

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question