Advice on MySQL Database

Question

I was given a mySQL Database schema by my lecturer to create a banking app. There are some parts I don't understand.

CREATE SCHEMA Banking;


/* Customer Table */

DROP TABLE IF EXISTS Banking.Customer;

CREATE TABLE Banking.Customer(
    custId  int(11)     AUTO_INCREMENT,
    name        varchar(50)     NOT NULL,
    address     varchar(300) NOT NULL,
    email       varchar(100) NOT NULL,
    phone       int(11) DEFAULT NULL,
    PRIMARY KEY (custId)
)   ENGINE=InnoDB AUTO_INCREMENT=210590 DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS Banking.Account;

CREATE TABLE Banking.Account (
    accountNo int(11) NOT NULL AUTO_INCREMENT,
    cId       int(11) NOT NULL,
    balance   int(11) NOT NULL,
    sortCode  int(11) NOT NULL,
    PRIMARY KEY (accountNo),
    KEY cId (cId),
    KEY sortCode (sortCode),
    CONSTRAINT account_ibfk_1 
    FOREIGN KEY (cid) REFERENCES Banking.CUSTOMER(custId)
)   ENGINE=InnoDB AUTO_INCREMENT=816410 DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS Banking.Transaction;

CREATE TABLE Banking.Transaction (
  _id           int(11)         NOT NULL AUTO_INCREMENT,
  accountNo     int(11)         NOT NULL,
  amount        int(11)         NOT NULL,
  postBalance   int(11)         NOT NULL,
  type          varchar(255)    DEFAULT NULL,
  PRIMARY KEY   (_id),
  KEY accountNo (accountNo),
  CONSTRAINT transaction_ibfk_2 
  FOREIGN KEY (accountNo) REFERENCES Account (accountNo)
) ENGINE=InnoDB AUTO_INCREMENT=229377 DEFAULT CHARSET=utf8;

My QUESTIONS are why is the ENGINE set to InnoDB

What are the constraints transaction_ibfk_2 and CONSTRAINT account_ibfk_1 doing.

Also the WORD Key.

Why are elements called KEY when they are neither a primary/foreign key?

Thanks

score 1 · Accepted Answer · answered Dec 10 '17 at 18:00

why is the ENGINE set to InnoDB

MySQL supports several engines (the part of the DB that actually reads and writes the data), and InnoDB is one that supports database transactions, which are quite a useful thing to have in a banking application (unlike e.g. MyISAM engine, which is often the default engine if you don't explicitly specify one).

What are the constraints transaction_ibfk_2 and CONSTRAINT account_ibfk_1 doing.

They are just names for the foreign keys that follow in the next line, not stand-alone.

Also the word KEY. Why are elements called KEY when they are neither a primary/foreign key?

A plain KEY like that will only create an index, so that searching by that column goes faster in some cases. The same thing can also be created using the synonym INDEX, which is probably more obvious. Other than performance, it doesn't affect things much.

There are also PRIMARY KEY, FOREIGN KEY and UNIQUE KEY; you seem to know the first two, and the latter ensures that each value is present only in one row, which is sometimes a useful constraint to have. See this question for more details.

Advice on MySQL Database

1 Answers1