Monday , September 23 2019
Home / SQL / Define PRIMARY KEY constraint by CREATE TABLE statement in Oracle

Define PRIMARY KEY constraint by CREATE TABLE statement in Oracle

Definition of PRIMARY KEY constraint:

The PRIMARY KEY constraint is used to uniquely identify each record in a single column or combination of columns of a table. It combines UNIQUE constraint and NOT NULL constraint in a single declaration. If you want to restrict a column so that it cannot allow not only null values but also duplicate values in the table column, then you need to define PRIMARY KEY constraint on that column.

Note: If you define a PRIMARY KEY on table column, then the table will be designated as a parent table. And then you refer this PRIMARY KEY column in child table  column on which FOREIGN KEY has been set.

To build a relationship between two or more tables, you must have a PRIMARY KEY constraint for the column of parent table. And the child table have a reference of parent table.

Characteristics of PRIMARY KEY:

  • It never allow duplicate values to be inserted and updated in the column on which PRIMARY KEY constraint has been set.

Suppose you have defined a PRIMARY KEY constraint on the column REGISTRATIONNO  and you have already inserted registration no ‘1001’. If you want to insert  registration no ‘1001’ again, then the system will generate error for duplicate value. In the circumstances, you have to insert another registration no that have not inserted yet may be 1002 or 1003 or 1004.

  • It will not allow null values in the column. So, you have to must provide value.

Restrictions:

  • The PRIMARY KEY constraint can’t be set on the column whose data type is either LOB,LONG,LONG RAW,REF,BFILE,NESTED TABLE,VARRAY and User-Defined-Type.
  • A PRIMARY KEY constraint can be defined on a combination of up to 32 columns.
  • You can’t set both PRIMARY KEY and UNIQUE constraint on the same column or same combination of columns.
  • You can’t specify it on Sub-view. But you can specify for only root view(Top Level)

Note: A table or view can contain only one PRIMARY KEY constraint on a single column or combination of columns. You can define a PRIMARY KEY on combination of columns, but PRIMARY KEY can’t be more than one in a table.

Note: It is ideal to have a PRIMARY KEY in a table.

Note: You can set primary key constraint in two ways either  CREATE TABLE statement or ALTER TABLE statement.

Syntax for CREATE TABLE statement:

CREATE TABLE table_name

(

column_name       Datatype (Length)  CONSTRAINT constraint_name   PRIMARY KEY

);

Here, PRIMARY KEY keyword is used to specify the column as a PRIMARY KEY constraint.

Syntax for ALTER TABLE statement:

ALTER   TABLE   table_name    ADD   CONSTRAINT constraint_name  PRIMARY KEY (constraint_column_name);

Here, constraint_column_name is the name of the constraint for which PRIMARY KEY will be defined.

Set PRIMARY KEY constraint by CREATE TABLE statement:

You can define PRIMARY KEY constraint, when you create a table.

Practice:

This practice will describe how to define PRIMARY KEY constraint when you create a table by CREATE TABLE statement.

Syntax:

CREATE   TABLE   table_name

(

column_name    datatype (length) CONSTRAINT  constraint_name  PRIMARY   KEY

);

Here,

CONSTRAINT  keyword is used to set a constraint.

constraint_name is the name of PRIMARY KEY constraint. The name can be started with the first letter of PRIMARY KEY constraint pk. Then use underscore (_). After that brief column name such as prod_id for PRODUCT_ID column. So, if you set PRIMARY KEY constraint on PRODUCT_ID column, the name of the constraint can be pk_ prod_id. It’s ideal to use small letter in the name.

PRIMARY KEY keyword has been used to define the constraint type as PRIMARY KEY.

Example:    

Step-1:

Drop the PRODUCTINFO table if already exists.

DROP  TABLE   ProductInfo;

Step-2:

Create the PRODUCTINFO table containing 4 columns.

CREATE   TABLE   ProductInfo

(

Product_Id        Varchar2(10) CONSTRAINT  pk_prod_id  PRIMARY KEY,

Product_Name Varchar2(50),

Product_Type  Varchar2(50),

Unit                    Varchar2(30)

);

In the example, a PRIMARY KEY constraint has been defined on PRODUCT_ID column during PRODUCTINFO table creation by CREATE TABLE statement.

Step-3:

Now insert row:

