Variations on create

We have seen a basic usage of the SQL CREATE TABLE command. Now we see how to say that a field must be specified and how to specify a default value.

No field is mandatory in a table. If we don't specify it in a INSERT statement Oracle simpy put a NULL in it. If we want to force the caller to specify a value for a given field we could use the NOT NULL attribute. If we have a reasonable default for a field we can specify it using the DEFAULT attribute.

I'm not sure what is the sense in using NOT NULL and DEFAULT together, but we can do it. Just remember that, for Oracle, you should put first the DEFAULT attribute and then NOT NULL:

CREATE TABLE doughnut (
  name VARCHAR2(10) NOT NULL,
  price DEC(5,2) DEFAULT 2.99 NOT NULL,
  stock INT,
  created DATE
);

If we have a doughnut table created in this way and we try to insert a row in this way:

INSERT INTO doughnut (PRICE, STOCK, CREATED)
  VALUES (2.99, 33, to_date('12-01-2007','dd-mm-yyyy'));

We get an error: cannot insert NULL.

Post written while reading Head First SQL