At work we have an internal chat system, and there is a channel devoted for SQL chat, that covers MS SQL Server, Sybase and Oracle (even though there’s a separate channel for Oracle developrs to talk amongst themselves!).
Anyway a question was asked this week: “what is the difference between a primary key and an index on a table?”
to which I gave the response: “a primary key is a constraint on the table, whereas an index is something that makes it easier to look up things in the table”
Someone else (let’s call him Dave) then chimed in with: “not if its a unique index, that can be a pk without it being a constraint…”
And lo the debate began! Of course, Dave is knowledgable about Sybase (of which I know only what I’ve picked up in the SQL chat channel from watching other people talk about it!) and thinks that Oracle is unfriendly and only works with it if he really has no other choice.
SQL> create table test1 (col1 number);
Table created.
SQL> create unique index unq_t1_index on test1 (col1);
Index created.
SQL> create table test2 (col1 number);
Table created.
SQL> alter table test2
2 add constraint fk_t2_t1_col1
3 foreign key (col1)
4 references test1 (col1);
references test1 (col1)
*
ERROR at line 4:
ORA-02270: no matching unique or primary key for this column-list
Cue comments that imply Oracle is rubbish for making you specify a constraint in order to have a foreign key referencing the table – Sybase (and presumably MS SQL Server) have, apparently, loads of examples of indexes enforcing constraints with just an index and no formal constraint declaration.
Then the following conversation takes place:
<< sybase demo of fk setup to reference a table with only a unique index >>
Dave: hence my point, only Oracle enforces the pk constraint defn
Me: for fk's, true
Me: but surely good design practice would also enforce it?
Dave: we werent arguing design, we were debating whether the dbms
forces it ;)
Me: so what you're saying is that Oracle is better because it forces
better design? *{;-D
Dave: pah
Dave: :p
So, one for Oracle there! *{;-)
Recent Comments