Un able to make the table in SQl fine error?

I want to create table sales_master and I have already table customer see below but why it give me error see:


/

Name Null? Type
----------------------------------------- -------- ----------------------------
CUST_ID NOT NULL NUMBER(38)
CUST_NAME NOT NULL VARCHAR2(20)
CUST_ADDR NOT NULL VARCHAR2(20)
CUST_STATUS VARCHAR2(10)
CUST_CITY VARCHAR2(20)


When I create table i.
 create table sales_master
 (
   inv_no integer not null primary key,
   cust_id number(3) not null,
   cust_name varchar2(20) ,
     foreign key (cust_id,cust_name) references customer(cust_id,cust_name) ,
    item_id number(3) not null ,
    item_name varchar2(20),
    item_type  varchar2(20),
    ITEM_PRICEIN   integer not null,
    ITEM_PRICEOUT  integer not null,
    sal_date date  ,
     foreign key (item_id,item_name,item_type,item_pricein,item_priceout) references item_master
 (item_id,item_name,item_type,item_pricein,item_priceout),
   item_qty number(7,2) ,
    foreign key(item_qty) references item_master(item_qty),
    total_amt number(7,2)
 );
It give me the following error ..πŸ˜”

SQL> //
     foreign key (scust_id,cust_name) references customer(cust_id,cust_name) ,
                                                          *
ERROR at line 6:
ORA-02270: no matching unique or primary key for this column-list
can you please correct this ??πŸ˜”

