I have 2 columns 'id' and 'date' in a table, I want to avoid to insert repeated dates for the same id, I mean
我在表中有2列'id'和'date',我想避免为同一个id插入重复的日期,我的意思是
id | date
------------------
1 | 01/01/2015
2 | 01/01/2015
3 | 02/01/2015
1 | 01/01/2015 <--- this value is already insert for id 1, and should not be allowed for id 1
2 个解决方案
#1
1
A safe way to handle your situation would be to add a unique constraint on the combination of the id and date columns:
处理您的情况的一种安全方法是在id和date列的组合上添加唯一约束:
ALTER TABLE `table` ADD UNIQUE `ui`(`id`, `date`);
After adding this constraint, an attempt to insert a duplicate record will generate an error. As a note, if you use this option it will also remove duplicates which already exist in your table.
添加此约束后,尝试插入重复记录将生成错误。请注意,如果您使用此选项,它还将删除表中已存在的重复项。
#2
0
My understanding of the question is you do not want two rows to have the same value. You can use the unique keyword, but that would restrict the column to only have one of each value. So it will not allow id 1 and 2 to both have the same date.
我对这个问题的理解是你不希望两行具有相同的值。您可以使用unique关键字,但这会限制列只包含每个值中的一个。所以它不允许id 1和2都具有相同的日期。
If you really don't want to duplicate rows, then you need to check to see if the row is already in the table. If it is not there then add the row.
如果您确实不想复制行,则需要检查该行是否已存在于表中。如果不存在则添加行。
The other option is to add the duplicate rows and then use a "select distinct" when searching the database.
另一个选项是添加重复的行,然后在搜索数据库时使用“select distinct”。
#1
1
A safe way to handle your situation would be to add a unique constraint on the combination of the id and date columns:
处理您的情况的一种安全方法是在id和date列的组合上添加唯一约束:
ALTER TABLE `table` ADD UNIQUE `ui`(`id`, `date`);
After adding this constraint, an attempt to insert a duplicate record will generate an error. As a note, if you use this option it will also remove duplicates which already exist in your table.
添加此约束后,尝试插入重复记录将生成错误。请注意,如果您使用此选项,它还将删除表中已存在的重复项。
#2
0
My understanding of the question is you do not want two rows to have the same value. You can use the unique keyword, but that would restrict the column to only have one of each value. So it will not allow id 1 and 2 to both have the same date.
我对这个问题的理解是你不希望两行具有相同的值。您可以使用unique关键字,但这会限制列只包含每个值中的一个。所以它不允许id 1和2都具有相同的日期。
If you really don't want to duplicate rows, then you need to check to see if the row is already in the table. If it is not there then add the row.
如果您确实不想复制行,则需要检查该行是否已存在于表中。如果不存在则添加行。
The other option is to add the duplicate rows and then use a "select distinct" when searching the database.
另一个选项是添加重复的行,然后在搜索数据库时使用“select distinct”。