Click here to Skip to main content
14,265,979 members
Rate this:
Please Sign up or sign in to vote.
See more:
I have created 2 tables with 2 primary keys. I have to access the 2 primary keys as foreign keys in the third table. On writing the code i am getting error in accessing the primary keys.
The code is mentioned below:

Table Client

create table Client(
Id_Client nvarchar(10) not null primary key,
First_Name nvarchar(30),
Last_Name nvarchar(30),
Address nvarchar(50),
Phone_Number varchar(10))

Table Product

create table Product(
Id_Product nvarchar(10) primary key,
Product_Name nvarchar(30),
Measuring_Unit nvarchar(10),
Unit_Price int,
Quantity int)

Table Invoice

create table Invoice(
Id_Invoice nvarchar(10) primary key,
Id_Client nvarchar(10),
Id_Product nvarchar(10),
Total_Cost int,
Invoice_Status int
Invoice_Date datetime)

In the table invoice i have to call the id_client from the client table and id_product from the product table as foreign key. How to do it????
Posted
Rate this:
Please Sign up or sign in to vote.

Solution 2

I suppose you have to be prepared to add more than one product to an invoice. In this case you need a little more complicated schema. I suggest don't use character data as primary key in general. Even less when they can be autogenerated. It is a waste of storage and harder to deal with. In case of natural keys like invoice numbers you should generate the value only after the invoice is completed to guarantee continuity - thus you have to use a non-natural primary key.

create table client(
id_client int IDENTITY(1,1) NOT NULL PRIMARY KEY,
name nvarchar(100),
address nvarchar(100)
)

create table product(
id_product int IDENTITY(1,1) NOT NULL PRIMARY KEY,
price numeric(10,3) NOT NULL
)
 
create table invoice(
id_invoice int NOT NULL IDENTITY(1,1) PRIMARY KEY,
fk_client int NOT NULL FOREIGN KEY REFERENCES client(id_client),
invoice_number nvarchar(20) unique,
salesdate date
)

create table invoice_details(
id_invoice_detail int IDENTITY(1,1) NOT NULL PRIMARY KEY,
fk_invoice int FOREIGN KEY REFERENCES invoice(id_invoice),
fk_product int FOREIGN KEY REFERENCES product(id_product),
quantity numeric(10,3) NOT NULL
)
   
Comments
Sunil Pavithran Nair 18-Aug-13 7:32am
   
Mr Zoltan Zorgo,

Thank u sir.....i wish to ask u more doubts in future..If u can kindly give ur mail id so i can mail u my doubts and questions.

Regards,
Sunil Nair
Zoltán Zörgő 18-Aug-13 8:03am
   
Sorry, but I won't give you my email address. This is a professional forum, you can freely address us any kind of question related to programming... you will more likely find your answer in a community than by a single person. Just be concrete and do your part before coming here and respect the forum norms. We are expecting your questions.
Sunil Pavithran Nair 18-Aug-13 8:05am
   
sorry for trying to break forum norms sir. I sincerely regret it sir. I do sincerely hope that you will help me in future also. Thank you sir once again for helping me.
Rate this:
Please Sign up or sign in to vote.

Solution 1

I hope it will solve Ur Problem :-)

Table Client
create table Client(
Id_Client nvarchar(10) not null primary key,
)

Table Product
create table Product(
Id_Product nvarchar(10) primary key,
)

Table Invoice
create table Invoice(
Id_Invoice nvarchar(10) primary key,

Id_Client nvarchar(10) FOREIGN KEY REFERENCES Client(Id_Client) ,
Id_Product nvarchar(10) FOREIGN KEY REFERENCES Product (Id_Product)

)


It will be better to USE Constraints.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100