Every “INSERT” query check a combination of column's records

Question

Unfortunately, my mind has stacked right now and i would like to design a really simple DB schema.

What i want?

In a simple table, for instance Table1, there are two different columns, Series and Orders.

I would like every new record for Orders be unique for Series.

Series: A Orders: 1,2,3,4,5,6

Series: B Orders: 1,2,3,4,5,6

If the user try to insert Series A and Orders 1 for second time i would like to have an error.

I think that the best way is using stored procedure? Am I right?

Thanks in advance.

Answer 1

It sounds like what you are looking for is a "unique" constraint:

ALTER TABLE Table1 ADD UNIQUE(Series, Orders)

That will cause an error to be raised when you try create a row (by inserting new or updating an existing one) that duplicates an existing combination of Series and Order.

Depending on the rest of the database design, these columns might be a good candidate for a compound primary key which would also enforce uniqueness.

This is fundamentally basic database stuff, I would suggest you grab an introductory book or find a good tutorial online. Keywords that you should understand: primary key, foreign key, check constraint, unique constraint, surrogate key