Un able to make the table in SQl fine error?

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

Thanks buddy!

SQL is fun guys, I mean it is not so hard, It simply needs step by step working..
I love to work on it.

@ 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.😁

@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

@ 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 .😒

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');

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

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 .

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

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.😕

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],

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

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 !!😀

Can you post the line of code you wrote that gave the error?

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));

😛