Untitled

mail@pastecode.io avatar
unknown
plain_text
3 years ago
1.9 kB
3
Indexable
Never
drop table if exists stores cascade;
 CREATE TABLE stores(
	"id" INT NOT NULL PRIMARY KEY,
	storename VARCHAR(50) NOT NULL
 );
CREATE TABLE product(
	"id" INT NOT NULL PRIMARY KEY,
	productname VARCHAR(50) NOT NULL,
	productdescription VARCHAR(200) NOT NULL,
	productprice DOUBLE PRECISION  NOT NULL,
	store_id INT NOT NULL, 
	
	CONSTRAINT fk_store 
	FOREIGN KEY (store_id)
	references stores("id")
);
CREATE TABLE users(
"id" INT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
username VARCHAR(30) NOT NULL,
"password" VARCHAR(30) NOT NULL
);
create table orders(
	"id" INT NOT NULL PRIMARY KEY,
	product_id INT NOT NULL,
	totalprice INT,
	
	CONSTRAINT fk_product 
	FOREIGN KEY(product_id) 
		references product("id")
);
CREATE TABLE roles (
  role_id int NOT NULL,
  name varchar(45) NOT NULL,
  PRIMARY KEY (role_id)
);

 drop table if exists privilege CASCADE;

CREATE TABLE privilege (
  privilege_id int NOT NULL,
  name varchar(45) NOT NULL,
  PRIMARY KEY (privilege_id)
);
drop table if exists roles_privileges;

CREATE TABLE roles_privileges(
	privilege_id int NOT NULL,
	role_id int NOT NULL,
	CONSTRAINT fk_privilege
	FOREIGN KEY(privilege_id) 
	  REFERENCES privilege(privilege_id),
	CONSTRAINT fk_role
	FOREIGN KEY(role_id) 
	  REFERENCES roles(role_id)
);
drop table if exists users_roles;

CREATE TABLE users_roles(
	role_id int NOT NULL,
	user_id int NOT NULL,
	CONSTRAINT fk_role
	FOREIGN KEY(role_id) 
	  REFERENCES roles(role_id),
	constraint fk_user 
	FOREIGN KEY(user_id) 
		references users(id)
);

CREATE SEQUENCE hibernate_sequence START 1 INCREMENT 1;

alter table stores add user_id INT NOT NULL;
alter table stores add constraint fk_user foreign key (user_id) references users(id);
alter table product add quantity int not null;
select * from stores;