Re: Primary keys

From: Sybrand Bakker <gooiditweg_at_sybrandb.demon.nl>
Date: Sat, 08 Mar 2003 20:57:20 +0100
Message-ID: <5hik6v8hp92gpmorltpg2a8o7m0qcr5evb@4ax.com>

On Sat, 8 Mar 2003 19:41:16 -0000, "Robert Diggle" <rob_at_nospam.org> wrote:

>Hi,
>
>I am new to Oracle and would like some advice in creating primary keys. I
>have created a with the SQL syntax below.
>
>I have a few of questions:
>
>1. When creating primary keys that are just numbers, should I just set the
>datatype to INTEGER or use LONG (if that works)? Also should I also define
>the precision?
>

integer isn't a real Oracle datatype and will be automatically translated to NUMBER(38). Long is the the deprecated datatype for binary objects, it is not a 4-byte integer.

It is customary to use number(10) columns for surrogate keys

>2. Is there a way to get the key to auto increment when inserting a new
>record?

This question is asked multiple times per week. Please look up SEQUENCE in your application developers manual or

(ALWAYS!!!!!!!!!!!!!!!!) search google BEFORE posting.

>
>3. Should I create an index for this primary key in this case as I will be
>searching records using the primary key.
Primary keys are always indexed.

>
>Regards,
>Robert
>
>--- SQL Statements: ---------------------
>CREATE TABLE student (
> student_id INTEGER NOT NULL,
> firstname CHAR(20) NOT NULL,

CHARs are *fixed* length strings, you need to use VARCHAR2

> othernames CHAR(50) NULL,
> surname CHAR(20) NOT NULL,
> gender CHAR(1) NOT NULL,
> dob DATE NOT NULL,
> nextofkin CHAR(50) NOT NULL,
> homeaddress_id INTEGER NOT NULL,
> termaddress_id INTEGER NULL,
> PRIMARY KEY (student_id)
> );
>
>CREATE INDEX student_dob
> ON student (dob);
>
>CREATE INDEX student_gender
> ON student (gender);
>
>CREATE INDEX student_surname
> ON student (surname);
>
>ALTER TABLE student
> ADD (CONSTRAINT addr_stud FOREIGN KEY (homeaddress_id)
> REFERENCES address (address_id),
> CONSTRAINT addr_stud2 FOREIGN KEY (termaddress_id)
> REFERENCES address (address_id)
> );
>

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Sat Mar 08 2003 - 13:57:20 CST