Replies

  • sarveshgupta
    sarveshgupta
    Safwan whenever you make a foreign key you must ensure that the foreign key is a reference to the primary key of another table

    In the customer table you are refering you want to refer to the cust_id and cust_name

    But see that the customer table does not have these fields as primary key

    If you want to create sales_master table, first alter the table to define these two attributes as a primary key(Here they will make the composite primary key that is more than attributes making the primary key) then try executing the create table statement for sales_master

    It should help. Do tell if it solves the problem
  • safwan
    safwan
    Thanks buddy!
  • kumar_nitin
    kumar_nitin
    SQL is fun guys, I mean it is not so hard, It simply needs step by step working..
    I love to work on it.
  • safwan
    safwan
    @ sarvesh man when I checked the table already the cust_id is primary key in table customer .

    see this when i tried to insert same value for second time what i got is :

    SQL> insert into customer values(001,'Gupta co.','Boriwalli','good','Mumbai');
    
    1 row created.
    
    SQL> insert into customer values(001,'Mahesh Tec.Ltd.','Gudgaou','modrate','UP');
    insert into customer values(001,'Mahesh Tec.Ltd.','Gudgaou','modrate','UP')
    *
    ERROR at line 1:
    ORA-00001: unique constraint (SYSTEM.SYS_C005526) violated
    
    but still it gives me same error .πŸ˜’

    Kumar _nitin yes its easy but require daily practice.😁
  • sookie
    sookie
    @safwan

    Very first thing, you have not mentioned primary key of your Customer table in your very first post.. From last post, we concluded it is cust_id.
    create table sales_master
     (
       inv_no integer not null primary key,
       cust_id number(3) not null,
       cust_name varchar2(20) ,
         foreign key (cust_id,cust_name) references customer(cust_id,cust_name) ,
        item_id number(3) not null ,
        item_name varchar2(20),
        item_type  varchar2(20),
        ITEM_PRICEIN   integer not null,
        ITEM_PRICEOUT  integer not null,
        sal_date date  ,
         foreign key (item_id,item_name,item_type,item_pricein,item_priceout) references item_master
     (item_id,item_name,item_type,item_pricein,item_priceout),
       item_qty number(7,2) ,
        foreign key(item_qty) references item_master(item_qty),
        total_amt number(7,2)
     );
    
    If our conclusion is right then as per Mr Sarvesh, your above query should not give following error any more.
    [COLOR="Red"]SQL> //
         foreign key (scust_id,cust_name) references customer(cust_id,cust_name) ,
                                                              *
    ERROR at line 6:
    ORA-02270: no matching unique or primary key for this column-list
    [/COLOR]
    Reason: A FOREIGN KEY in one table points to a PRIMARY KEY in another table.

    So problem solved but now problem what you did was you are trying to enter two records in customer table that has same primary key [cust_id].

    SQL> insert into customer values(001,'Gupta co.','Boriwalli','good','Mumbai');
    
    1 row created. 
    
    SQL> insert into customer values(001,'Mahesh Tec.Ltd.','Gudgaou','modrate','UP');
    [COLOR="Red"]insert into customer values(001,'Mahesh Tec.Ltd.','Gudgaou','modrate','UP')
    *
    ERROR at line 1:
    ORA-00001: unique constraint (SYSTEM.SYS_C005526) violated
    [/COLOR]
    Just check once cust_id you are passing in both the insert statements, they are same '001' but always remember primary key is unique and not null.

    So, bottom line: Be clear about what primary key you want to keep in your customer table and then use the same as a foreign key in your next table query.

    Any doubts feel free to ask. πŸ˜€

    Thanks !
    -Sookie
  • safwan
    safwan
    @ sokii

    thanks, I am clear about Primary key πŸ˜€ but when I got that for reference (means we require unique or primary key constraints ) I altered table πŸ˜‰and set Cust_id as primary key in table Customer .I showed second error to show that the cust_id is primary key then also its not working.πŸ˜” But wait !! while writing this I checked it again it showing same error as before .πŸ˜’
  • sookie
    sookie
    safwan
    @ sokii
    I showed second error to show that the cust_id is primary key then also its not working.πŸ˜” But wait !! while writing this I checked it again it showing same error as before .πŸ˜’
    Wait wait..Are you again the following two records?
    SQL> insert into customer values(001,'Gupta co.','Boriwalli','good','Mumbai');
    
    SQL> insert into customer values(001,'Mahesh Tec.Ltd.','Gudgaou','modrate','UP');
    
    Dear try to insert following two records? Don't keep cust_id as 001 for both the records because it is a primary key of the table.
    SQL> insert into customer values([COLOR="Red"]001[/COLOR],'Gupta co.','Boriwalli','good','Mumbai');
    
    SQL> insert into customer values([COLOR="Red"]002[/COLOR],'Mahesh Tec.Ltd.','Gudgaou','modrate','UP');
    
  • sarveshgupta
    sarveshgupta
    Sorry Safwan for late reply and sookie thanks for taking the charge

    Now as I have seen remember following things always

    Super Key: is the all the attributes that collectively help us identify a record uniquely

    Primary Key : is the minimal super key means that the minimum of attributes that can be used to identify a record uniquely instead of using all the attributes

    Foreign key : is a key in a table that wants help of a primary key defined in another relation/table to help recognise records of this table... It is usually used to extend the attributes of a relation or refer to another table
    Say in this case sales_master want to know the customer id to store the customer details corresponding to his id number as its details are related to the cust_id

    Since a foreign key is refering to a primary key that is it is like copying the primary key column into this table, so it is compulsive for the foreign key column to follow the properties of primary key..

    And primary key cannot be null and it must have a unique value... it cannot have duplicate values

    So as Sookie pointed out while inserting the values in table make sure you never insert any duplicate value

    as you have already inserted 001 as first record now insert any value of cust_id except 001 as cust_id is primary key means every record should have a unique value
  • safwan
    safwan
    Sokii and Sarvesh I am clear about the primary key concept and sarvesh thanks for adding some more information in previous post .

    But wait !! while writing this I checked it again it showing same error as before .πŸ˜’
    But ,at sokii 😲
    I was reffering this error not related to insert command but related to Create table command .πŸ˜‰


    create table sales_master
    (
    inv_no integer not null primary key,
    cust_id number(3) not null,
    cust_name varchar2(20) ,
    foreign key (cust_id,cust_name) references customer(cust_id,cust_name) ,
    item_id number(3) not null ,
    item_name varchar2(20),
    item_type varchar2(20),
    ITEM_PRICEIN integer not null,
    ITEM_PRICEOUT integer not null,
    sal_date date ,
    foreign key (item_id,item_name,item_type,item_pricein,item_priceout) references item_master
    (item_id,item_name,item_type,item_pricein,item_priceout),
    item_qty number(7,2) ,
    foreign key(item_qty) references item_master(item_qty),
    total_amt number(7,2)
    );
    Let me try as per sarvesh .
  • sarveshgupta
    sarveshgupta
    Safwan I just saw that you are specifying the foreign key constraint explicitly after the column that is it is being defined as a table level constraint

    Actually I am not sure about this but you can try defining foreign key constraint as a column level constraint

    you can do it like this
    cust_id number(3,0) not null references customer(cust_id),
    
    instead of
    cust_id number(3) not null,
    cust_name varchar2(20) ,
    foreign key (cust_id,cust_name) references customer(cust_id,cust_name) ,
    
    Maybe it helps
  • safwan
    safwan
    Sarvesh when I tried your suggestion it gave me this error :

    18 ;
    cust_id number(3) not null unique,references customer( cust_id),
    *
    ERROR at line 4:
    ORA-00907: missing right parenthesis
    Now let me tell you that I used this option of second method becouse it was giving me above error.

    And when I tried the Super key in cust_id I got the following error :
    SQL> create table customer(
    2 cust_id number(3) not null super key,
    3 cust_name varchar2(30) ,
    4 cust_addr varchar2(30),
    5 cust_status varchar2(10),
    6 cust_city varchar2(20));
    cust_id number(3) not null super key,
    *
    ERROR at line 2:
    ORA-00907: missing right parenthesis
    πŸ˜”πŸ˜”
    Please do give some tips to correct this errors and let me tell you just because of this error my database is pending for my grad 12 final project of Informatics Practices . So if you suggest then i will go for MS access but i don't know their also how to create this kind of relationship.πŸ˜•
  • Mahesh Dahale
    Mahesh Dahale
    Actually I am not sure about this but

    CREATE TABLE customer
    (cust_id NUMBER(3), 
    cust_name VARCHAR2(30),
    cust_addr VARCHAR2(30),
    cust_status VARCHAR2(10),
    cust_city varchar2(20),
    CONSTRAINT customer_cust_id_pk PRIMARY KEY (cust_id));
    Try this without comma, Spaces
    cust_id number(3) not null unique[COLOR=Red][B],[/B][/COLOR]references customer[COLOR=Red]( cust_id)[/COLOR],
  • sarveshgupta
    sarveshgupta
    Yes Safwan, Mahesh is right remove the word unique and ',' and any space within brackets
    the constraint is specified along with the column description it should not be seperated by using any seperator

    Hope this time it works
  • safwan
    safwan
    sarveshgupta
    Yes Safwan, Mahesh is right remove the word unique and ',' and any space within brackets
    the constraint is specified along with the column description it should not be seperated by using any seperator

    Hope this time it works
    It gave me error of 'missing right parenthesis 'some thing like that.😲

    But Guys problem solved i tailored the tables by referring to Mahesh !!πŸ˜€
  • sarveshgupta
    sarveshgupta
    Can you post the line of code you wrote that gave the error?
  • safwan
    safwan
    well, I had done it yesterday night after going from forum so I dont have it now.
    But I did all this stuffs today and i have already closed the SQl command line so I wont be able to take it back. But I can show you my code of tables which I created .
    CREATE TABLE customer
    (cust_id NUMBER(5), 
    cust_name VARCHAR2(30),
    cust_status VARCHAR2(30),
    cust_addr VARCHAR2(30),
    city VARCHAR2(30),
    CONSTRAINT customer_cust_id_pk PRIMARY KEY (cust_id));
    
    
    ------------------------------------------------------------------------------------------------------------------------------------------------------------------
    
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    create table sales_master
    ( inv_no number(5) not null,
      cust_id number(5) not null,
     cust_name varchar2(30) ,
     item_id number(3) ,
    item_name varchar2(30),
    item_type varchar2(30),
    item_pricein number(7,2),
    item_priceout number(7,2),
    sal_dat date not null,
    CONSTRAINT cust_sales_master_fk FOREIGN KEY (cust_id) REFERENCES customer(cust_id),
    Constraint sal_inv_no_pk Primary key (cust_id));
    
    
    
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    
    create table item_master(
    item_id  number(5) not null,
    item_name  varchar2(30) not null,
    item_type varchar2(20),
    item_qty number(7),
    item_pricein number(7,2),
    item_priceout number(7,2),
    supp_name varchar2(30),
    purchase_date date ,
    sal_dat date,
    CONSTRAINT sal_item_master_fk Foreign key(sal_dat) references sales_master(sal_dat),
    Constraint    supp_item_master_fk Foreign key(supp_name) references supplier(supp_name));
    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    create table supplier(
    supp_id number(5) not null primary key,
    supp_name varchar2(30),
    item_pricein number(7,2) foreign key));
    
    
    πŸ˜›

You are reading an archived discussion.

Related Posts

Automatic spelling correction Starting this week, when your query was misspelled, Google goes a step further than asking "Did you mean..." by automatically showing results for the corrected query, saving...
In a tugoff war to capture the internet Microsoft has announced some great enhancements to its search engine Bing being touted as Bing 2.0 The features are: * The beta...
Microsoft revealed some information about what’s coming in Internet Explorer 9 and Silverlight 4 Internet Explorer 9 IE team is about three weeks into the IE 9 project. Microsoft is...
Hi, i m final yr BE student. i want to make a project on " Robot for solving rubics cube using image processing". i had go on through various sites...
Gmail has long had keyboard shortcuts, though learning them can be difficult. Enter the Gboard, a specialized mini-keyboard for Google's e-mail service. It debuts this Friday at an asking price...