-1

I know about Primary Keys.

PKs must satisfy: (1)Unique, (2) Not NULL, (3) Fully Functional Dependency, (4) Indexed.... these 04 properties.

What are some properties that a Foreign key must always satisfy?

2

The purpose of the foreign key constraint is to protect the referential integrity of the data.

  1. Inserts and updates into the foreign key column must exist in the reference column.
  2. Protects the relationship (link) between the foreign and primary table by forcing you to remove the foreign key prior to performing DDL such as DROP and TRUNCATE.

Hope this helps!

  • 1
    1 implies 2: since FK columns' subrows must exist in referenced columns, referenced columns can't be dropped/truncated. PS The "link" is the FK. PPS In the "Relational" Model (application) relationships are represented by relations/tables. Similarly Entity-"Relationship" Modeling relationships are represented by relations/tables. FKs are (incorrectly) called "relationships" in some methods & products and in some (misre)presentations of the RM & ERM. FKs are constraints ie truths letting the DBMS prevent some errors but are not needed to understand, update or query. – philipxy Jan 9 '16 at 2:44
1

Foreign keys (FKs)

  • A relational FK references a relational PK. An SQL FK references an SQL UNIQUE NOT NULL (possibly a PK).
  • A FK's referencing column types agree with corresponding referenced column types.
  • A FK's referencing column values must appear as corresponding referenced column values.
  • A relational table has no NULLs. An SQL FK subrow with a NULL does not constrain.
  • A relational FK can be the empty set; SQL doesn't allow declaring that.

Primary keys (PKs)

  • A relational PK must be UNIQUE NOT NULL but not contain a smaller UNIQUE NOT NULL. An SQL PK is UNIQUE NOT NULL. (It's really a primary superkey.)
  • A relational PK can sometimes be the empty set; SQL doesn't allow declaring that.
  • A relational table has at least one PK candidate. An SQL base table can have duplicate rows and so no PK.
  • A PK functionally determines every column.

PS

  • A PK can be a partial functional determinant. A table with a primary key does not have to be in any particular normal form in order to query.
  • A PK is independent of indexes. Indexes are a DBMS implementation feature. Even in SQL a table primary key does not have to have an associated index.
  • Why the downvote? – philipxy Jan 9 '16 at 2:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy