List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:April 26 2005 3:02pm
Subject:Re: why NOT NULL in PRIMARY key??
View as plain text  
At 16:56 +0200 4/26/05, Jigal van Hemert wrote:
>From: "Paul DuBois"
>
>>  >I realise that it may (and is) defined in such a way, but it still does
>not
>>  >explain *why* part of a PRIMARY key might not be NULL. If the combination
>of
>>  >parts in the PRIMARY key is such that it can uniquely identify a record
>it
>>  >would be sufficient for a primary key IMHO. It could well be a UNIQUE
>index
>>  >with the restriction that the complete key (the parts combined) may not
>be
>>  >NULL...
>>
>>  I'm not sure I understand what you are saying.  But if part of a PRIMARY
>KEY
>>  could be NULL, then it _wouldn't_ uniquely identify records.
>
>It would IMHO if the other parts combined would be unique.

If the other parts are unique, then you don't need the nullable column
in your primary key.  Just define it with the other columns.



>
>In this case it's a table that contains account_id, parameter_name and
>value.
>Account_id and parameter_name would be sufficient to uniquely identify a
>records (only one parameter with the same name per account allowed).
>
>But since searches use the parameter_name/value combination in almost all
>cases I would define the key as:
>parameter_name-value-account_id. InnoDB is very fast if you use the primary
>key and a lot slower if you use secudary key(s), so queries can get
>considerably faster if you use a primary key.
>My combined key would be able to uniquely identify records. I know the SQL
>standard defines a PRIMARY KEY as a combination between UNIQUE and NOT NULL,
>but it's still not clear to me why this implies that all *parts* of the
>primary key *must* also have the NOT NULL constraint.
>
>Regards, Jigal.


-- 
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Thread
why NOT NULL in PRIMARY key??Jigal van Hemert26 Apr
  • Re: why NOT NULL in PRIMARY key??Martijn Tonies26 Apr
  • Re: why NOT NULL in PRIMARY key??Jochem van Dieten26 Apr
  • Re: why NOT NULL in PRIMARY key??Jigal van Hemert26 Apr
  • Re: why NOT NULL in PRIMARY key??Harald Fuchs26 Apr
  • Re: why NOT NULL in PRIMARY key??Jigal van Hemert26 Apr
    • Re: why NOT NULL in PRIMARY key??SGreen26 Apr
      • Att. Shawn GreenJigal van Hemert2 May
  • Re: why NOT NULL in PRIMARY key??Dawid Kuroczko26 Apr
  • Re: why NOT NULL in PRIMARY key??Jigal van Hemert26 Apr
    • Re: why NOT NULL in PRIMARY key??Jochem van Dieten26 Apr
  • Re: why NOT NULL in PRIMARY key??Jigal van Hemert26 Apr
    • Re: why NOT NULL in PRIMARY key??Paul DuBois26 Apr
  • Re: why NOT NULL in PRIMARY key??Martijn Tonies26 Apr
  • Re: why NOT NULL in PRIMARY key??Mikhail Entaltsev26 Apr
  • Re: why NOT NULL in PRIMARY key??Jigal van Hemert26 Apr
  • Re: why NOT NULL in PRIMARY key??Harald Fuchs26 Apr
  • Re: why NOT NULL in PRIMARY key??Martijn Tonies26 Apr
  • Re: why NOT NULL in PRIMARY key??Jigal van Hemert27 Apr
    • Re: why NOT NULL in PRIMARY key??Peter Brawley27 Apr
  • Re: why NOT NULL in PRIMARY key??Jigal van Hemert27 Apr
    • Re: why NOT NULL in PRIMARY key??Frank Bax27 Apr
    • Re: why NOT NULL in PRIMARY key??Peter Brawley27 Apr
    • Re: why NOT NULL in PRIMARY key??Joerg Bruehe27 Apr
  • Re: why NOT NULL in PRIMARY key??Jigal van Hemert28 Apr
RE: why NOT NULL in PRIMARY key??Jay Blanchard26 Apr
  • Re: why NOT NULL in PRIMARY key??Jigal van Hemert26 Apr
    • Re: why NOT NULL in PRIMARY key??Paul DuBois26 Apr
  • RE: why NOT NULL in PRIMARY key??Frank Bax26 Apr
  • Re: why NOT NULL in PRIMARY key??Jigal van Hemert26 Apr
    • Re: why NOT NULL in PRIMARY key??Paul DuBois26 Apr
  • Re: why NOT NULL in PRIMARY key??Martijn Tonies26 Apr
  • Re: why NOT NULL in PRIMARY key??Jigal van Hemert26 Apr
    • Re: why NOT NULL in PRIMARY key??Paul DuBois26 Apr
    • Re: why NOT NULL in PRIMARY key??Peter Brawley26 Apr
RE: why NOT NULL in PRIMARY key??emierzwa26 Apr
  • RE: why NOT NULL in PRIMARY key??SGreen26 Apr
  • Re: why NOT NULL in PRIMARY key??Martijn Tonies26 Apr
RE: why NOT NULL in PRIMARY key??Jay Blanchard26 Apr
  • Re: why NOT NULL in PRIMARY key??Dawid Kuroczko27 Apr
  • Re: why NOT NULL in PRIMARY key??Martijn Tonies27 Apr
RE: why NOT NULL in PRIMARY key??emierzwa26 Apr
Re: why NOT NULL in PRIMARY key??beacker30 Apr
  • Re: why NOT NULL in PRIMARY key??Jigal van Hemert1 May
  • Re: why NOT NULL in PRIMARY key??Martijn Tonies1 May