mysql no primary key

i have this code here

drop table if exists Payments;

create table Payments
(
    customer_email VARCHAR(50) NOT NULL,
    amount DOUBLE,
    payment_开发者_如何学Pythontype ENUM('Visa','Mastercard', 'Cash'),

    PRIMARY KEY (customer_email),
    FOREIGN KEY (customer_email) references customer(email)

);

now each time i enter a customer's payment buy entering his email and the amount. the prob is that each time i enter the same email i get a primary key error (can't duplicate the primary key)

the primary key here refers to a table that consists of the personal details of the customer.

any ideas?

---

The primary key must be unique -- if each customer is going to have multiple payments, you cannot designate the customer_email as a primary key. Consider adding a separate id column and making that the primary key instead.

---

I'd normalise your design and use a stored procedure to insert payments as follows:

Full script here : http://pastie.org/1688269

Hope this helps :)

Example calls

call insert_payment('foo@bar.com',1,100);

call insert_payment('bar@foo.com',2,200);

call insert_payment('pants@elis.com',3,300);

call insert_payment('another@customer.com',1,400);

call insert_payment('another@customer.com',2,500);

mysql> select * from payments_view order by pay_id desc;
+--------+---------------------+-------------+---------------+--------+---------+----------------------+
| pay_id | pay_date            | pay_type_id | pay_type_name | amount | cust_id| email                |
+--------+---------------------+-------------+---------------+--------+---------+----------------------+
|      5 | 2011-03-19 01:34:28 |           2 | mastercard    | 500.00 |       4| another@customer.com |
|      4 | 2011-03-19 01:34:28 |           1 | visa          | 400.00 |       4| another@customer.com |
|      3 | 2011-03-19 01:34:28 |           3 | cash          | 300.00 |       3| pants@elis.com       |
|      2 | 2011-03-19 01:34:28 |           2 | mastercard    | 200.00 |       2| bar@foo.com          |
|      1 | 2011-03-19 01:34:28 |           1 | visa          | 100.00 |       1| foo@bar.com          |
+--------+---------------------+-------------+---------------+--------+---------+----------------------+
5 rows in set (0.00 sec)

Stored procedure

The stored procedure first checks to see if a customer account already exists, if not it creates one then inserts the payment data.

delimiter ;

drop procedure if exists insert_payment;

delimiter #

create procedure insert_payment
(
in p_email varchar(512),
in p_pay_type_id tinyint unsigned,
in p_amount decimal(10,2)
)
begin

declare v_cust_id int unsigned default 0;

  if not exists (select 1 from customers where email = p_email) then
    insert into customers (email) values (p_email);
    set v_cust_id = last_insert_id();
  else
    select cust_id into v_cust_id from customers where email = p_email;
  end if;

  insert into payments (cust_id, pay_type_id, amount) 
   values (v_cust_id, p_pay_type_id, p_amount);

  select last_insert_id() as new_pay_id;

end#

Tables, views and triggers

drop table if exists payments;
drop table if exists payment_types;
drop table if exists customers;

create table payment_types
(
pay_type_id tinyint unsigned not null auto_increment primary key,
name varchar(255) unique not null
)
engine=innodb;

create table customers
(
cust_id int unsigned not null auto_increment primary key,
email varchar(512) unique not null,
total_amount_paid decimal(10,2) not null default 0
)
engine=innodb;

create table payments
(
pay_id int unsigned not null auto_increment primary key,
cust_id int unsigned not null,
pay_type_id tinyint unsigned not null,
pay_date datetime not null,
amount decimal(10,2) not null default 0,
key (pay_date),
foreign key (cust_id) references customers(cust_id),
foreign key (pay_type_id) references payment_types(pay_type_id)
)
engine=innodb;

drop view if exists payments_view;
create view payments_view as 
select
 p.pay_id,
 p.pay_date,
 p.pay_type_id,
 pt.name as pay_type_name,
 p.amount,
 c.cust_id, 
 c.email
from
 customers c
inner join payments p on c.cust_id = p.cust_id
inner join payment_types pt on p.pay_type_id = pt.pay_type_id;

delimiter #

create trigger payments_before_ins_trig before insert on payments
for each row
begin
 set new.pay_date = now();

 update customers set total_amount_paid = total_amount_paid + new.amount
  where cust_id = new.cust_id;
end#

delimiter ;

Testing

insert into payment_types (name) values ('visa'),('mastercard'),('cash');

insert into customers (email) values ('foo@bar.com'),('bar@foo.com'),('pants@elis.com');

call insert_payment('foo@bar.com',1,100);
call insert_payment('bar@foo.com',2,200);
call insert_payment('pants@elis.com',3,300);
call insert_payment('another@customer.com',1,400);
call insert_payment('another@customer.com',2,500);

select * from payment_types order by pay_type_id;
select * from customers order by cust_id;
select * from payments order by pay_id;
select * from payments_view order by pay_id desc;

---

A primary key value can only exist once in the column. To support the value existing more than once, you either:

• can't put a primary key (or unique for that matter) constraint on the column
• use more than one column as the primary key (called a composite key)

I would solve your issue by adding the date that someone make a payment:

CREATE TABLE Payments (
  customer_email VARCHAR(50) NOT NULL,
  payment_date DATETIME,
  amount DOUBLE,
  payment_type ENUM('Visa','Mastercard', 'Cash'),
  PRIMARY KEY (customer_email, payment_date),
  FOREIGN KEY (customer_email) references customer(email)
);

A date makes sense, because it's what someone is likely to need/use for reporting. Because of the date & time being stored, it's very unlikely you'll have duplicates of identical date values (which would result in an error, like the one you've already encountered). It's also easy to populate the date value in an INSERT statement, using either NOW() or the ANSI standard CURRENT_TIMESTAMP... or you could define a DEFAULT constraint for the column to automatically use the current date when data is inserted.