Howto set fields to not be unique in mysql

Databases
1

Hi,
I am trying to update a record in a mysql database but get the following error.

      Error updating submitted teaching jjjj points.    
  

exception 'PDOException' with message 'SQLSTATE[23000]: Integrity 
constraint violation: 1062 Duplicate entry '21' for key 'PRIMARY'' in 
/home2/shanegib/public_html/artgibney/admin/teachingpoints/index.php:280
Stack trace:
#0 
/home2/shanegib/public_html/artgibney/admin/teachingpoints/index.php(280):
 PDOStatement->execute()
#1 {main}

The database looks like this,

1079×385 107 KB

The data looks like this,

1084×192 49.1 KB

I think the problem is all the fields are set to be unique values. So how do I change that?
Or is the problem that the fields are nearly all set to PRIMARY. If so how do I change that?
Thanks,
Shane

2

A primary/composite key has to be unique.

Though something is very wrong with your model, considering what columns your primary key consist of.

Could you paste the CREATE SQL for the table?

To only have the primary key be the id column, you can use the SQL code below, if you want it to be a composite key, just add more column names with a comma after the id.

ALTER TABLE [table name]
DROP PRIMARY KEY,
ADD PRIMARY KEY (id);

3

Hi,
Thanks for your reply. When you say

Do you mean that I should create the whole table again?
I ran

But I am still getting the same error when I try to edit or update the table.

      Error updating submitted teaching jjjj points.    
  

exception 'PDOException' with message 'SQLSTATE[23000]: Integrity 
constraint violation: 1062 Duplicate entry '25' for key 'PRIMARY'' in 
/home2/shanegib/public_html/artgibney/admin/teachingpoints/index.php:280
Stack trace:
#0 
/home2/shanegib/public_html/artgibney/admin/teachingpoints/index.php(280):
 PDOStatement->execute()
#1 {main}

I think I just want all fields not to be unique except the id and to set that as a primary key. But how do I do that? The year in both rows of the database is 2012 and the database doesn’t seem to like these being the same.
Thanks,
Shane

4

To remove the UNIQUE constraint on the field ‘year’, I tried,

ALTER TABLE teachingpts
DROP INDEX year

but get an error,

#1091 - Can't DROP 'year'; check that column/key exists

But this is how the w3cschool says to do it in their tutorial.

Thanks

5

I tried,

SHOW CREATE TABLE teachingpts

and get,

514×521 40.4 KB

Thanks,
Shane

6

You want to run this query as well:

ALTER TABLE teachingpts
DROP INDEX question,
DROP INDEX question_2,
DROP INDEX question_3;

Note. this will remove the index on the “question” column, if you want to have an index on the column just add it afterwards with:

ALTER TABLE teachingpts
ADD INDEX question (question);

Also just to clarify, when you insert records, you do not insert a value to the “id” column correct?

1 Like
7

Hi,
I finally figured out what I was doing wrong. I was trying update the id instead of using it to tell the mysql which row to update.

My index.php file is now,

try
  {
    $sql = 'UPDATE teachingpts SET
        unit = :unit,
        year = :year,
        exam = :exam,
        question = :question,
        part = :part,
        subpart = :subpart,
        currno = :currno,
        topic = :topic,
        subtopic = :subtopic,
        tp = :tp,
        questiontype = :questiontype
        WHERE id = :id';
    $s = $pdo->prepare($sql);
    $s->bindParam(':id', $_POST['id']);
    $s->bindParam(':unit', $_POST['unit']);
    $s->bindParam(':year', $_POST['year']);
    $s->bindParam(':exam', $_POST['exam']);
    $s->bindParam(':question', $_POST['question']);
    $s->bindParam(':part', $_POST['part']);
    $s->bindParam(':subpart', $_POST['subpart']);
    $s->bindParam(':currno', $_POST['currno']);
    $s->bindParam(':topic', $_POST['topic']);
    $s->bindParam(':subtopic', $_POST['subtopic']);
    $s->bindParam(':tp', $_POST['tp']);
    $s->bindParam(':questiontype', $_POST['questiontype']);
    $s->execute();
  }
  catch (PDOException $e)
  {
    $error = 'Error updating submitted teaching points....';
    include 'error.html.php';
    echo $e;
    exit();

}

Previously and incorrectly it was like this,

try
  {
    $sql = 'UPDATE teachingpts SET
        id =: id, ------ this is incorrect 
        unit = :unit,
        year = :year,
        exam = :exam,
        question = :question,
        part = :part,
        subpart = :subpart,
        currno = :currno,
        topic = :topic,
        subtopic = :subtopic,
        tp = :tp,
        questiontype = :questiontype';  ----needs  WHERE id = :id';
    $s = $pdo->prepare($sql);
    $s->bindParam(':id', $_POST['id']);
    $s->bindParam(':unit', $_POST['unit']);
    $s->bindParam(':year', $_POST['year']);
    $s->bindParam(':exam', $_POST['exam']);
    $s->bindParam(':question', $_POST['question']);
    $s->bindParam(':part', $_POST['part']);
    $s->bindParam(':subpart', $_POST['subpart']);
    $s->bindParam(':currno', $_POST['currno']);
    $s->bindParam(':topic', $_POST['topic']);
    $s->bindParam(':subtopic', $_POST['subtopic']);
    $s->bindParam(':tp', $_POST['tp']);
    $s->bindParam(':questiontype', $_POST['questiontype']);
    $s->execute();
  }
  catch (PDOException $e)
  {
    $error = 'Error updating submitted teaching points....';
    include 'error.html.php';
    echo $e;
    exit();

}

Thanks,
Shane

1 Like
8

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.