List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:July 30 2002 2:31am
Subject:Re: do you need to define not null and unique?
View as plain text  
At 16:32 -0700 7/29/02, Desmond Lee wrote:
>Hello
>
>
>Just wnated to calrify something here.
>
>If you define your id in a table to be a primary key, do you also 
>need to define it as 'NOT NULL' and 'UNIQUE'.
>
>I've notice in some examples like the following off the mysql site:
>CREATE TABLE animals (
>             id MEDIUMINT NOT NULL AUTO_INCREMENT,
>             name CHAR(30) NOT NULL,
>             PRIMARY KEY (id)
>             );
>that they say the id col is not null, but do they have to since 
>they've already defined it to be a primary key?

A PRIMARY KEY must be defined to be NOT NULL, a UNIQUE index need not
be.  If you define a column as AUTO_INCREMENT, MySQL will automatically
define it as NOT NULL (at least, it will as of some 3.23.xx version of
MySQL).

If you define a column as a PRIMARY KEY and as UNIQUE, you may end up
with two unique indexes on the column, so you don't want to do that.

>Thanks
>
>Desmond

Thread
do you need to define not null and unique?Desmond Lee30 Jul
  • Re: do you need to define not null and unique?Paul DuBois30 Jul