CODEWITHSUNDEEP
mysqlsqlconstraints
harryo
harryo

Reputation: 513

Using MySQL CHECK to make sure value in table matches in other table

I want to have one of my tables check to make sure that values entered into one of its fields are correct.

In my case, I have a table called Book, and a table called Sale. Since each sale has the isbn of a book, it should be possible for me to make sure that the price entered into the Sale table matches the price that corresponds to the isbn in the Book table. I'm convinced that I ought to be using the CHECK constraint in SQL, but I do not know how to go about doing this.

Parent Table:

CREATE TABLE Book
(
    isbn INTEGER NOT NULL,
    title VARCHAR(20),
    author VARCHAR(20),
    price DOUBLE,
    PRIMARY KEY (isbn)
);

An INSERT into the Book table:

INSERT INTO Book
VALUES (1, 'A Separate Peace', 'John Knowles' 9.99);

My Child Table:

CREATE TABLE Sale
(
    date DATE,
    saleId INTEGER NOT NULL,
    isbn INTEGER,
    price DOUBLE,
    PRIMARY KEY (saleId),
    FOREIGN KEY (isbn) REFERENCES Book (isbn),
    CHECK (price = (Book (price) WHERE Book (isbn) = isbn))
);

Example INSERT statement for Sale: 

INSERT INTO Sale 
VALUES ('2012-01-01', 1234, 1, 8.99);

Notice that the correct price is 9.99, not 8.99. I want this INSERT to return an error.

Upvotes: 0

Views: 713

Answers (3)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115550

Assuming that prices can change and you want to have the current book prices in Book.price, while the prices that books are sold in Sale.price.

To ensure that the price inserted into table Sale is the current book price (stored in Book), instead of: 

INSERT INTO Sale 
  VALUES ('2012-01-01', 1234, 1, 8.99) ;

you could use something like this:

INSERT INTO Sale 
  SELECT '2012-01-01', 1234, isbn, price
  FROM Book
  WHERE isbn = 1 ;

Upvotes: 1

Kypros
Kypros

Reputation: 2986

Yes @ypercube 's solution will achieve what you want, BUT that goes against best practices and database normalization.

If that was the case then we should also add more columns in the sales table for the name, author etc.

I suggest that you separate the business logic and keep it simple unless you really have to.

Upvotes: 0

Kypros
Kypros

Reputation: 2986

You should implement this kind of business logic in the software part of the system.

  • How about when you would like to offer a discount or when a product is on offers?
  • What if a sale lets say 1 year before for Book1 was 9.99 and a year later the price for the same book changes to 8.99?

If you don't want that, then there is no reason for saving the price twice (in both tables), therefore just remove the price column from Sales table, and use the isbn no when reading from the database to determine the price of the product.

Upvotes: 2

Related Questions