CODEWITHSUNDEEP
mysqlsqldatabasedatabase-design
Ben Aidley
Ben Aidley

Reputation: 347

MySQL - Are "NOT NULL" constraints needed for primary keys?

is it necessary to declare "NOT NULL" constraints for primary keys in the MySQL database? A primary key cannot have NULL values because it auto_increments anyway and automatically fills the field record. So am I correct in saying this mean I can remove the "NOT NULL" constraint for my primary keys?

Upvotes: 17

Views: 9085

Answers (4)

Pratik
Pratik

Reputation: 1

WE need not declare explicitly the column as not null because Primary key constraint makes the column NOT NULL. I have checked in Oracle.

Upvotes: 0

a1ex07
a1ex07

Reputation: 37354

Primary key must not include nullable columns. auto_increment is not a check constraint, (it is rather a default constraint) , so you cannot remove not null from definition of the column that is part of primary key regardless of presence of auto_increment. You don't have to type not null while creating the table for primary key in mysql, because the engine adds this constraint automatically.

Upvotes: 0

Tony Hopkinson
Tony Hopkinson

Reputation: 20320

Yes and no You can remove "Not null", that won't remove the constraint though. Personally I'd leave them in, you gain nothing worthwhile from taking them out.

Upvotes: 2

T.J. Crowder
T.J. Crowder

Reputation: 1074058

(As you've tagged your question mysql.) In MySQL, you don't have to do it explicitly. From the manual:

A PRIMARY KEY is a unique index where all key columns must be defined as NOT NULL. If they are not explicitly declared as NOT NULL, MySQL declares them so implicitly (and silently).

Of course, just because you don't have to doesn't mean you might not want to for clarity, etc.

Upvotes: 21

Related Questions