How i prevent duplicate row by date

Question

I Have 3 column

stu_id

sub_code

lecture_date

What I want is that the id and code columns cannot be repeated. I am trying but it only allows me to select a primary key.

CREATE TABLE example(
   stu_id INT PRIMARY KEY,
   sub_code VARCHAR(3) PRIMARY KEY,
   lecture_date DATE

i have put the solution to your problem now the id and the code adapt it depending on your data types — Javier G.Raya, Apr 02 '22 at 09:26
By definition, a table can only have one primary key, but you can use the `UNIQUE KEY` constraint over the columns you want to be unique. — Félix Adriyel Gagnon-Grenier, Apr 11 '22 at 16:01
actually my friend if the primary key is not put next to the data but at the bottom as primary key(column1,column2) it catches it perfectly I do it this way in fact. If not, check it out on the next page @FélixAdriyelGagnon-Grenier : https://www.db-fiddle.com/f/3PnzHErrf2fZFGZY67K12X/148 — Javier G.Raya, Apr 11 '22 at 16:14
the fact that two fields are primary keys is known as a composite primary key. For more information see this page that explains it @FélixAdriyelGagnon-Grenier : https://www.javatpoint.com/mysql-composite-key#:~:text=A%20composite%20key%20in%20MySQL,only%20when%20they%20are%20combined. — Javier G.Raya, Apr 11 '22 at 16:18
@FélixAdriyelGagnon-Grenier check : https://stackoverflow.com/a/1110364/12464880 — Javier G.Raya, Apr 11 '22 at 16:21
@Khaled Do you want the unicity to be over two columns, eg there can't be any repeating sub_code for the same stu_id, or by column, eg there can be only one sub_code in the whole table? In the comments on the answer you say you want to prevent duplicates in *the same day*? but you've put the indexes over id and code? which one is it? — Félix Adriyel Gagnon-Grenier, Apr 12 '22 at 13:32

Javier G.Raya · Answer 1 · 2022-04-12T16:18:21.117

1

If you want both fields to be unique, you can make the first one as primary key and the second one unique.

The UNIQUE constraint ensures that all values in a column are different.

Example :

CREATE TABLE examp (
   id INT PRIMARY KEY,
   cod VARCHAR(3) UNIQUE,
   fecha DATE
);

Then you do the insert

INSERT into youTable values(youfirtcolumn,yousecondcolumn,youthirdcolumn);

Example :


INSERT INTO youTable VALUES(1,'A1',NOW()),
                          (2,'A2','2022-04-03');

GOOD EXAMPLE : THIS WORKS

FAIL EXAMPLE : THIS WORKS

edited Apr 12 '22 at 16:18

answered Apr 02 '22 at 09:23

Javier G.Raya

bro but that is not my problem , i mean main concept of prevent duplicate values in same day in any case – Khaled Salah Apr 02 '22 at 09:30
no i mean , when i insert 1, 'A1',curdate if i try to insert same values again restrict that and when i insert 1,'A2',curdate its ok – Khaled Salah Apr 02 '22 at 09:39
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/243540/discussion-between-khaled-salah-and-javier-g-raya). – Khaled Salah Apr 02 '22 at 09:51
thx bro , i talked about didnt allow to add 2 p k in same table bcz i try from structure not by query – Khaled Salah Apr 02 '22 at 09:56

1 Answers1