Re: Required property of Primary Key fields

Tech-Archive recommends: Fix windows errors by optimizing your registry

On 3 Sep, 16:45, "tina" <nos...@xxxxxxxxxxx> wrote:
i would agree that single-field primary keys should always be Required =
Yes. as for composite keys, i suppose it's possible that a valid value for
all but one of the individual fields in the key might be Null

No, it isn't possible.

Consider this simple example:

CREATE TABLE Test1
(
col1 INTEGER NOT NULL,
col2 INTEGER
)
;
ALTER TABLE Test1 ADD
PRIMARY KEY (col1, col2)
;
INSERT INTO Test1 (col1, Col2) VALUES (1, NULL)
;

While the ALTER TABLE (PK creation) with the NULLable column succeeds,
it's a bit pointless because the INSERT will fail with a "primary key
cannot contain a Null value" error.

....unless by 'primary key' you meant a logical key rather than the
arbitrary PRIMARY KEY designation. While you could use a UNIQUE
constraint (index no duplicates), be warned that Jet allows multiple
NULL values e.g.

CREATE TABLE Test2
(
col1 INTEGER NOT NULL,
col2 INTEGER
)
;
ALTER TABLE Test2 ADD
UNIQUE (col1, col2)
;
INSERT INTO Test2 (col1, Col2) VALUES (1, NULL)
;
INSERT INTO Test2 (col1, Col2) VALUES (1, NULL)
;
INSERT INTO Test2 (col1, Col2) VALUES (1, NULL)
;

All the above statements succeed but would you consider this table to
have a logical 'primary key'? I do not.

Also consider that those NULL values would all change in a single
CASCADE referential action e.g.

CREATE TABLE Test3
(
col1 INTEGER NOT NULL,
col2 INTEGER
)
;
ALTER TABLE Test3 ADD
UNIQUE (col1, col2)
;
ALTER TABLE Test3 ADD
FOREIGN KEY (col1, col2)
REFERENCES Test2 (col1, col2)
ON UPDATE CASCADE
;
INSERT INTO Test3 (col1, Col2) VALUES (1, NULL)
;
INSERT INTO Test3 (col1, Col2) VALUES (1, NULL)
;
INSERT INTO Test3 (col1, Col2) VALUES (1, NULL)
;
UPDATE Test2
SET col2 = 1
;

The final statement fails with a "Cannot perform cascading operation.
It would result in a duplicate key in table ''." error (yes, that's
verbatim and yes the table name Test3 appears is blank due to a bug in
Jet/ACE).

Jamie

--


.

Relevant Pages

  • Re: Duplicate entry inserted in the SQL database while calling an
    ... containing columns PK, col1, col2 and col3, with a primary key created ... col1 varcharNOT NULL, ... col2 int NOT NULL, ... in col1 and col2 are to be considered duplicates and should be ...
    (microsoft.public.inetserver.asp.db)
  • Re: How do you mulitply in a field?
    ... referencing table must match exactly in the referencing table by ... col1 INTEGER NOT NULL, ... col2 INTEGER, UNIQUE ... composite PRIMARY KEY that is not really a primary key is about clustering ...
    (microsoft.public.access.tablesdbdesign)
  • Re: How do you mulitply in a field?
    ... referencing table must match exactly in the referencing table by ... col1 INTEGER NOT NULL, ... col2 INTEGER, UNIQUE ... composite PRIMARY KEY that is not really a primary key is about ...
    (microsoft.public.access.tablesdbdesign)
  • Re: concatenate function
    ... Primary Key, e.g. ... ADD CONSTRAINT constraintName ... ON tablename (col1, col2) ...
    (microsoft.public.sqlserver.programming)
  • Re: Inserting multiple records into two tables...with a twist
    ... I assume that you have an IDENTITY column, ... reason would typically be a high rate of concurrent insertions.) ... INSERT tblB(id, col1, col2, ... ...
    (comp.databases.ms-sqlserver)