Whoops, Oracle does auto not null primary keys

Software

Update: It’s been brought to my attention that not defining primary keys as not null does have an impact after all, refer to Dale McGowan’s comment below. Suffice to say, I will continue to define them as not null from now on!

Much like a musician who despite getting the right sound realises they've been playing their instrument wrong throughout their career, I often find I'm not using computer software properly or I've been wasting effort. I guess I get myself into a predictable routine that I'm used to.

In this case I found out something about Oracle databases. I've been using them for years, but it took a practical class at university yesterday to realise I've been wasting time with a simple line of code that was completely unnecessary.

If you do any sort of work with databases you'd probably want to smack me on the head for this, but I didn't know that primary keys are automatically assigned as not null; presumably because it doesn't make logical sense and it would break a table to have a primary key that has null values!

Up until Friday this is the schema I would have created and entered into an Oracle database:

create table Jackarse (
  refID char(6) not null,
  name varchar(32),
  grilledCheeseSandwich boolean,
  primary key (refID)
);

And here's what I've since learned I can do instead:

create table Jackarse (
  refID char(6),
  name varchar(32),
  grilledCheeseSandwich boolean,
  primary key (refID)
);

That's right, both these schemas will result in the same table being created in Oracle. I guess it wasn't harming anything having the not null command there, it was just unnecessary.

Author bio and support

Me!

Ruben Schade is a technical writer and IaaS engineer in Sydney, Australia who refers to himself in the third person in bios. Wait, not BIOS… my brain should be EFI by now.

The site is powered by Hugo, FreeBSD, and OpenZFS on OrionVM, everyone’s favourite cloud infrastructure provider.

If you found this post helpful or entertaining, you can shout me a coffee or buy some silly merch. Thanks!