CODEWITHSUNDEEP
mysql
Matthew
Matthew

Reputation: 1

"Cannot add foregin key constraint"

DROP TABLE IF EXISTS CARD_ACCOUNT;
Create Table CARD_ACCOUNT(
acct_no Char(16),
exp_date date,
card_type ENUM('Debit','Credit') NOT NULL,
cust_ID integer NOT NULL
);

DROP TABLE IF EXISTS DEBIT_CARD;
Create Table DEBIT_CARD(
acct_no Char(16),
exp_date date,
bank_no CHAR(9) NOT NULL,
Constraint debit_card_pk primary key(acct_no,exp_date),
Constraint debit_card_fk foreign key(acct_no,exp_date) References card_account(Acct_no,exp_date)
ON UPDATE CASCADE
ON DELETE CASCADE
);

When I try to run this statement I get a "Cannot add foregin key constraint" error in Mysql on the Debit_Card table, why do I get this error the script I am learning from has everything writen the same exact way as I have.

Upvotes: 0

Views: 36

Answers (2)

Jeffry Evan
Jeffry Evan

Reputation: 327

card_account(Acct_no,exp_date) must be Primary Key if you want reference to it in Foreign Key.

and why you don't make it into 1 table?

Create Table CARD_ACCOUNT(
   acct_no Char(16),
   exp_date date,
   bank_no CHAR(9) NOT NULL,
   card_type ENUM('Debit','Credit') NOT NULL,
   cust_ID integer NOT NULL,
   Constraint CARD_ACCOUNT_PK primary key(acct_no,exp_date)
);

i think it serve the same purpose. you already have card_type to know if its debit or credit card so why make separate table for that?

Upvotes: 1

user8406805
user8406805

Reputation:

Use the below code, I have just added one primary key constraint in the first table, it will allow you to create the foreign key. But, as "Tim Biegeleisen" commented there is database design problem, you should think again about your database design.

DROP TABLE IF EXISTS CARD_ACCOUNT;
Create Table CARD_ACCOUNT(
acct_no Char(16),
exp_date date,
card_type ENUM('Debit','Credit') NOT NULL,
cust_ID integer NOT NULL,
Constraint debit_card_pk primary key(acct_no,exp_date)
);

DROP TABLE IF EXISTS DEBIT_CARD;
Create Table DEBIT_CARD(
acct_no Char(16),
exp_date date,
bank_no CHAR(9) NOT NULL,
Constraint debit_card_pk primary key(acct_no,exp_date),
Constraint debit_card_fk foreign key(acct_no,exp_date) References card_account(Acct_no,exp_date)
ON UPDATE CASCADE
ON DELETE CASCADE
);

Upvotes: 0

Related Questions