I have one table in mysql named 'UserFriends' where I am updating my websites user's friends details.
我在mysql中有一个名为'UserFriends'的表,我正在更新我的网站用户的朋友详细信息。
here is the schema of the table (UserFriends)
这是表的架构(UserFriends)
id int,
Userid int,
friendid int,
createdate timespan
now, I want to create unique index on userid & friendid. that i have created unique index well. so, right now i am not able to insert same userid and friendid as duplicate. but if i am inserting same value in opposite field it accept without generating error.
现在,我想在userid和friendid上创建唯一索引。我已经创造了独特的指数。所以,现在我无法插入相同的userid和friendid作为重复。但如果我在相反的字段中插入相同的值,它接受而不会产生错误。
example :
insert into userfriends ( userid, friendid )
select 1, 2 --- insert perfect
insert into userfriends ( userid, friendid )
select 1, 2 --- show error because unique index comes in a picture
now i am inserting
现在我插入
insert into userfriends ( userid, friendid )
select 2, 1 --- records insert here (i don't want this)
How do i prevent this?
我该如何防止这种情况?
1 个解决方案
#1
0
First things first: for your inserts, don't use 'select' to generate the values, as it's liable to become confusing. Instead do it this way:
首先要做的事情是:对于你的插入,不要使用'select'来生成值,因为它可能会让人感到困惑。而是这样做:
INSERT INTO userfriends (userid, friendid) VALUES (1, 2)
That said, the only way to have a unique qualifier that works backwards too is to do it programmatically. If you want to prevent x,y from being accepted, run SELECT * FROM userfriends WHERE userid = y AND friendid = x and if you get a result, reject the insert before it happens. If you don't, then insert into the table as normal, and your unique key will deny it from there on.
也就是说,拥有一个向后工作的唯一限定符的唯一方法是以编程方式执行它。如果要阻止x,y被接受,请运行SELECT * FROM userfriends WHERE userid = y AND friendid = x,如果得到结果,请在插入之前拒绝插入。如果不这样做,则按正常方式插入表中,并且您的唯一键将从那里拒绝它。
#1
0
First things first: for your inserts, don't use 'select' to generate the values, as it's liable to become confusing. Instead do it this way:
首先要做的事情是:对于你的插入,不要使用'select'来生成值,因为它可能会让人感到困惑。而是这样做:
INSERT INTO userfriends (userid, friendid) VALUES (1, 2)
That said, the only way to have a unique qualifier that works backwards too is to do it programmatically. If you want to prevent x,y from being accepted, run SELECT * FROM userfriends WHERE userid = y AND friendid = x and if you get a result, reject the insert before it happens. If you don't, then insert into the table as normal, and your unique key will deny it from there on.
也就是说,拥有一个向后工作的唯一限定符的唯一方法是以编程方式执行它。如果要阻止x,y被接受,请运行SELECT * FROM userfriends WHERE userid = y AND friendid = x,如果得到结果,请在插入之前拒绝插入。如果不这样做,则按正常方式插入表中,并且您的唯一键将从那里拒绝它。