Oracle Create table constraint question

[AgentZap]

I am doing an assignment for college that I can't seem to find an example of in our book for how to do properly.

In one of my tables I have two columns that are both primary keys and foreign keys

I have "invoice_number" that is part of my Primary Key and it is a Foreign Key in another table and I have "item_number" which is the other part of the Primary Key and is a Foreign Key in another table.

What is throwing me in the fact they are also foreign keys. For a composite PK I can just do

CONSTRAINT invoice_num_item_num_pk
PRIMARY KEY (invoice_number, itemnumber)

but I am not sure how to also setup the FK constraint and in what order it needs to be done.

 

[KB]

Don't you do something like this in the CREATE TABLE command?

FOREIGN KEY (invoice_number) REFERENCES table2 (id)

the order is not important.

 

[AgentZap]

Correct, I am doing this in the create table

This is what I have so far, but as I noted I need to make invoice_number and item_number FK1 and FK2 respectively. I have them as a compound primary key I just need to make them compound primary and also foreign.

CREATE TABLE ship_items
(
invoice_number NUMBER(8), <-- this needs to be my FK1 (from invoices)
item_number NUMBER(7), <-- this needs to be my FK2 (from items)
CONSTRAINT invoice_num_item_num_pk
PRIMARY KEY (invoice_number, item_number),

item_qty_shipped NUMBER(3),
CONSTRAINT item_qty_shipped_nn NOT NULL,
item_sold_price NUMBER(6,2),
CONSTRAINT item_sold_price_nn NOT NULL
);

 

[Drakkon]

I say download a copy of DeZign for Databases and diagram out what you want I dont consider it cheating cause very DBA i know of uses tools like this to map out their DB's...
http://www.datanamic.com/index.html

 

[AgentZap]

Unfortunately that doesn't do me any good when I need to think for myself on the test using logic I gained by doing the assignment the hard way

 

[chiguy2891]

CONSTRAINT name for constraint PRIMARY KEY (column name)
CONSTRAINT name for constraint FOREIGN KEY (column name)
REFERENCES table(column)


name for constraint is just an id that you want the constraint to be known as.