0

I started learning SQL (SQL-Server) a few days ago but now I'm getting confused about when is it necessary to set NOT NULL for a field. I've known that the NOT NULL restriction won't let me update that row after it was created with null value. But how we know whether a field of data worth setting NOT NULL or not?

Considering the following example (Extracted from the internet):

CREATE TABLE CUSTOMERS(
   ID       INT           NOT NULL,
   NAME     VARCHAR (20)  NOT NULL,
   AGE      INT           NOT NULL,
   ADDRESS  CHAR (25),
   SALARY   INT
   PRIMARY KEY (ID)
);

Why ID, Name, Age are set NOT NULL whereas ADRESS, SALARY are not. Is setting-NOT-NULL-restriction for fields at my disposal?

If I set NOT NULL restriction for all the fields of the table as the following. Is it ok?

CREATE TABLE CUSTOMERS(
   ID       INT           NOT NULL,
   NAME     VARCHAR (20)  NOT NULL,
   AGE      INT           NOT NULL,
   ADDRESS  CHAR (25)     NOT NULL,
   SALARY   INT           NOT NULL
   PRIMARY KEY (ID)
);

Hope to receive any explaination about my problem. Thanks in advance!

DunDev
  • 220
  • 1
  • 4
  • 8
  • NOT NULL is used when you cannot accept an empty value. In your first sample, clearly a customer without an ID, Name and Age wouldn't be useful (I can't tell you why age is required, because it's not my table). Your design makes sense if you require ADDRESS and SALARY to contain a value, and not if they don't matter to you. Having a NOT NULL constraint means you cannot post a row without having data for those columns. If you can accept data without knowing the address or salary, then you don't want NOT NULL. This is really a tutorial question that any good SQL book can answer for you. – Ken White Jul 11 '17 at 00:41
  • @KenWhite Is it possible if I set all of the fields to NOT NULL like the second example? – DunDev Jul 11 '17 at 00:46
  • It is also helpful to determine on a join if you get a "Null" when the field is not null. It is cause by an outer join. – M T Head Jul 11 '17 at 00:46
  • Did you read what I wrote? Yes, it's possible, but only if you cannot allow any of the column's values to be empty. If you add NOT NULL as a constraint to every single column, then you can only save or modify a row if all the values are provided. Again, this is **basic SQL**. Find a tutorial or book. – Ken White Jul 11 '17 at 00:54
  • @KenWhite I've got it. Thanks so much! – DunDev Jul 11 '17 at 00:57

0 Answers0