Re: Multi-column keys: Multi-questions

"Robert R. Wagner" wrote:
>
> I have a tableA with multiple columns forming a primary key and tableB using
> that key as a foreign key. They tables were converted from Access by the
> conversion wizard, so I'm not really sharp on creating similar relationships
> for new tables, which I will soon need to do.
>
> If I were starting from scratch:
> 1) How would I create the multicolumn primary key in TableA using
> SQL*PLUS?
Either at creation time
CREATE TABLE TableA ...
CONSTRAINT [<name>] PRIMARY KEY (column1, column2, ..., column)

or subsequently

ALTER TABLE TableA
CONSTRAINT [<name>] PRIMARY KEY (column1, column2, ..., column)

> 2) How would I create the foreign key in TableB?

Either at creation time
CREATE TABLE TableB ...
CONSTRAINT [<name>] FOREIGN KEY (column1, column2, ..., column) REFERENCES TableA [(column1, column2, ..., column)]

or subsequently
ALTER TABLE TableB ...
CONSTRAINT [<name>] FOREIGN KEY (column1, column2, ..., column) REFERENCES TableA [(column1, column2, ..., column)]

>
> Once created, in SQL*PLUS, how do I see the names of the columns that form
> TableA's primary key?

I don't know of an easy way other than to reference (USER|ALL|DBA)_CONS_COLUMNS, possibly joined with (USER|ALL|DBA)_CONSTRAINTS.
>
> Lastly, must the column names in tableB's foreign key be the same as those
> forming the primary key in TableA?

No. The specific (TableB) column names must be cited. TableA's columns, if not specified, imply TableA's primary key.

Note that there are other potential values to the CREATE TABLE and ALTER TABLE statements, including physical storage parameters, multiple FOREIGN KEY clauses, and the like.

Regards,

Gary Received on Thu Dec 09 1999 - 15:44:50 CST