Because I get this error

Question

Because I get this error

Navigation

#1 by (0 votes)

0

ERROR: duplicate key violates uniqueness restriction «matricula_pkey» DETAIL: The key already exists (cod_est, cod_asig) = (1085, 1080). SQL state: 23505

At the time of entering the data of a table this error appears, and my database is formed as follows:

create table Estudiante(
cod_est varchar (11) primary key,
nom_est varchar(35),
apell_est varchar(35),
edad smallint,
estrato smallint,
genero char(1),
fecha_nac date
);

create table matricula(
cod_est varchar(11),
cod_asig varchar(4),
fechainc date default '1-08-2018', 
fechafin date default '30-11-2018',
nota real check(nota>=10 and nota<=50),
primary key (cod_est, cod_asig),
Foreign key (cod_est) REFERENCES Estudiante(cod_est)on delete no action,
foreign key (cod_asig)REFERENCES asignatura(cod_asig)on delete no action
);

create table asignatura(
cod_asig varchar(4) primary key not null,
nom_asig varchar(35),   
nrocred smallint
);

What I'm trying to do is:

insert into matricula (cod_est,cod_asig,fechainc,fechafin,nota)
values 
           ('1085','2030',default,default,10),
           ('1453','1011',default,default,18);

mysql postgresql

asked by David 20.05.2018 в 23:43

source

1 answer

Configure the display of rows in integration service Call to protected method Barryvdh \ DomPDF \ PDF :: render () from context 'App \ Http \ Controllers \ UserController'

score 0 · Accepted Answer

The error you're having is pretty clear:

ERROR: duplicate key violates uniqueness restriction «matricula_pkey» DETAIL: The key already exists (cod_est, cod_asig) = (1085, 1080). SQL state: 23505

In the CREATE TABLE of the table matricula you can see that you have declared a first compound key (a primary key can have one or several columns).

The primary key is a restriction , which prevents duplicate rows in the table whose values are equal in the columns that make up that key.

Therefore, if in the table matricula you declare:

... primary key (cod_est, cod_asig),

you're saying: never accept me two rows with the same values in the columns cod_est and cod_asig

Suppose there is already a row in the table:

cod_est        cod_asig
1085           1080

If you try to insert another row with those two values in those two columns, it will give you the error you are having.

However, you can insert rows like this:

cod_est        cod_asig
1080           1085
1080           1080
1080           1081
1080           1082
1085           1083

That is, you are entitled to only one row for each combination of cod_est/cod_asig .

What do I do then?

You have to decide, in the design of your table, the records that should be unique and based on that, declare it to be a primary key, or an index of uniqueness.

For example:

If for example what should not be repeated is only cod_est , you declare the restriction like this: ... primary key (cod_est), ... ,

In that case, you can have rows like this:

cod_est        cod_asig
1080           1085
1080           1080  --ERROR ya existe un cod_est 1080
1080           1081  --ERROR ya existe un cod_est 1080
1080           1082  --ERROR ya existe un cod_est 1080
1085           1083
1085           1084  --ERROR ya existe un cod_est 1085
1086           1085

On the other hand, if it were cod_asig , you declare the restriction like this: ... primary key (cod_asig), ... .

In that case, you can have rows like this:

cod_est        cod_asig
1080           1085
1080           1080  
1080           1081  
1080           1082  
1085           1083
1085           1084
1086           1085  --ERROR ya existe un cod_asig 1085

In the case of needing a combination of columns, then you declare the restriction as you already have it, but knowing that you will not be able to repeat rows with equal values in the columns that are part of the primary key.

Also , you can use a uniqueness index ( UNIQUE ) that indicates that the table should not have repeated values according to the criteria indicated in the index.

Here is another problem (let's say better dilemma) that may be the subject of an interesting question to ask here if it does not already exist: should I use a combined primary key or a combined single index to restrict duplication? of data?

Everything will depend on the reality of your program and your data. Restrictions are a fundamental element for the operation of the database and if they are not well established, it can cause problems such as data redundancy, slow queries, etc.

I hope at least I have clarified the problem you are having now, that is the intention of the answer ... and to open your appetite to this wonderful world of databases and to the important decision of how to set the restrictions.