---

Specifying primary key, other keys and referential integrity constraints

---

• Specifying the Primary Key Attribute(s)
  • How to to define that a given set of attributes constitute the primary key of the relation:

    CONSTRAINT [constraint-name] PRIMARY KEY(attribute-list)

    ---

  • Example:

    CREATE TABLE test1 ( ssn char(9), salary decimal(10,2), CONSTRAINT Test1PrimKey PRIMARY KEY(ssn) );

  • Demo the effect, try:

    insert into test1 values ('111223333', 4000.00); insert into test1 values ('111223333', 5000.00); // fails !!

    The second insert command will fail (because 111223333 is already in the database !!!)

    ---

    ---

  • Example: multiple attributes in the primary key

    CREATE TABLE test2 ( essn CHAR(9), pno INTEGER, hours dec(5,2), CONSTRAINT Test2PrimKey PRIMARY KEY (essn, pno) );

    • The third insert will fail:

      insert into test2 values ('111223333', 44, 4.5); insert into test2 values ('111223333', 23, 3.5); insert into test2 values ('111223333', 44, 6.5);

    ---

    ---

  • NOTES:

    A key constraint may be identified by a constraint name : Test1PrimKey and Test2PrimKey in the examples above. The constraint name will be necessary if you want to drop a constraint The constraint name is necessary to identify the constraint that you want to drop

  ---

  ---

  ---

  ---

  ---

• Specifying "other" keys.
  • Recall:

    A relation can have more than one key

    ---

    ---

  • How to specify/define other (candidate) keys :

    CONSTRAINT [constraint-name] UNIQUE(attribute-list)

    ---

  • Example:

    CREATE TABLE test3 ( dnumber INTEGER, dname CHAR(20), CONSTRAINT Test3PrimKey PRIMARY KEY(dnumber), /* Primary key */ CONSTRAINT Test3SecondKey UNIQUE(dname) /* Another key */ );

    specifies that no two tuples in the "test3" relation may have the same value for dname

    Example:

    The second insertion will fail: insert into test3 values (4, 'Research'); insert into test3 values (5, 'Research');

  ---

  ---

  ---

  ---

  ---

• Referential Integrity Constraint.
  • Recall that:

    Foreign Key = a set of attributes used to reference (= identify) tuples in another relation The referential integrity constraint states that the referenced tuple must exist

    ---

  • How to define a referential integrity constraint in SQL:

    CONSTRAINT [constraint-name] FOREIGN KEY (attribute-list) REFRENCES relation(attribute-list)

    ---

  • Example:

    CREATE TABLE test4 ( ssn CHAR(9), /* <------- primary key */ salary dec(9,2), CONSTRAINT Test4PrimKey PRIMARY KEY(ssn) ); CREATE TABLE test5 ( essn CHAR(9), pno INTEGER, CONSTRAINT Test5ForeignKey FOREIGN KEY (essn) /* (essn) is used as foreign key */ REFERENCES test4(ssn) /* It references "ssn" in relation "test4" */ );

    ---

  • Now this insert will fail :
    • insert into test5 values ('111223333', 5);

    because there is no tuple in test4 with SSN='111223333'

  • To make this insert legal, we must first insert the employee:
    • insert into test4 values ('111223333', 5000.00);

    ---

  • Note:

    The attributes used as a foreign key must be specified as: primary key,        or as unique Example: this will cause an error: CREATE TABLE test4 ( ssn CHAR(9), // Not primary key nor UNIQUE salary dec(9,2) ); CREATE TABLE test5 ( essn CHAR(9), pno INTEGER, CONSTRAINT Test5ForeignKey FOREIGN KEY (essn) REFERENCES test4(ssn) );