0

I have a simple database with two tables :

Catalog (id_catalog, name )

Product (id_product, name, quantity, id_catalog# )

None of those fields can be empty, so I put a NOT NULL constraint everywhere.

But I need that two products couldn't have the same name if they are in the same catalog. How could I do that?

I mean

1, chocolat, 5, 1
2, chocolat, 7, 2

those are OK because they are in two different catalogs, but :

1, chocolat, 5, 1
2, chocolat, 7, 1

should not be allowed, because I shouldn't accept chocolat twice in the same catalog.

mustaccio
  • 18,234
  • 16
  • 48
  • 57
Nita
  • 11
  • 3
  • 2
    Oracle supports [multi-column unique constraints](https://stackoverflow.com/questions/17343516/how-to-give-a-unique-constraint-to-a-combination-of-columns-in-oracle) if that's what you need? – Rup Jan 26 '18 at 15:26
  • 1
    This is typically solved bey either creating a constraint (unique) or by creating a unique index. I personally think that the first method is better because more descriptive. – Ronald Jan 26 '18 at 15:27

1 Answers1

4

You can create a unique index to avoid inserting more than one row with the same couple (name, id_catalog#).

Create the table:

SQL> create table Product(id_product number, name varchar2(10), quantity number, id_catalog# number);

Table created.

Add an index :

SQL> create unique index idx1 on Product (name, id_catalog#);

Index created.

This will work:

SQL> insert into Product (id_product, name, quantity, id_catalog#) values (1, 'chocolat', 5, 1);

1 row created.

This will not work:

SQL> insert into Product (id_product, name, quantity, id_catalog#) values (2, 'chocolat', 7, 1);
insert into Product (id_product, name, quantity, id_catalog#) values (2, 'chocolat', 7, 1)
*
ERROR at line 1:
ORA-00001: unique constraint (ALEK.IDX1) violated

But this will, due to different id_catalog#

SQL> insert into Product (id_product, name, quantity, id_catalog#) values (2, 'chocolat', 7, 2);

1 row created.

SQL>
Aleksej
  • 22,443
  • 5
  • 33
  • 38