I do file parsing in Perl and insert into a table in a MySQL database. For example, I have the following fields:
S.No ,PCID,USERNAME, TIME INFORMATION.
1 203 JANE 22:08 updation
2 203 JANE 22:09 deletion
3 203 JANE 22:10 insertion
In this table I wanted to have the PCID to be unique, USERNAME to be unique. S.No is unique, since I have set it to autonumbering and it's the primary key. Now my question is:
If I add PCID and USERNAME as composite primary key, I still find duplicates in the table. There is no change. The same o/p. What should be done to remove the duplicate? Should I code in Perl to check for duplicates before insertion?
Please guide and provide assistance. Thanks in advance.
解决方案
You want the S.No to remain the primary key and PCID + USERNAME to be unique, so close to what Hammerite said:
ALTER TABLE MyTable
ADD PRIMARY KEY (`S.No`),
ADD UNIQUE KEY `PCID_USER_uk` (`PCID`, `USERNAME`);