MySql multi-table constraint

Question

A simple equity database has 3 tables: a ticker table, an exchange table, and a currency table. The ticker table has two columns: a symbol column (ex: IBM), and a foreign key to a row in the exchange table. The exchange table has two columns: a name column (ex: NYSE), and a foreign key to a row in the currency table. The currency table has one column: a symbol (ex: USD). (I've left out the primary keys column for each table).

How do I enforce a constraint that there should not be the same ticker symbol twice for the same currency? i.e., I only want to allow one (IBM + USD). It is not enough to create a unique constraint on (ticker.symbol + ticker.exchange); (IBM + NASDAQ) is invalid if there is already (IBM + NYSE). I thought I could create a view that joined the ticker table and the currency table and create a unique index on (view.ticker + view.currency); however, as far as I can now tell, one cannot create an index on a view.

I'm not sure this constraint is a great idea: a company can actually be listed on two exchanges in the same currency - especially in Europe, where a company might be listed on exchanges in two countries, both in Euros. — eggyal
– eggyal, Commented Apr 23, 2012 at 20:44
That's fine -- I didn't want to overwhelm everyone with details, but the field is actually 'primary exchange'. There can be many secondary exchanges for each row in ticker table. However, the constraints of not having multiple rows with the same (ticker + currency) still seems reasonable. Thanks for giving this some thought though. — Jimmy
– Jimmy, Commented Apr 24, 2012 at 1:20

eggyal · Accepted Answer · 2012-04-23 20:47:40Z

1

I don't think it's possible to specify this constraint in MySQL, but you could use triggers to check that such combinations don't already exist (raising an error if they do); note however that you'd need to have triggers before insert on the ticker table and before update on all three tables.

answered Apr 23, 2012 at 20:47

eggyal

127k18 gold badges220 silver badges244 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySql multi-table constraint

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related