Hej alle på udvikleren.
Jeg sidder og og prøver at oprette en DB i Mysql, men kan ikke tilføje en foreign key til min table.
Her er hele min code til oprettelse af tabeller.
- /* Create database */
- CREATE DATABASE Payz;
- USE Payz;
-
- /* Create customer table */
- CREATE TABLE users (
- id int NOT NULL AUTO_INCREMENT,
- companyName varchar(300) NOT NULL,
- cvr int NOT NULL,
- legalBusinessName varchar(300) NOT NULL,
- address varchar(250) NOT NULL,
- zipcode int NOT NULL,
- city varchar(250) NOT NULL,
- phone int NOT NULL,
- contactName varchar(250) NOT NULL,
- contactMail varchar(250) NOT NULL,
- contactPhone int NOT NULL,
- bank varchar(250) NOT NULL,
- regNo int NOT NULL,
- accountNo int NOT NULL,
- companyLogo varchar(4000) NOT NULL,
- approved bit NOT NULL DEFAULT FALSE,
- PRIMARY KEY(id),
- KEY idx_cvr(cvr)
- ) CHARACTER SET utf8 ENGINE=InnoDB;
-
- /* Create admin table */
- CREATE TABLE admins (
- id int NOT NULL AUTO_INCREMENT,
- username varchar(250) NOT NULL,
- password varchar(250) NOT NULL,
- email varchar(250) NOT NULL,
- PRIMARY KEY(id),
- KEY idx_email(email)
- ) CHARACTER SET utf8 ENGINE=InnoDB;
-
- /* Create payment table */
- CREATE TABLE payment (
- id INT NOT NULL AUTO_INCREMENT,
- paymentOwner int unsigned NOT NULL,
- referenceNo INT NOT NULL,
- referenceText varchar(4000),
- amountToPay int NOT NULL,
- recipientEmail varchar(250) NOT NULL,
- creationDate date NOT NULL,
- paymentMade bit NOT NULL DEFAULT FALSE,
- paymentMadeOn date NULL,
- PRIMARY KEY(id)
- ) CHARACTER SET utf8 ENGINE=InnoDB;
-
-
- /* Create payment table update event(TRIGGER)*/
- CREATE TRIGGER event_insert BEFORE INSERT ON payment FOR EACH ROW SET NEW.creationDate = NOW();
-
-
- /* Alter payment table, add constraint */
- ALTER TABLE payment ADD
- CONSTRAINT FK_payment
- FOREIGN KEY (paymentOwner) REFERENCES users(cvr)
- ON UPDATE CASCADE
- ON DELETE CASCADE
Det eneste der ikke virker denne del
- /* Alter payment table, add constraint */
- ALTER TABLE payment ADD
- CONSTRAINT FK_payment
- FOREIGN KEY (paymentOwner) REFERENCES users(cvr)
- ON UPDATE CASCADE
- ON DELETE CASCADE
Når jeg prøver at køre det, får jeg følgende fejl.
Error Code: 1005
Can't create table 'payz.#sql-658_5020' (errno: 150)
Jeg har prøvet at kigge på mysql.com(
http://dev.mysql.com/doc/refman/5.0/en/innodb-error-codes.html)Men der er ikke meget hjælp at hente ud over "table creation failed because a foreign key constraint was not correctly formed"
Håber der er nogle her inde som måske kan se hvad jeg gør galt.