Data Primary key vs. Artificial (Autonumber) primary key

On Dec 5, 7:10 am, John W. Vinson
wrote:
With CurrentProject.Connection

.Execute _
"CREATE TABLE Cities" & _
" (city_id IDENTITY (1,1) NOT NULL UNIQUE" & _
",city_name VARCHAR (30) NOT NULL" & _
",PRIMARY KEY (city_name));"

.Execute _
"CREATE TABLE States" & _
" (state_id IDENTITY(1,1) NOT NULL UNIQUE" & _
",state_name VARCHAR (30) NOT NULL" & _
",PRIMARY KEY (state_name));"

.Execute _
" CREATE TABLE Locations" & _
" (location_id IDENTITY (1,1) NOT NULL UNIQUE" & _
",city_id INTEGER NOT NULL" & _
" REFERENCES Cities (city_id)" & _
",state_id INTEGER NOT NULL" & _
" REFERENCES States (state_id)" & _
",PRIMARY KEY (city_id, state_id));"

End With
End Sub

Well, sure, it works. You're creating autonumber primary keys.

No, he isn't.

It *works*,
everyone agrees with that

What exactly is everyone agreeing with, in your opinion?

Jeff Boyce said: "If you're going to create relationships, they will
always be based on the primary key. You cannot change that."

Michael Gramelspacher said (paraphrasing): "I can create a FOREIGN KEY
REFERENCE (a.k.a. Access Relationship with referential integrity
enforced) based on a UNIQUE constraint, which goes against what Jeff
Boyce said about relationships always being based on the primary key."

Jamie says: If by 'relationships' Jeff Boyce meant 'Access
Relationships' (i.e. entities created in the Relationships window in
the Access user interface) then there is no requirement for them to be
based on either PKs or even UNIQUEs; heck, the columns in the
respective tables don't even need to be the same data type!

Jamie.

--