Need help about sql

Question

CREATE TABLE IF NOT EXISTS `customer` (
`UserNo` int(3) NOT NULL AUTO_INCREMENT,
`UserID` int(4) NOT NULL,
`Username` text NOT NULL,
`password` int(6) NOT NULL,
`AccountNo` int(10) NOT NULL,
`FirstName` text NOT NULL,
`LastName` text NOT NULL,
`DateOfBirth` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`Gender` text,
`Tel` int(10),
`Account_type_No` int(2) NOT NULL,
`Address` text NOT NULL,
PRIMARY KEY (`UserNo`,`AccountNo`)
);

CREATE TABLE IF NOT EXISTS `account_type` (
`Account_type_No` int(2) NOT NULL AUTO_INCREMENT,
`Account_type_Name` text NOT NULL,
PRIMARY KEY (`Account_type_No`)
);

CREATE TABLE IF NOT EXISTS `seller` (
`Seller_No` int(4) NOT NULL,
`Seller_Name` text NOT NULL,
PRIMARY KEY (`Seller_Name`(254))
);

CREATE TABLE IF NOT EXISTS `product_and_service` (
`Product_and_service_No` int(6) NOT NULL AUTO_INCREMENT,
`Product_and_service_Name` text NOT NULL,
`Seller_Name` text NOT NULL,
FOREIGN KEY (Seller_Name) REFERENCES seller(Seller_Name),
PRIMARY KEY (`Product_and_service_No`)
);


CREATE TABLE IF NOT EXISTS `customer_purchase` (
`Purchase_No` int(6) NOT NULL AUTO_INCREMENT,
`User_No` int(10) NOT NULL,
`Product_and_service_No` int(6) NOT NULL,
FOREIGN KEY (User_No) REFERENCES customer(User_No),
PRIMARY KEY (`Purchase_No`)
);

CREATE TABLE IF NOT EXISTS `balance` (
`AccountNo` int(10) NOT NULL,
`Current_balance` int(10) NOT NULL,
`Date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
FOREIGN KEY (AccountNo) REFERENCES customer(AccountNo),
FOREIGN KEY (Product_and_service_No) REFERENCES product_and_service(Product_and_service_No)
);

CREATE TABLE IF NOT EXISTS `transaction` (
`Transaction_No` int(8) NOT NULL AUTO_INCREMENT,
`AccountNo` int(10) NOT NULL,
`Purchase_No` int(6) NOT NULL,
`Total` int(10) NOT NULL,
FOREIGN KEY (Purchase_No) REFERENCES customer(Purchase_No),
FOREIGN KEY (AccountNo) REFERENCES customer(AccountNo),
PRIMARY KEY (`Transaction_No`)
);

So, my problem are

MariaDB [onlinebankingsystem]> CREATE TABLE IF NOT EXISTS `product_and_service` (
    -> `Product_and_service_No` int(6) NOT NULL AUTO_INCREMENT,
    -> `Product_and_service_Name` text NOT NULL,
    -> `Seller_Name` text NOT NULL,
    -> FOREIGN KEY (Seller_Name) REFERENCES seller(Seller_Name),
    -> PRIMARY KEY (`Product_and_service_No`)
    -> );
ERROR 1170 (42000): BLOB/TEXT column 'Seller_Name' used in key specification without a key length
MariaDB [onlinebankingsystem]>
MariaDB [onlinebankingsystem]>
MariaDB [onlinebankingsystem]> CREATE TABLE IF NOT EXISTS `customer_purchase` (
    -> `Purchase_No` int(6) NOT NULL AUTO_INCREMENT,
    -> `User_No` int(10) NOT NULL,
    -> `Product_and_service_No` int(6) NOT NULL,
    -> FOREIGN KEY (User_No) REFERENCES customer(User_No),
    -> PRIMARY KEY (`Purchase_No`)
    -> );
ERROR 1005 (HY000): Can't create table `onlinebankingsystem`.`customer_purchase` (errno: 150 "Foreign key constraint is incorrectly formed")
MariaDB [onlinebankingsystem]>
MariaDB [onlinebankingsystem]> CREATE TABLE IF NOT EXISTS `balance` (
    -> `AccountNo` int(10) NOT NULL,
    -> `Current_balance` int(10) NOT NULL,
    -> `Date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
    -> FOREIGN KEY (AccountNo) REFERENCES customer(AccountNo),
    -> FOREIGN KEY (Product_and_service_No) REFERENCES product_and_service(Product_and_service_No)
    -> );
