0

trying to make a database for teams in a tv show here. but when I try and insert data into tblShowteam the following error made its appearance.

Msg 2627, Level 14, State 1, Line 2
Violation of PRIMARY KEY constraint 'PK__tblShowt__F693078C03317E3D'. Cannot insert duplicate key in object 'dbo.tblShowteam'.

tables

-- tabbellen aanmaken
create table tblShow(
setId       int,
Datum       date,
teams       int
primary key (setId));

create table tblShowteam(
SetId           int,
datum           date,
teams           int,
primary key (teams));


create table tblTeam(
TeamId      int,
Coach       varchar(35),
CoachId     int,
teams       int
primary key (CoachId));


-- participant table
create table tblDeelnemer(
DeelnemerId     int identity(1, 1),
DeelnemerV      varchar(35),
deelnemerT_V    varchar(10),
DeelnemerA      varchar(35),
CoachId         int,
datum_optreden  date
primary key (DeelnemerId));


--table for the public viewers
create table tblKijker(
Kijkerv         varchar(35),
KijkerT_V       varchar(10),
KijkerA         varchar(35),
Stoelnummer     int identity(1,3),
ShowId          int Not null,
Email           varchar(35)
primary key (Email));

my inserts would look like this:

insert into tblShowteam values (1, '2014-06-28', 1)
insert into tblShowteam values (2, '2014-06-05', 1)
insert into tblShowteam values (3, '2014-06-12', 1)
insert into tblShowteam values (4, '2014-06-19', 1)
insert into tblShowteam values (5, '2014-06-26', 1)
all other inserts (in diffrent tables) work like normal.

what am i doing wrong here?

2
  • 3
    Why on earth are you adding the tbl suffix in all tables? Jun 22, 2014 at 13:22
  • 1
    On the question, what is the ShowTeam table about? Is it a junction (intersection, many-to-many) table between tables Show and Team? Jun 22, 2014 at 13:24

2 Answers 2

1

tblShowteam has primary key on teams column (

create table tblShowteam( SetId int, datum date, teams int, primary key (teams));

You are inserting rows with the same primary key (1) multiple time :

insert into tblShowteam values (1, '2014-06-28', 1);  
--the same as  "insert into tblShowteam (SetId,datum,teams) VALUES (...,1);
insert into tblShowteam values (2, '2014-06-05', 1) ; --it's gonna fail , record with 
-- teams = 1 already exists

I guess setId was supposed to be primary key, not teams.

0

In create table you have set teams as primary key, and also you are aware that primary key does not allow duplicate values.

mysql> create table tblShowteam(
        -> SetId           int,
        -> datum           date,
        -> teams           int,
        -> primary key (teams));
    Query OK, 0 rows affected (0.13 sec)

Check the insert query for teams field you have mentioned values as 1 and if you insert the same value for second record it will behave as below.

   mysql> insert into tblShowteam values (1, '2014-06-28', 1);
    Query OK, 1 row affected (0.03 sec)

    mysql> insert into tblShowteam values (2, '2014-06-05', 1);
    ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

Please alter and keep SetId as primary key.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.