CODEWITHSUNDEEP
sqloracle-databaseforeign-keysconstraints
user2643050
user2643050

Reputation: 41

Create table with an error

I created a table called ITEM

CREATE TABLE item
(Item_no NUMBER(4) CONSTRAINT item_item_no_pk PRIMARY KEY, 
item_name VARCHAR2 (50) CONSTRAINT item_item_name_nn NOT NULL, 
Category_code VARCHAR2 (3) CONSTRAINT item_category_code_nn NOT NULL,
Qty_in NUMBER (5) CONSTRAINT item_qty_in_ck CHECK (qty_in > 0), 
Qty_out NUMBER (5) CONSTRAINT item_qty_out_ck CHECK (qty_out > 0), 
Qty_bal NUMBER (5) CONSTRAINT item_qty_bal_ck CHECK (qty_bal >= 0),
Last_purchase_date DATE, 
Last_sale_date DATE, 
Cost_price NUMBER (5,2) CONSTRAINT ITEM_cost_price_ck CHECK (cost_price > 0),
Sales_price NUMBER (5,2) CONSTRAINT ITEM_sales_price_ck CHECK (sales_price > 0));

Now I’m trying to create a category table

CREATE TABLE category
   (Category_code VARCHAR2(3) NOT NULL,  
    CONSTRAINT category_Category_code_fk 
    FOREIGN KEY (Category_code) REFERENCES item (Category_code), 
    Category_name VARCHAR2(50) NOT NULL);

And I’m getting this error:

SQL Error: ORA-00955: name is already used by an existing object 00955. 00000 - "name is already used by an existing object"

I am not sure what i'm missing since I know you can have column names that are the same...I've been going in circles. If any one can point me in the right direction I would appreciate it.

Upvotes: 1

Views: 305

Answers (1)

Anoop
Anoop

Reputation: 369

Try this modified sql code

CREATE TABLE category
(
 Category_code VARCHAR(3) PRIMARY KEY,
 Category_name VARCHAR(50) NOT NULL
)

CREATE TABLE item
(
 Item_no            NUMERIC(4, 2) CONSTRAINT item_item_no_pk PRIMARY KEY,
 item_name          VARCHAR (50) CONSTRAINT item_item_name_nn NOT NULL,

 Category_code      VARCHAR(3) CONSTRAINT item_category_code_nn NOT NULL
      CONSTRAINT    category_Category_code_fk 
      FOREIGN KEY   (Category_code) 
      REFERENCES    category (Category_code),

 Qty_in             NUMERIC(4, 2) CONSTRAINT item_qty_in_ck CHECK (qty_in > 0),
 Qty_out            NUMERIC(4, 2) CONSTRAINT item_qty_out_ck CHECK (qty_out > 0),
 Qty_bal            NUMERIC(4, 2) CONSTRAINT item_qty_bal_ck CHECK (qty_bal >= 0),
 Last_purchase_date DATE,
 Last_sale_date     DATE,
 Cost_price         NUMERIC(4, 2) CONSTRAINT ITEM_cost_price_ck CHECK (cost_price > 0),
 Sales_price        NUMERIC(4, 2) CONSTRAINT ITEM_sales_price_ck CHECK(sales_price > 0)
)

When you create one Foreign Key Relationship,

  1. First create the table that contains the primary key, (Category table ).

  2. Define the primary key, (Category_code VARCHAR(3) PRIMARY KEY)

  3. Create table that reference Category_code, (Item table).

  4. Create reference i.e Foreign Key

    Category_code VARCHAR(3) CONSTRAINT item_category_code_nn NOT NULL CONSTRAINT category_Category_code_fk FOREIGN KEY (Category_code) REFERENCES category (Category_code)

Upvotes: 1

Related Questions