创建具有其他惟一约束的代理键表

I am self-taught for database design and MYSQL. Thank you for looking at my question. I am currently have a database looks like this in mySQL:

我自学了数据库设计和MYSQL。谢谢你看我的问题。我现在的数据库在mySQL中是这样的:

The reason I used a composite key is I want keep Owner and DevName unique all the time. One problem I can see is that I need to use two foreign keys all the time so Data1 table is not normalized.

我使用组合键的原因是，我希望一直保持所有者和DevName的惟一性。我可以看到的一个问题是，我需要一直使用两个外键，这样Data1表就不会被规范化。

So I made a table looks like this, making another table that has a surrogate key table.

所以我做了一个像这样的表，做了另一个有代理键表的表。

Does this look okay? I am not sure making new table is really a good idea...

这看起来好吗?我不确定新桌子是不是一个好主意……

Is there any to make them in a single table that has a surrogate key, keeping a combination of Owner and DevName unique like this?:

是否有任何方法可以将它们放在一个具有代理键的表中，使所有者和DevName的组合保持唯一?

1 个解决方案

#1

The Device3 and Data3 looks good to me. I wouldn't mess with adding an extra table unless there was some compelling reason to. And likely any compelling reason to create third table boils down to just dealing with the composite foreign key, like in the original Device/Data1 model.

我觉得这台设备和Data3很好。除非有什么令人信服的理由，否则我不会乱加一个额外的表。创建第三个表的任何引人注目的原因可能都可以归结为只处理复合外键，如原始设备/Data1模型。

It seems like all that is missing is UNIQUE constraint. You can add a UNIQUE constraint (i.e. UNIQUE INDEX) on the combination of owner and devname columns. e.g.

似乎所缺少的只是唯一的约束。您可以在所有者和devname列的组合上添加惟一的约束(即惟一索引)。如。

 CREATE TABLE device3 
 ( id       INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
 , owner    VARCHAR(30) NOT NULL COMMENT ''
 , devname  VARCHAR(30) NOT NULL COMMENT ''
 , ...

 , UNIQUE KEY device3_UX1 (owner, devname)

 ) ...

Or, if you already have the device3 table created, you can just add add a unique index to the existing table. e.g.

或者，如果已经创建了device3表，那么只需向现有表添加惟一索引。如。

 CREATE UNIQUE INDEX device3_UX1 ON device3 (owner, devname)

#1