在SQL中制作反对称密钥

发布于 2024-10-14 13:41:00 字数 160 浏览 10 评论 0原文

ALTER TABLE `test` ADD UNIQUE (
`a` ,
`b`
);

我希望在创建这个唯一密钥后，将无法添加B，A。

例如：“Michael”、“Jackson”，不允许稍后插入“Jackson”、“Michael”。

原文

ALTER TABLE `test` ADD UNIQUE (
`a` ,
`b`
);

I want that after creating this unique key, it won't be possible to add B,A.

For example: "Michael", "Jackson", won't allow inserting later "Jackson", "Michael".

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

灯下孤影 2024-10-21 13:41:00

我假设您的应用程序处理除真实姓名之外的其他内容。（因为“Jackson Michael”是英文中的合法名称。“John David”和“David John”也是合法名称。）

最简单的方法是使用 CHECK() 约束来强制两列之间按字母顺序排列。

alter table test
add constraint test_alpha_order
check (a < b);

但请注意，您也可能会遇到区分大小写的问题。也就是说，您的 dbms 可能认为 {'Jackson', 'Michael'} 和 {'jackson', 'michael'} 是两个不同的有效值。如果区分大小写是一个问题，我见过的最常见的解决方案是强制使用小写值，如下所示。

alter table test
add constraint test_alpha_order
check (a = lower(a) and
       b = lower(b) and
       a < b );

I'm going to assume you're application deals with something besides real names. (Because "Jackson Michael" is a legitimate name in English. So are "John David" and "David John".)

The simplest way is to use a CHECK() constraint to enforce alphabetical order between the two columns.

alter table test
add constraint test_alpha_order
check (a < b);

But note that you might run into problems with case sensitivity, too. That is, your dbms might believe that {'Jackson', 'Michael'} and {'jackson', 'michael'} are two different, valid values. If case sensitivity is a problem, the most common solution I've seen is to enforce lowercase values, like this.

alter table test
add constraint test_alpha_order
check (a = lower(a) and
       b = lower(b) and
       a < b );

回复收藏 0 原文

醉生梦死 2024-10-21 13:41:00

与我关于 MySQL 中缺乏检查约束的评论相关，一个众所周知的解决方法是触发器。您将执行以下检查作为 BEFORE INSERT ON 触发器的一部分：

SELECT COUNT(*) from mytable WHERE b=NEW.a and a=NEW.b

但要使此操作失败，您必须求助于 MySQL Trickery 1.0（之前在导致 INSERT 失败的触发器？可能吗？）

因此，您需要类似以下内容：（已测试）

delimiter |
CREATE TRIGGER t1
BEFORE INSERT ON mytable
FOR EACH ROW
BEGIN
      set @x = (SELECT COUNT(*) FROM mytable WHERE b=NEW.a and a=NEW.b);
      IF (@x > 0) THEN
         set @y = (SELECT noSuchField FROM mytable);
      END IF;
END;
|

注意：我忽略了这里不区分大小写——你的问题似乎不需要这样做。

Related to my comment about lack of check constraints in MySQL, a well known workaround has been the trigger. You would do the following check as part of an BEFORE INSERT ON trigger:

SELECT COUNT(*) from mytable WHERE b=NEW.a and a=NEW.b

but to make this cause a failure, you have to resort to MySQL Trickery 1.0 (previously described in TRIGGERs that cause INSERTs to fail? Possible?)

So, you would need something like the following: (tested)

delimiter |
CREATE TRIGGER t1
BEFORE INSERT ON mytable
FOR EACH ROW
BEGIN
      set @x = (SELECT COUNT(*) FROM mytable WHERE b=NEW.a and a=NEW.b);
      IF (@x > 0) THEN
         set @y = (SELECT noSuchField FROM mytable);
      END IF;
END;
|

NOTE: I'm ignoring case insensitivity here -- your question doesn't seem to require that.

回复收藏 0 原文

~没有更多了~