-----------------------------------

Tallguy

Sat Feb 05, 2011 11:00 am


Oracle

-----------------------------------

So i didnt see a forum for Oracle so here i am...

so im in a basic database course, i have posted the assignment that i have

-how do i state that there are two primary keys in one table? is that allowed?

and i have posted the code that i have done for it

-i have tried to run it in oracle, but, continues to day there is an error with all my primary keys :/


-----------------------------------

Ultrahex

Sat Feb 05, 2011 11:53 am


Re: Oracle

-----------------------------------

Most people here on compsci.ca do not work with database software (hence no category), however

http://techonthenet.com/oracle/primary_keys.php should answer all your questions, instead of me just regurgitating information.


-----------------------------------

2goto1

Sat Feb 05, 2011 2:05 pm


RE:Oracle

-----------------------------------

you can only have 1 pk per table


-----------------------------------

rdrake

Sat Feb 05, 2011 3:11 pm


Re: RE:Oracle

-----------------------------------

you can only have 1 pk per tableTo expand on this, a table can have a composite primary key.  Still a single primary key, but it is composed of multiple columns.


-----------------------------------

Tallguy

Sat Feb 05, 2011 3:56 pm


RE:Oracle

-----------------------------------

how do you state a composite PK in the table?


-----------------------------------

rdrake

Sat Feb 05, 2011 4:01 pm


Re: Oracle

-----------------------------------

PRIMARY KEY (col1, col2, ..., coln)


-----------------------------------

2goto1

Sat Feb 05, 2011 4:08 pm


Re: Oracle

-----------------------------------

From http://techonthenet.com/oracle/foreign_keys/foreign_keys.php all you have to do is comma delimit your pk columns: 

CREATE TABLE supplier
(	supplier_id	numeric(10)	not null,
supplier_name	varchar2(50)	not null,
contact_name	varchar2(50),	
CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name)
);

Composite keys are usually based on the natural primary key of your database table. The drawback to a composite primary key is that if you have a data model will lots of foreign keys to your composite primary key, your SQL queries will be a little bigger, and your database storage requirements will increase. Query performance may also degrade. One common solution to this is to create a single column primary key in your table, called a surrogate primary key. The surrogate primary key is just a system identifier. The composite natural primary key then becomes regular old columns in your database table, keeping them not null of course. That being said, as an intro course, you're probably better off just creating a composite primary key