Re: duplicates - Mailing list pgsql-admin

----- Original Message -----
From: "Tsirkin Evgeny" <tsurkin@mail.jct.ac.il>
To: "Andrei Bintintan" <klodoma@ar-sd.net>
Cc: <pgsql-admin@postgresql.org>
Sent: Monday, September 06, 2004 10:57 AM
Subject: Re: [ADMIN] duplicates


> On Mon, 6 Sep 2004, Andrei Bintintan wrote:
>
> > If still got problems, please post some queries, be more specific.
> >
> > Best regards,
> > Andy.
> >
> Ok i will try:
> CREATE TABLE schedule (
>        studentid    decimal(9),
>        groupid      decimal(10),
>        maslulsignid decimal(7),
>        tfusot        varchar(29)
> );

Use something like this:

 CREATE TABLE schedule (
    id serial PRIMARY KEY,
    studentid    decimal(9),
    groupid      decimal(10),
    maslulsignid decimal(7),
    tfusot        varchar(29)
 );

Now, ALWAYS use a ID for a table. This id will be always uniqe(because it's
primary key). You don't have to insert this field when you're making an
insert, the server does it automatically. And when you delete you refere
this key and not the student ID.

For ex: you have student id = 1111 in 3 groups
id studentid groupid maslulsignid
1    1111        22        some val
2    1111        33        some val
3    1111        44        some val

If you delete: "Delete from table where studentid=1111" it deletes all the
fields from table. If you want to delete only field 2 then you delete:
delete from table where id=2.

Normally in this kind of tables you should never have dupplicates I mean, in
tables you should not have dupplicates(all fields the samein your case
(studentid    groupid    maslulsignid  tfusot) the same val  - this means
structure conception error).

Hope this helps.

Best regards.

>
> that is the table for writing down the courses/groups
> a students takes.note there is NO unique constrain here
> (maybe it should be but that the way it is and probably
> can't can't be changed).while changing groups for a student
> the applic. deletes all the groups the student had :
> delete from schedule where studentid=11111;
> and then inserts the new groups (regular inserts).
> Now sometimes we got duplicates of the same groupid and
> studentid in the table(everything is same).i thought that \
> maybe the delete do not delete but the insert succeed?
>
> Note :I know that there could be created the a unique key and
> a constrain but befor we have to understand why the duplicates
> were created.
>
> --
> Evgeny.