INSERT    INTO    ProductInfo (Product_Id, Product_Name, Product_Type,Unit)   VALUES  (‘P-1001′,’SOAP’,’GENERAL’,’PCS’);

Step-4:

Commit and query to confirm data.

COMMIT;

SELECT * FROM ProductInfo;

Step-5:

In this step, insert the same record that already inserted.

INSERT   INTO   ProductInfo (Product_Id, Product_Name, Product_Type,Unit)   VALUES  (‘P-1001′,’SOAP’,’GENERAL’,’PCS’);

When you attempt to insert the record whose PRODUCT_ID is ‘P-1001’ already exists, the following error will be generated by oracle and the row will not be inserted for primary key constraint (PERVEJ.PK_PROD_ID) violation.

INSERT    INTO   ProductInfo (Product_Id, Product_Name, Product_Type,Unit)  VALUES (‘P-1001′,’SOAP’,’GENERAL’,’PCS’)

Error at line 1

ORA-00001: unique constraint (PERVEJ.PK_PROD_ID) violated

In the circumstance, you have to insert the unique value of PRODUCT_ID column that has not been inserted yet.

Step-6:

Change the value of PRODUCT_ID to ‘P-1002’ instead of ‘P-1001’ and execute INSERT the statement.

INSERT   INTO  ProductInfo (Product_Id, Product_Name, Product_Type,Unit)  VALUES  (‘P-1002’, ‘SHAMPOO’, ‘GENERAL’, ‘PCS’);

Step-7:

Commit and query to confirm whether row has been inserted successfully.

COMMIT;

SELECT Product_Id, Product_Name, Product_Type,Unit FROM ProductInfo;

The Result set of SELECT statement:

PRODUCT_ID PRODUCT_NAME PRODUCT_TYPE UNIT

P-1001        SOAP                    GENERAL             PCS

P-1002        SHAMPOO           GENERAL             PCS

From the above result set, we see that the record for PRODUCT_ID P-1002 has been inserted successfully in the PRODUCTINFO table.

This is how; we can define PRIMARY KEY constraint in CREATE TABLE statement.

Check Also

Set NOT NULL constraint by ALTER TABLE statement in Oracle

You can set NOT NULL constraint on existing table column by ALTER TABLE statement. Practice: …

Set NOT NULL constraint by CREATE TABLE statement in Oracle

Definition of NOT NULL constraint: The NOT NULL constraint is used to restrict null values …

13 comments

  1. It’s amazing to go to see this web site and reading the views of all friends concerning this paragraph, while I am also keen of getting know-how.

  2. Hi everyone, it’s my first pay a visit at this site, and piece of writing is really fruitful in favor of me, keep up posting these types of articles or reviews.

  3. My brother recommended I might like this website. He was totally right. This post actually made my day. You cann’t imagine just how much time I had spent for this info! Thanks!

  4. I know this web site provides quality depending content and additional information, is there any other website which gives these stuff in quality?

  5. My brother suggested I might like this blog. He was entirely right. This post truly made my day. You cann’t imagine just how much time I had spent for this information! Thanks!

  6. I’m extremely impressed with your writing abilities and also with the structure on your blog. Is this a paid subject or did you modify it your self? Either way stay up the nice high quality writing, it is uncommon to see a nice blog like this one today..

    • I have wrote this post myself. Not only this post but also every post is written by myself. I am focusing here my 10 years of programming experiences.

  7. Do you mind if I quote a couple of your articles as long as I provide credit and sources back to your website? My blog site is in the exact same niche as yours and my visitors would genuinely benefit from some of the information you present here. Please let me know if this alright with you. Thanks a lot!|

  8. May I simply just say what a comfort to uncover somebody who actually understands what they are discussing on the web. You actually realize how to bring an issue to light and make it important. A lot more people must check this out and understand this side of your story. I can’t believe you are not more popular given that you definitely possess the gift.

  9. I just want to mention I’m newbie to blogging and site-building and actually liked this website. More than likely I’m planning to bookmark your site . You amazingly have great articles and reviews. Regards for revealing your web site.

  10. You should compliment this article with a video or infographic. You will really get a lot of social shares. Thank you!!

  11. “I’m really impressed along with your writing abilities neatly as with the layout in your blog. Is this a paid topic or did you modify it your self? Anyway keep up the excellent quality writing, it is uncommon to look a great weblog like this one these days.”

Leave a Reply

Your email address will not be published. Required fields are marked *