根据第三个表上2个表的2个主键创建2个外键 [英] create 2 foreign keys based on 2 primary keys of 2 tables on third table
问题描述
我创建了2个包含2个主键的表。我必须在第三个表中访问2个主键作为外键。在编写代码时，我在访问主键时出错。
代码如下：
表客户端
创建表客户端（
Id_Client nvarchar（10）not null主键，
First_Name nvarchar（30），
Last_Name nvarchar（30），
地址nvarchar（50），
Phone_Number varchar（10））
表产品
创建表产品（
Id_Product nvarchar（10）主键，
Product_Name nvarchar（30），
Measuring_Unit nvarchar（10），
Unit_Price int，
数量int）
表发票
创建表发票（
Id_Invoice nvarchar（10）主键，
Id_Client nvarchar（10），
Id_Product nvarchar（10），
Total_Cost int，
Invoice_Status int
Invoice_Date datetime）
在表格发票中我必须打电话给来自客户端表的id_client和来自product表的id_product作为外键。怎么做????
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????
推荐答案
我想你必须准备好在发票上添加多个产品。在这种情况下，您需要一个更复杂的架构。我建议一般不要使用字符数据作为主键。当它们可以自动生成时更少。这是浪费存储，更难处理。如果是发票编号等自然键，则只有在发票完成后才能生成值以保证连续性 - 因此您必须使用非自然主键。
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
)
我希望它能解决Ur问题:-)
表客户端
创建表客户端（
Id_Client nvarchar（10）not null主键，
）
表产品
创建表产品（
Id_Product nvarchar（10）主键，
）
表发票
创建表发票（
Id_Invoice nvarchar（10）主键，
Id_Client nvarchar（10）FOREIGN KEY REFERENCES客户端（Id_Client） ，
Id_Product nvarchar（10）外国参考参考产品（Id_Product）
）
USE Constraints会更好。
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.
这篇关于根据第三个表上2个表的2个主键创建2个外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
