CODEWITHSUNDEEP
mysqlselect
Rumesh Eranga Hapuarachchi
Rumesh Eranga Hapuarachchi

Reputation: 2576

MySql : Select statement inside Check statement

I have to create a table as following

Borrower(customerNo,LoanNo)

customers can take loans if they havent take more than 3 loans.

I created table as follows

create table borrower(
customerno int(5),
LoanNo int(5),
primary key(customerno,loanno),
check( customerno not in 
(select customerno from borrower group by customerno having count(loanno)>=4))
);

But it givs me an error saying

[root@localhost:3306] ERROR 1146: Table 'test.borrower' doesn't exist

Can someone tell me how to fix this error??

Upvotes: 5

Views: 2840

Answers (2)

Bohemian
Bohemian

Reputation: 425083

The reason it's giving the error is because the CHECK constraint refers to the table being created, but it doesn't exist at the time that the statement is parsed.

But I have some bad news for you... mysql ignores CHECK constraints. It is allowed as syntax only for compatibility with create statements from other databases.

See the mysql documentation for CREATE TABLE:

The CHECK clause is parsed but ignored by all storage engines.

You'll have to use a trigger, but note that you can't throw an exception from one. The best you can hope for is when you detect a problem, do something like execute SELECT * FROM TOO_MANY_LOANS and hope that the caller figures out what the error "No such table TOO_MANY_LOANS" really means.

Upvotes: 4

Sir Rufo
Sir Rufo

Reputation: 19106

As this belongs to the business rules and not to data structure you should use a Stored Procedure like this

DELIMITER ;;
CREATE PROCEDURE `AddCustomerLoan`(IN Acustomerno int(5), IN ALoanNo int(5), OUT ResultCode INT)
BEGIN

SELECT COUNT(*)
INTO @LoanCount
FROM borrower
WHERE customerno = Acustomerno;

IF @LoanCount < 4 THEN

    INSERT INTO borrower ( customerno, LoanNo )
    VALUES ( Acustomerno, ALoanNo );

    SET ResultCode = 0;

ELSE

    -- Too many Entries
    SET ResultCode = 1;

END IF;

END;;
DELIMITER ;

The ResultCode will inform your application if it was successful or not, and why not successful.

Another advantage is that you can modify the maximum entries or get the maximum entries per customer, without changing your application code.

Upvotes: 2

Related Questions