CODEWITHSUNDEEP
mysqlforeign-keys
Kunal Sanjeevkumarsetpal
Kunal Sanjeevkumarsetpal

Reputation: 35

Error 150 : table couldn't be created

Where am I going wrong? I get the error 150 debit_card table couldn't be created. Something to do the foreign keys i know, just don't know what exactly. Help?

create table customer(
cust_id int auto_increment,
first_name varchar(25),
last_name varchar(25),
street varchar(25),
city varchar(25),
state char(2),
zip varchar(10),
CONSTRAINT customer_pk primary key(cust_id)
);

create table card_account(
acct_no char(16),
exp_date date,
card_type ENUM("Debit","Credit") not null,
CONSTRAINT card_account_pk primary key(acct_no,exp_date)
);

create table debit_card(
bank_no char(9) not null,
acct_no char(16),
exp_date date,
CONSTRAINT debit_card_pk primary key(acct_no,exp_date),
CONSTRAINT debit_card_acct_no_fk foreign key(acct_no) references card_account(acct_no) on delete cascade,
CONSTRAINT debit_card_exp_date_fk foreign key(exp_date) references card_account(exp_date) on delete cascade
);

Upvotes: 1

Views: 25

Answers (1)

KaeL
KaeL

Reputation: 3659

Your card_account table has a composite primary key. You cannot use those columns separately in a foreign key constraint.

But you can add a foreign key referencing to the composite primary key:

create table debit_card(
bank_no char(9) not null,
acct_no char(16),
exp_date date,
CONSTRAINT debit_card_pk primary key(acct_no,exp_date),
CONSTRAINT debit_card_acct_no_fk foreign key(acct_no,exp_date) references card_account(acct_no,exp_date) on delete cascade
);

Upvotes: 2

Related Questions