A continuación, se muestran diferentes ejemplos de creación de tablas y en ellos diferentes formas de definición de claves principales y foraneas.

CREATE SCHEMA ventas;

------------------------------------------------------------------------------------
--
-- Create table tb_client
--
------------------------------------------------------------------------------------


CREATE TABLE ventas.tb_client
(
  client_id char(5) NOT NULL,
  client_name char(40) NOT NULL,
  age integer,
  address char(120),
  city char(25),
  country char(50) NOT NULL,
  contact_email char(100),
  phone char(15),
  created_date date,
  
    -- Define primary key --
  CONSTRAINT         pk_client_id     PRIMARY KEY (client_id)

);



------------------------------------------------------------------------------------
--
-- Create table tb_category
--
------------------------------------------------------------------------------------

CREATE TABLE ventas.tb_category
(
  category_id char(5) NOT NULL,
  category_name char(60) NOT NULL,
  created_date date,
  
  -- Define primary key --
  CONSTRAINT         pk_category_id     PRIMARY KEY (category_id)
);

------------------------------------------------------------------------------------
--
-- Create table tb_product
--
------------------------------------------------------------------------------------

CREATE TABLE ventas.tb_product
(
  product_id char(5) NOT NULL,
  category_id char(5),
  product_name char(60) NOT NULL,
  price  DECIMAL(12,2) NOT NULL,
  created_by_user char(10) NOT NULL DEFAULT 'OS_SYSTEM', 
  created_date date,
  updated_date date
);

-- Define primary key --
ALTER TABLE ventas.tb_product ADD CONSTRAINT pk_product_id PRIMARY KEY (product_id);

-- Define foreign key --
ALTER TABLE ventas.tb_product ADD CONSTRAINT fk_category_id FOREIGN KEY (category_id)
REFERENCES ventas.tb_category(category_id);


------------------------------------------------------------------------------------
--
-- Create table tb_order
--
------------------------------------------------------------------------------------

CREATE TABLE ventas.tb_order
(
  order_id char(10) NOT NULL,
  client_id char(5) NOT NULL,
  product_id char(5) NOT NULL,
  quantity integer NOT NULL,
  total DECIMAL(8,2),
  created_date date
);

-- Define primary key --
ALTER TABLE ventas.tb_order ADD CONSTRAINT pk_order_id PRIMARY KEY (order_id);

-- Define foreign key --
ALTER TABLE ventas.tb_order ADD CONSTRAINT fk_client_id FOREIGN KEY (client_id)
REFERENCES ventas.tb_client(client_id);

ALTER TABLE ventas.tb_order ADD CONSTRAINT fk_product_id FOREIGN KEY (product_id)
REFERENCES ventas.tb_product(product_id);

Del código definido anteriormente se extrae el siguiente esquema:

Código fuente descargable

Código Fuente descargable incluyendo datos