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.

 

[5ingh]

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

 

[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?

 

[5ingh]

Personal choice

 

[suarve]

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

 

[Haircut]

Second way is preferrable is the first doesn't allow you to name your constraint and you get some Oracle assigned name.

But yes, they do the same thing at the end of the day.

 

[5ingh]

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

That's what is confusing when using oracle for the first time. Just pick your preferred method and stick to it. There's also different ways to enter VALUES (insert rows) into your table.

INSERT INTO Staff (staffID,Name,Surname) VALUES('1','Joe','Blogs');

The other way of doing the above is to remove the (column,column,column). But that can cause problems down the line. The above method assures you're entering the right values into the right columns.

 

[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?