Noob question bout oracle

[suarve]

Hi,

Just a quick check. I making some tables using oracle and noticed a few ways of defining keys. Which way is correct? Depending on where i read they are done differently

A PK

Create table bleh
(SomeID NUMBER(6) PRIMARY KEY NOT NULL)

Have also seen it done as follows:
CREATE TABLE Employee
(EmpID NUMBER(4) CONSTRAINT EMPPRIMARYKEY PRIMARY KEY)

A FK

CREATE TABLE STAFF
(BRANCHID REFERENCES BRANCH)

However, have seen it done as follows:
CREATE TABLE BLEHHHH
(Dep_ID NUMBER(4) NOT NULL,
CONSTRAINT EMPFOREIGNKEY1 FOREIGN KEY (Dep_ID) REFERENCES tblDepartment (Dep_ID))



Am unsure whuich way i should be doing this for each table (using oracle v10).



Thanks

EDIT: SQL*Plus seems to allow both methods, giving no errors when creating both ways.

 

[suarve]

This is the way I do it - poor example:

CREATE TABLE Staff
(
staffID        INT NOT NULL,
Name        VARCHAR(25),
Surname        VARCHAR(25),
PRIMARY KEY         (staffID));

CREATE TABLE Payroll
(
PayID INT NOT NULL,
Salary  VARCHAR(6),
staffID INT NOT NULL,
FOREIGN KEY (staffID) REFERENCES Staff(staffID) ON DELETE CASCADE,
PRIMARY KEY (PayID));

There are a few ways of doing it, but I like it like above

Was wondering if there is difference in the various ways, or is it down to personal choice?

 

[suarve]

thats what was confusing me tbh. Would have thought somethiong like this would be mentioned somewhere?

 

[suarve]

Cheers for replies people.

 

[suarve]

Actually while on topic i have another small question:

I have a staff table, where each record has a staffid. staff manage eachother and on the erm i am showing this table as having a recursive relationship. Kindfa like this:

StaffID Manager
1
2 1
3 1
4 2

Would i need to specify this fact when creating the table in oracle? E.g.



create table staff (
STAFFID NUMBER(10) CONSTRAINT STAFF_PK PRIMARY KEY NOT NULL,
MANAGER NUMBER(7),
CONSTRAINT STAFF_STAFF_FK FOREIGN KEY (STAFFID) REFERENCES staff(staffID);


Any ideas?