Create Tables for ORACLE (Foreign Keys Help)

[webgod]

I am trying to create the following tables. I am having issues getting my head around creating the primary keys, foreign keys and whether the fields are null or not. I am new to Oracle, used Mysql before, but never had to use foreign keys…

Suppliers (SupplierNo, Name, Status, City)
Parts (PartNo, Name, Color, Weight, City)
Projects (ProjectNo, Name, City)
Shipments (SupplierNo, PartNo, ProjectNo, Quantity)

[r937]

@webgod

used Mysql before, but never had to use foreign keys…

heh, that’s mildly amusing

anyhow, here ya go –

CREATE TABLE Suppliers
( SupplierNo  INTEGER     NOT NULL PRIMARY KEY
, Name        VARCHAR(37) NOT NULL
, Status      CHAR(2)     NOT NULL
, City        VARCHAR(37) NOT NULL 
);

CREATE TABLE Parts 
( PartNo      INTEGER     NOT NULL PRIMARY KEY
, Name        VARCHAR(37) NOT NULL
, Color       CHAR(3)     NOT NULL
, Weight      SMALLINT    NOT NULL
, City        VARCHAR(37) NOT NULL
);

CREATE TABLE Projects 
( ProjectNo   INTEGER     NOT NULL PRIMARY KEY
, Name        VARCHAR(37) NOT NULL
, City        VARCHAR(37) NOT NULL 
);

CREATE TABLE Shipments 
( SupplierNo  INTEGER NOT NULL 
, FOREIGN KEY ( SupplierNo ) REFERENCES Suppliers ( SupplierNo )
, PartNo      INTEGER NOT NULL 
, FOREIGN KEY ( PartNo     ) REFERENCES Parts     ( PartNo )
, ProjectNo   INTEGER NOT NULL 
, FOREIGN KEY ( ProjectNo  ) REFERENCES Projects  ( ProjectNo )
, PRIMARY KEY ( SupplierNo  
              , PartNo     
              , ProjectNo )
, Quantity    SMALLINT NOT NULL
);