ERROR 1072 (42000): Key column 'Product_and_service_No' doesn't exist in table
MariaDB [onlinebankingsystem]>
MariaDB [onlinebankingsystem]> CREATE TABLE IF NOT EXISTS `transaction` (
    -> `Transaction_No` int(8) NOT NULL AUTO_INCREMENT,
    -> `AccountNo` int(10) NOT NULL,
    -> `Purchase_No` int(6) NOT NULL,
    -> `Total` int(10) NOT NULL,
    -> FOREIGN KEY (Purchase_No) REFERENCES customer(Purchase_No),
    -> FOREIGN KEY (AccountNo) REFERENCES customer(AccountNo),
    -> PRIMARY KEY (`Transaction_No`)
    -> );
ERROR 1005 (HY000): Can't create table `onlinebankingsystem`.`transaction` (errno: 150 "Foreign key constraint is incorrectly formed")

How can I solve this problem?

See : http://stackoverflow.com/questions/1827063/mysql-error-key-specification-without-a-key-length — PaulF, Oct 26 '15 at 12:28
Why seller_name as pk? Seller_No seems like a more reasonable choice. — jarlh, Oct 26 '15 at 12:53
@EdwinJarosiński sry that I make it hard to read, this is first time that I ask on this site and thank you for edit my questions. — Pisit Koolplukpol, Oct 26 '15 at 13:09
@jarlh Because my prof. told me at the first progress that customer should select seller_name to see what service or product that seller have, but may be I confused will my complex form. — Pisit Koolplukpol, Oct 26 '15 at 13:09
Please google the error messages. You can add 'stackoverflow' to your search terms. You should not post here until you have done that. If you don't understand an error, then ask about that one, saying what you do't and don't understand about it. Also only ever ask one question. One try to compile that gets one blob of text with many error messages is not one question. PS That output of errors isn't your problem. Something about it is your problem. Eg you don't understand any of the errors. Please be clear and explicit. — philipxy, Oct 29 '15 at 00:28
Customer PK (`UserNo`,`AccountNo`) says a (`UserNo`,`AccountNo`) pair is unique; and so presumably identifies a customer, and that there can be copies of a`userNo`s and of an`AccountNo`. Almost surely this *not* what you want. You probably want 1. PK (`UserNo`) 2. If two customers can't share an account then UNIQUE NOT NULL (`AccountNo`). 3. If a customer can have more than one account then `AccountNo` use an Account table. — philipxy, Oct 29 '15 at 00:53
To *fix* error messages you need to fix code. To fix code it needs to *fit your application*: What independent statements do you want to make about your application? (Give the *meaning* of each table in terms of its columns.) Are there limitations on situtations, ie on what the corresponding table or sets of tables can contain? (Give *constraints*.) See the "Educate yourself about database design" section of [this answer](http://stackoverflow.com/a/31397135/3404097) — philipxy, Oct 29 '15 at 01:07

score 1 · Accepted Answer · answered Nov 03 '15 at 03:50

You cannot index a TEXT field. Does Seller_Name need to be bigger than VARCHAR(255)? If not, then use that datatype instead. Change any other TEXT columns that don't need to be really big.

That will probably fix the first error. The rest of the errors have cascaded from that.

Meanwhile, don't use INT(6); the (6) does not mean anything. If you are suggesting that it has some maximum size, look into using MEDIUMINT UNSIGNED.

Need help about sql

1 Answers1