mysql没有主键

Question

我都会有此代码

drop table if exists Payments;

create table Payments
(
    customer_email VARCHAR(50) NOT NULL,
    amount DOUBLE,
    payment_type ENUM('Visa','Mastercard', 'Cash'),

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

);

现在每次我输入客户的付款购买时输入他的电子邮件和金额时， 。 问题是，每次我输入同一封电子邮件时，我都会收到主键错误（无法复制主键），

这里的主键指的是包含客户个人详细信息的表。

有什么想法吗？

Answer 1

主键必须是唯一的 - 如果每个客户要进行多次付款，则不能将 customer_email 指定为主键。考虑添加一个单独的 id 列并将其作为主键。

Answer 2

我将标准化您的设计并使用存储过程插入付款，如下所示：

此处完整脚本：http://pastie.org/ 1688269

希望这会有所帮助:)

调用示例

call insert_payment('[email protected]',1,100);

call insert_payment('[email protected]',2,200);

call insert_payment('[email protected]',3,300);

call insert_payment('[email protected]',1,400);

call insert_payment('[email protected]',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| [email protected] |
|      4 | 2011-03-19 01:34:28 |           1 | visa          | 400.00 |       4| [email protected] |
|      3 | 2011-03-19 01:34:28 |           3 | cash          | 300.00 |       3| [email protected]       |
|      2 | 2011-03-19 01:34:28 |           2 | mastercard    | 200.00 |       2| [email protected]          |
|      1 | 2011-03-19 01:34:28 |           1 | visa          | 100.00 |       1| [email protected]          |
+--------+---------------------+-------------+---------------+--------+---------+----------------------+
5 rows in set (0.00 sec)

存储过程

存储过程首先检查客户帐户是否已存在，如果不存在，则创建一个帐户，然后插入付款数据。

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#

表、视图和触发器

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 ;

测试

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

insert into customers (email) values ('[email protected]'),('[email protected]'),('[email protected]');

call insert_payment('[email protected]',1,100);
call insert_payment('[email protected]',2,200);
call insert_payment('[email protected]',3,300);
call insert_payment('[email protected]',1,400);
call insert_payment('[email protected]',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;

Answer 3

主键值在列中只能存在一次。要支持多次存在的值，您可以：

• 不能在列上放置主键（或唯一的）约束
• 使用多个列作为主键（称为复合键）

我会解决您的问题通过添加某人付款的日期：

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)
);

日期是有意义的，因为这是某人可能需要/用于报告的日期。由于日期和在存储的时间中，您不太可能有相同日期值的重复项（这会导致错误，就像您已经遇到的那样）。使用 NOW() 或 ANSI 标准 CURRENT_TIMESTAMP 在 INSERT 语句中填充日期值也很容易...或者您可以为列定义 DEFAULT 约束插入数据时自动使用当前日期。