Solved

Oracle Create Table Primary Key

Posted on 2014-10-05
8
408 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
  • 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
 

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

757 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now