Ooh, a little possible “gotcha” in there, Razzak – or “gotme” anyway:
I don’t think that I have ever noticed anywhere advice to delete un-needed
indexes and rules _before_ creating a PK or FK.
On reflection it sounds obvious and good advice. However, is it a problem if
the deletions are done after or just good practise?
Regards,
Alastair.
From: A. Razzak Memon
Sent: Wednesday, July 13, 2011 5:40 PM
To: RBASE-L Mailing List
Subject: [RBASE-L] - Re: Many-2-Many Table Primary Keys: Recommendation?
At 11:31 AM 7/13/2011, Bruce Chitiea wrote:
>The Crawl-Walk-Run Department would surely appreciate your wisdom and
>experience here.
>
>Have you any recommendation for crafting PKs for many-to-many (M2M)
>tables?
Bruce,
PKs for Many-To-Many relationship? Hmmmmmm ....
FWIW, a PRIMARY KEY is the column, or set of columns, that uniquely
identifies a row in a table. In other words, it is a set of values
that distinguishes one row from another. Every table should have a
column or set of columns that uniquely identify a row, but not every
table has a defined primary key. Only one primary key can be defined
per table.
Note:
. A PRIMARY KEY cannot be defined if any one of the columns included
in the desired key already have a NULL or DUPLICATE values.
. Defining a PRIMARY KEY automatically enforces NOT NULL and UNIQUE
constraints on the column.
. A PRIMARY KEY definition is used instead of a RULE to prevent
duplicates, and has the advantage of faster performance.
. R:BASE automatically builds an INDEX on the specified column(s)
when a PRIMARY KEY is defined.
. Delete the rules and indexes that are no longer needed before
defining a PRIMARY KEY constraints.
On the other hand, like a PRIMARY KEY, a FOREIGN KEY is a column or
a group of columns that matches a defined PRIMARY KEY, i.e, the
values in a FOREIGN KEY reference values in the PRIMARY KEY.
Note:
. A PRIMARY KEY can exist without a FOREIGN KEY, but a FOREIGN KEY
cannot exist without a PRIMARY KEY.
. A FOREIGN KEY is always defined to reference a PRIMARY KEY.
. A FOREIGN KEY automatically checks that the values in the FOREIGN
KEY exist in the referenced PRIMARY KEY.
. Many FOREIGN KEYs can be defined in one table, and many FOREIGN
KEYs in different tables can reference the same PRIMARY KEY.
. A FOREIGN KEY can have DUPLICATE values but cannot have NULLs.
. The value in a FOREIGN KEY must exist in a PRIMARY KEY and PRIMARY
KEY also prohibit NULLs.
. A FOREIGN KEY replaces a verify a value RULE.
. An INDEX is automatically built when a FOREIGN KEY constraint is
defined.
. Delete the RULEs and INDEXes that are no longer necessary before
defining a FOREIGN KEY constraint.
PRIMARY and FOREIGN KEYs must match in terms of the specific number
of columns. If you have a Multi-Column PRIMARY KEY. you cannot have
a Single-Column FOREIGN KEYs reference it. If you have a Multi-Column
FOREIGN KEY, it cannot reference a single column PRIMARY KEY. When a
PRIMARY KEY is defined as more than one column (Multi-Column PK),
those columns are treated as a whole. PRIMARY and FOREIGN KEYs MUST
match exactly.
Hope that helps!
Very Best R:egards,
Razzak.
