如何将foreing键添加到具有两个主键的表中 [英] how do you add a foreing key to a table that has two primary keys

问题描述

使用LifeSpireDB;





创建表客户（

Customer_ID int，

Customer_Name varchar（50），

Customer_Address varchar（50），

Customer_Phone varchar（50），

主键（customer_ID） ）;



创建表订单（

Order_Num int，

Order_Date varchar（10），
Order_Status varchar（50），

Customer_ID int，

主键（Order_Num），

约束fk_PerCostumer外键（Customer_ID ）引用Customer（Customer_ID））;



创建表ItemsInOrder（

Order_Num int，

Item_ID varchar（ 15）

主键（Order_Num，Item_ID），

约束fk_perItemsOder外键（Order_Num）引用订单（Order_Num））;



创建表项（

Item_UID int，

Item_ID varchar（15），

Item_Description varchar（300）

主键（Item_UID），

缺点traint fk_PerItem外键（Item_ID）引用ItemsInOrder（Item_ID））;







我的表项目有一个外键是表ItemsInOrder中的主键，问题是ItemsInOrder有两个主键。



任何人都可以帮我吗???

解决方案

您好，



首先，您可以只有一个主键。您的主键中可以有多个列。这样的密钥称为复合或复合主键。



根据您的示例：

 创建  table 项目（
 Item_UID  int ， 
 Item_ID  varchar （ 15 ），
 Item_Description  varchar （ 300 ）
  primary   key （Item_UID），
 约束 fk_PerItem  foreign   key （Item_ID） references  ItemsInOrder（ Order_Num，Item_ID ））; 

这很重要：您需要在项表中添加 Order_Num 列（我尚未将其添加到此示例中） 。否则，你就是无法建立外键关系！



--------------------- -------------------------------------------------- ----------------

我对你的数据模型的看法：

1.为什么你需要一个复合主键 ItemsInOrder 表？我会说你可以添加 ItemsInOrder_ID 列并使其成为简单的主键。

2. 项目与 ItemsInOrder <之间的关系/ b>表格应该是一对多。

3. 订单和 ItemsInOrder 表之间的关系应该是一对多。

use LifeSpireDB;


create table Customer(
Customer_ID int,
Customer_Name varchar(50),
Customer_Address varchar(50),
Customer_Phone varchar(50),
primary key (customer_ID));

create table Orders(
Order_Num int,
Order_Date varchar(10),
Order_Status varchar(50),
Customer_ID int,
primary key(Order_Num),
constraint fk_PerCostumer foreign key(Customer_ID) references Customer(Customer_ID));

create table ItemsInOrder(
Order_Num int,
Item_ID varchar(15)
primary key (Order_Num, Item_ID),
constraint fk_perItemsOder foreign key(Order_Num) references Orders(Order_Num));

create table Item(
Item_UID int,
Item_ID varchar(15),
Item_Description varchar(300)
primary key (Item_UID),
constraint fk_PerItem foreign key (Item_ID) references ItemsInOrder(Item_ID));



My table Item has a foreign key which is a primary key in table ItemsInOrder, the problem is the ItemsInOrder has two primary keys.

Can anyone help me please???

解决方案

Hi,

First of all, you can have only one primary key. You can have multiple columns in your primary key. Such key is called a compound or composite primary key.

According to your example:

create table Item(
Item_UID int,
Item_ID varchar(15),
Item_Description varchar(300)
primary key (Item_UID),
constraint fk_PerItem foreign key (Item_ID) references ItemsInOrder(Order_Num, Item_ID));

This is important: You need to have a Order_Num column in your Item table (I haven't added it into this example). Otherwise, you just can't create a foreign key relationship!

---------------------------------------------------------------------------------------
My thoughts about your data model:
1. Why do you need a composite primary key in ItemsInOrder table? I would say that you could add ItemsInOrder_ID column and make it as simple primary key.
2. Relationship between Item and ItemsInOrder tables should be one-to-many.
3. Relationship between Orders and ItemsInOrder tables should be one-to-many.

这篇关于如何将foreing键添加到具有两个主键的表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！