Re: DB design and foreign keys - Mailing list pgsql-sql

so, after the needed modifications the SQL schema is the following

-- SQL schema for business-test-db2

CREATE TABLE customers (  customer_code serial UNIQUE,  alfa_customer_code varchar(6),  customer_name character
varying(250)NOT NULL,  address character varying(250) NOT NULL,  city character varying(250) NOT NULL,  zip_code
charactervarying(8) NOT NULL,  prov character varying(30) NOT NULL,  security character varying(15) NOT NULL,  tel
charactervarying(30),  tel2 character varying(20) NOT NULL,  fax character varying(250),  url character varying(250),
email1character varying(250) NOT NULL,  email2 character varying(250) NOT NULL,  discount1 integer,  discount2 integer,
PRIMARY KEY (customer_code)
 
);

CREATE TABLE users  (  id smallint NOT NULL,  login varchar(20) NOT NULL,  pwd varchar(20) NOT NULL,  name varchar(20)
NOTNULL,  customer_code int REFERENCES customers (customer_code),  valid date,  primary key (id)
 
);

CREATE TABLE products   (  code varchar(60) UNIQUE NOT NULL,  description varchar(250) NOT NULL,  dimensions
varchar(250)NOT NULL,  price numeric NOT NULL,  state boolean,  PRIMARY KEY (code)
 
);

CREATE TABLE orders  (  order_code serial UNIQUE NOT NULL,  customer_code integer REFERENCES customers (customer_code)
NOTNULL,  order_date time without time zone NOT NULL,  remote_ip inet NOT NULL,  order_time timestamp with time zone
NOTNULL,  order_type varchar(10) NOT NULL,  state varchar(10) NOT NULL,  PRIMARY KEY (order_code)
 
);

CREATE TABLE order_items (  order_code integer REFERENCES orders (order_code) NOT NULL,  customer_code integer
REFERENCEScustomers (customer_code) NOT NULL,  product_code varchar(60) REFERENCES products (code) NOT NULL,  qty int
NOTNULL,  price numeric NOT NULL,  row_price numeric
 
);

--
-- END OF FILE

the order_items table is:

business-test-db2=# \d order_items            Tabella "public.order_items"   Colonna         |         Tipo
   | Modificatori
 
-------------------+-------------------------+--------------order_code       | integer                      | not
nullcustomer_code| integer                      | not nullproduct_code   | character varying(60) | not nullqty
       | integer                      | not nullprice                | numeric                    | not nullrow_price
    | numeric                    |
 
Vincoli di integrità referenziale   "$1" FOREIGN KEY (order_code) REFERENCES orders(order_code)   "$2" FOREIGN KEY
(customer_code)REFERENCES customers(customer_code)   "$3" FOREIGN KEY (product_code) REFERENCES products(code)
 

business-test-db2=#



thanks a lot, you all pointed me out of misconceptualized position.

best ragards all,
Gianluca Riccardi

p.s.

Colonna = column
Tipo = type
Modificatori = modifiers
Vincoli di integrita' referenziale = referential integrity constraints