Computer Science Canada Oracle |
| Author: | Tallguy [ Sat Feb 05, 2011 11:00 am ] |
| Post subject: | 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 :/ |
|
| Author: | Ultrahex [ Sat Feb 05, 2011 11:53 am ] |
| Post subject: | 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. |
|
| Author: | 2goto1 [ Sat Feb 05, 2011 2:05 pm ] |
| Post subject: | RE:Oracle |
you can only have 1 pk per table |
|
| Author: | rdrake [ Sat Feb 05, 2011 3:11 pm ] |
| Post subject: | Re: RE:Oracle |
2goto1 @ Sat Feb 05, 2011 2:05 pm wrote: you can only have 1 pk per table To expand on this, a table can have a composite primary key. Still a single primary key, but it is composed of multiple columns. |
|
| Author: | Tallguy [ Sat Feb 05, 2011 3:56 pm ] |
| Post subject: | RE:Oracle |
how do you state a composite PK in the table? |
|
| Author: | rdrake [ Sat Feb 05, 2011 4:01 pm ] | ||
| Post subject: | Re: Oracle | ||
|
|||
| Author: | 2goto1 [ Sat Feb 05, 2011 4:08 pm ] |
| Post subject: | Re: Oracle |
From http://techonthenet.com/oracle/foreign_keys/foreign_keys.php all you have to do is comma delimit your pk columns: Quote: 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 |
|