web2py中的复合键

问题描述

我在 web2py 中定义了一个表

I have a table defined in web2py

db.define_table(
'pairing',
Field('user',writable=True,readable=True),
Field('uid', writable=True , readable=True)
)

该表需要具有唯一的用户和 uid 组合.我已经浏览了 web2py 文档，但没有直接的方法来定义复合键.我们如何在web2py中定义复合方式

This table needs to have user and uid combination being unique. I have looked through the web2py documentation , but there isn't direct way to define composite key .How do we define composite way in web2py

推荐答案

这取决于您要尝试做什么.默认情况下，web2py 会自动创建一个自动递增的 id 字段作为每个表的主键，这是推荐的方法.如果您正在处理具有复合主键的旧数据库并且无法更改架构，则可以指定 primarykey 属性，但有一些限制(如 此处):

It depends on what you are trying to do. By default, web2py automatically creates an auto-incrementing id field to serve as the primary key for each table, and that is the recommended approach whenever possible. If you are dealing with a legacy database with composite primary keys and cannot change the schema, you can specify a primarykey attribute, though with some limitations (as explained here):

db.define_table('pairing',
    Field('user', writable=True, readable=True),
    Field('uid', writable=True, readable=True),
    primarykey=['user', 'uid'])

也许你并不真正需要一个真正的复合主键，但你只需要某种方法来确保只在表中插入唯一的用户/uid 值对.在这种情况下，您可以通过为两个字段之一指定正确构造的 IS_NOT_IN_DB 验证器来实现:

Perhaps instead you don't really need a true composite primary key, but you just need some way to ensure only unique pairs of user/uid values are inserted in the table. In that case, you can do so by specifying a properly constructed IS_NOT_IN_DB validator for one of the two fields:

db.define_table('pairing',
    Field('user', writable=True, readable=True),
    Field('uid', writable=True, readable=True))

db.pairing.uid.requires=IS_NOT_IN_DB(db(db.pairing.user==request.vars.user),
    'pairing.uid')

这将确保 uid 在 user 匹配插入的 user 的新值的记录集中是唯一的(因此组合user 和 uid 必须是唯一的).请注意，验证器(例如 IS_NOT_IN_DB)仅在通过 SQLFORM 或使用 .validate_and_insert() 方法插入值时才应用，因此上述方法不适用于任意插入到表中，但主要用于用户输入提交.

That will make sure uid is unique among the set of records where user matches the new value of user being inserted (so the combination of user and uid must be unique). Note, validators (such as IS_NOT_IN_DB) are only applied when values are being inserted via a SQLFORM or using the .validate_and_insert() method, so the above won't work for arbitrary inserts into the table but is primarily intended for user input submissions.

您还可以使用 SQL 对表设置多列唯一约束(您可以直接在数据库中或通过 web2py .executesql() 方法进行).但是，即使有这样的约束，您仍然希望在应用程序中进行一些输入验证以避免数据库中的错误.

You can also use SQL to set a multi-column unique constraint on the table (which you can do directly in the database or via the web2py .executesql() method). Even with such a constraint, though, you would still want to do some input validation within your application to avoid errors from the database.

这篇关于web2py中的复合键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！