84 lines
3.1 KiB
Plaintext
84 lines
3.1 KiB
Plaintext
create table product (
|
|
category int not null,
|
|
id int not null,
|
|
price decimal,
|
|
primary key(category, id))
|
|
engine=ndb;
|
|
create table customer (
|
|
id int not null,
|
|
primary key (id))
|
|
engine=ndb;
|
|
create table product_order (
|
|
no int not null auto_increment,
|
|
product_category int not null,
|
|
product_id int not null,
|
|
customer_id int not null,
|
|
primary key(no),
|
|
index (product_category, product_id),
|
|
constraint fk1
|
|
foreign key (product_category, product_id) references product(category, id)
|
|
on update restrict on delete cascade,
|
|
index (customer_id),
|
|
constraint fk2
|
|
foreign key (customer_id) references customer(id))
|
|
engine=ndb;
|
|
show create table product;
|
|
Table Create Table
|
|
product CREATE TABLE `product` (
|
|
`category` int(11) NOT NULL,
|
|
`id` int(11) NOT NULL,
|
|
`price` decimal(10,0) DEFAULT NULL,
|
|
PRIMARY KEY (`category`,`id`)
|
|
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
show create table customer;
|
|
Table Create Table
|
|
customer CREATE TABLE `customer` (
|
|
`id` int(11) NOT NULL,
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
show create table product_order;
|
|
Table Create Table
|
|
product_order CREATE TABLE `product_order` (
|
|
`no` int(11) NOT NULL AUTO_INCREMENT,
|
|
`product_category` int(11) NOT NULL,
|
|
`product_id` int(11) NOT NULL,
|
|
`customer_id` int(11) NOT NULL,
|
|
PRIMARY KEY (`no`),
|
|
KEY `product_category` (`product_category`,`product_id`),
|
|
KEY `customer_id` (`customer_id`),
|
|
CONSTRAINT `fk1` FOREIGN KEY (`product_category`,`product_id`) REFERENCES `product` (`category`,`id`) ON DELETE CASCADE ON UPDATE RESTRICT,
|
|
CONSTRAINT `fk2` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
|
|
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
insert into product values (1,1,5);
|
|
insert into customer value (1);
|
|
insert into product_order value (1,1,1,1);
|
|
drop table product_order, customer, product;
|
|
show create table product;
|
|
Table Create Table
|
|
product CREATE TABLE `product` (
|
|
`category` int(11) NOT NULL,
|
|
`id` int(11) NOT NULL,
|
|
`price` decimal(10,0) DEFAULT NULL,
|
|
PRIMARY KEY (`category`,`id`)
|
|
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
show create table customer;
|
|
Table Create Table
|
|
customer CREATE TABLE `customer` (
|
|
`id` int(11) NOT NULL,
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
show create table product_order;
|
|
Table Create Table
|
|
product_order CREATE TABLE `product_order` (
|
|
`no` int(11) NOT NULL AUTO_INCREMENT,
|
|
`product_category` int(11) NOT NULL,
|
|
`product_id` int(11) NOT NULL,
|
|
`customer_id` int(11) NOT NULL,
|
|
PRIMARY KEY (`no`),
|
|
KEY `product_category` (`product_category`,`product_id`),
|
|
KEY `customer_id` (`customer_id`),
|
|
CONSTRAINT `fk2` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
|
|
CONSTRAINT `fk1` FOREIGN KEY (`product_category`,`product_id`) REFERENCES `product` (`category`,`id`) ON DELETE CASCADE ON UPDATE RESTRICT
|
|
) ENGINE=ndbcluster AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
drop table product_order, customer, product;
|