duanhu7615 2016-11-20 20:52
浏览 14
已采纳

如何根据2个外键使用不同的主键

So I have a table in MySQL that has the rows Date, Username, and Hours. What I want to be able to do is to generate a Primary key based off two foreign key values being Date and Username referenced from another table

Since a User can only work X hours on a certain day, but can obviously work many days. Then of course multiple Users can work on the same day. But what would distinguish them is their Username. And what would distinguish each particular user apart from himself is the days that they worked.

What I want to be able to do with this generated PK field is have a SQL query like

INSERT INTO Shift (`Date`, `Username`, `Hours`)
VALUES('$Date', '$username', 5) 
ON DUPLICATE Key UPDATE `Date` = '$today', `Username`= '$username', Hours= $TotalHr

I've considered just making a SQL Query in PHP to test if it the Username and Date are in the table, and either doing an insert or update from there. But that seems like an Ugly work around, surely there has to be a way to achieve this.

  • 写回答

1条回答 默认 最新

  • dongliao3450 2016-11-20 22:10
    关注

    A combined primary key is also called a composite key. An example table with these keys would be:

    CREATE TABLE Shift(
       Date DATETIME,
       Username VARCHAR(75),
       Hours INT(11),
       PRIMARY KEY (Date, Username)
    ) 
    

    You could ofcourse also alter your table with a query.

    ALTER TABLE Shift
    ADD PRIMARY KEY (Date, Username)
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥35 引用csv数据文件(4列1800行),通过高斯-赛德尔法拟合曲线,在选取(每五十点取1点)数据,求该数据点的曲率中心。
  • ¥20 程序只发送0X01,串口助手显示不正确,配置看了没有问题115200-8-1-no,如何解决?
  • ¥15 Google speech command 数据集获取
  • ¥15 vue3+element-plus页面崩溃
  • ¥15 像这种代码要怎么跑起来?
  • ¥15 安卓C读取/dev/fastpipe屏幕像素数据
  • ¥15 pyqt5tools安装失败
  • ¥15 mmdetection
  • ¥15 nginx代理报502的错误
  • ¥100 当AWR1843发送完设置的固定帧后,如何使其再发送第一次的帧