How do I to keep the values of a column unique in MySQL?

Question

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.

Answer 1

I'm assuming that you want each column to be unique, rather than each composite key to be unique. Use a unique constraint on the columns that should be unique:

• http://www.java2s.com/Code/SQL/Select-Clause/Altertableaddunique.htm
• http://www.java2s.com/Code/SQL/Select-Clause/SettingaUniqueConstraint.htm

I'm not sure what happens if you add a unique constraint to MySQL on a column that doesn't have unique values already. You might have to perform manual cleanup before it will let you add the constraint.

You definitely shouldn't do this in Perl. Good data driven apps are about getting all of the logic of the app as close to the data model as possible. This one belongs database side.

Answer 2

ALTER TABLE MyTable DROP PRIMARY KEY

ALTER TABLE MyTable
    ADD PRIMARY KEY (`S.No`),
    ADD UNIQUE KEY `PCID_uk` (`PCID`),
    ADD UNIQUE KEY `USERNAME_uk` (`USERNAME`)

If the file you're importing from contains duplicate values and you want the duplicate values to be discarded, use the IGNORE keyword. If you're using LOAD DATA INFILE then this is achieved using syntax like this:

LOAD DATA INFILE 'file_name' IGNORE INTO TABLE ...

See this documentation page.

Answer 3

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`);