0

I have two tables, one called 'persons' the other called 'results'.

persons has an ID, and a playername.

Results has an ID, a playerID, and a score and time.

It seems logical to me that the "playerID" in results should not be unique (since the same player can have multiple scores), though when executing this query:

insert into results (playerid, score, time) values
((select id from persons where playername = 'Dylan'),100,100);

I get the error

ERROR:  duplicate key value violates unique constraint "results_playerid_key"
DETAIL:  Key (playerid)=(1) already exists.

Is this perhaps an error on the database? (The database is managed by several lecturers at my university, and I have no way of changing any settings).

Thanks for any insight you can provide.

Improve this question
2
  • 1
    No it's not an error with the database (unless you mean potentially an error in its design/conception). The column has a unique constraint on it, so you can't insert duplicate values into it. If you don't want that constraint, you'll need to remove it Commented Dec 19, 2013 at 16:45
  • @Clive Yes I'm sorry if my question wasn't clear enough. I am aware that the constraint is on the database, but I thought it could be an error in the design! Thanks! Commented Dec 19, 2013 at 16:50

2 Answers 2

Reset to default
1

Although it may seem logical to you (and indeed to me) maybe it does not sound logical to the people managing the DB, so that they put a constraint on playerid as unique.

Maybe there was a mistake and they wanted to set the id as an autoincrementing primary key? Can you at least investigate the schema to see what constraints are on even if you cannot change them?

It may well be that the idea behind the DB is to really have one result per player, do you know the business or application logic potentially sitting behind this choice?

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thank you for your answer. I had to make sure it wasn't my query that was wrong for some reasons. They have an auto-incrementing ID on another field. You have made a good point about "it does not sound logical to the peope..". So I have decided to send a mail to one of the lecturers, asking if she could elaborate on the logic behind having a unique constraint on the key. Thank you!
0

Have you tried renaming the foreign key in the results table to something that isn't "playerid" (I'd use player_id or player_no)? Also make certain that it's a number in the results table, and not set to a unique value.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.