CODEWITHSUNDEEP
sqlinsert-update
Marcelo Fabian
Marcelo Fabian

Reputation: 39

SQL How to not insert duplicated values

I'm trying to create a procedure that inserts data into a table of registers but i don't want to repeat the second parameter, this is the table

CREATE TABLE Inscription
(
    idClass INT references tb_class,
    idStudent INT references tb_student,
)

The idea is that a student (idStudent) can register in various classes but not in the same class (idClass), I tried to add a unique constraint in the idStudent column but that only allows a student to register in one single class.

Upvotes: 0

Views: 47

Answers (3)

SoManyGoblins
SoManyGoblins

Reputation: 5927

Your unique key needs to encompass both idClass and idStudent, so any particular combination cannot repeat itself.

Upvotes: 0

GMB
GMB

Reputation: 222462

You are looking to create a constraint on your table that includes both columns idClass and idStudent.

Once that constraint is created, an attempt to insert duplicate class/student will result in an error being raised.

As your table does not seem to include a primary key, you would better make that constraint your primary key.

NB : you did not tell which RDBMS you are using hence cannot give you the exact syntax to use...

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269743

I always suggest that all tables have a numeric primary key. In addition, your foreign key references are not correct. And what you want to do is add a unique constraint.

The exact syntax depends on the database. The following is for SQL Server:

CREATE TABLE Inscriptions (
    idInscription int identity(1, 1) primary key
    idClass int references tb_classes(idClass),
    idStudent int references tb_students(idStudnt)
    unique (idClass, idStudent)
);

Notice that I name the tables as the plural of the entity, but the id using the singular.

The Inscriptions table probably wants other columns as well, such as the date/time of the inscription, the method, and other related information.

Upvotes: 1

Related Questions