From: "Paul DuBois"
> >I realise that it may (and is) defined in such a way, but it still does
> >explain *why* part of a PRIMARY key might not be NULL. If the combination
> >parts in the PRIMARY key is such that it can uniquely identify a record
> >would be sufficient for a primary key IMHO. It could well be a UNIQUE
> >with the restriction that the complete key (the parts combined) may not
> I'm not sure I understand what you are saying. But if part of a PRIMARY
> could be NULL, then it _wouldn't_ uniquely identify records.
It would IMHO if the other parts combined would be unique.
In this case it's a table that contains account_id, parameter_name and
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.