Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2003
    Posts
    268

    Unanswered: enforce SuperType SubType Relationship

    does anyone know of a way to have the DB (rather than programatically) enforce referential integrity on a supertype subtype relationship (which I still havent found a way to legitimately do in Accesss). here is an example

    A measurement can be captured as a Goal or a Benchmark. Each of these has criteria specific to its method (goal or benchmark). This information cannot be stored in the same table. However, a task has multiple subtasks, and these subtasks have mutiple measurements.

    If I delete a measurement I want the the DB to automatically delete from the goal or benchmark tables without having to do it with code.

    Is this doable. Rightnow I am doing this with code, but there has to be a better way.

    MW

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Uh.. that would fall under basic relational database design theory...

  3. #3
    Join Date
    Dec 2003
    Posts
    268

    Uh......

    Originally posted by Teddy
    Uh.. that would fall under basic relational database design theory...
    Thanks for the clarification on that it is design theory. I am quite aware of that. Seeing as there are other RDBMSs out there that support supertype/subtype relationships I was wondering it this was a possiblitily with MSAccess

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1

    Re: Uh......

    Originally posted by mjweyland
    Thanks for the clarification on that it is design theory. I am quite aware of that. Seeing as there are other RDBMSs out there that support supertype/subtype relationships I was wondering it this was a possiblitily with MSAccess
    I see, well now that we have that all cleared up, youmay want to poke around in the help text for "cascade delete".

    Here's a quick description:
    cascading delete
    For relationships that enforce referential integrity between tables, cascading delete is an option that causes the deletion of a record from the primary table to automatically delete all related records in the related foreign table or tables.For example, if you establish a relationship between a Customers (primary) table and an Orders (foreign) table with the cascading delete option enabled, when a record in the Customers table is deleted, all orders associated with that customer would also be deleted in the foreign Orders table.Now also definable from SQL DDL.

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: Uh......

    Originally posted by mjweyland
    Thanks for the clarification on that it is design theory. I am quite aware of that. Seeing as there are other RDBMSs out there that support supertype/subtype relationships I was wondering it this was a possiblitily with MSAccess
    I think it can ... You will have to structure it properly AND relate it accordingly ... This is a humdinger relationship scheme ...

  6. #6
    Join Date
    Dec 2003
    Posts
    268

    Not Clear

    i don't know if I am making myself clear.

    On the relationships screen in MSAccess there is no way to have a relationship between three tables where one tables PK is the PK or FK in one of two other tables. If there is a relationship between two tables Access expects that there is a FK or PK in the related table to be able to enforce referential integrity. Here is a quick Schema:
    ' Create Table : 'Person'
    ' PersonPK :
    ' name :
    '
    CREATE TABLE Person (
    PersonPK TEXT(10) NOT NULL,
    name VARCHAR(25) NOT NULL,
    CONSTRAINT pk_Person PRIMARY KEY (PersonPK));

    '
    ' Create Table : 'Student'
    ' PersonPK : (references Person.PersonPK)
    ' StudentID :
    '
    CREATE TABLE Student (
    PersonPK TEXT(10) NOT NULL,
    StudentID TEXT(10) NOT NULL,
    CONSTRAINT pk_Student PRIMARY KEY (PersonPK),
    CONSTRAINT fk_Student FOREIGN KEY (PersonPK)
    REFERENCES Person (PersonPK));

    '
    ' Create Table : 'Staff'
    ' PersonPK : (references Person.PersonPK)
    ' EmployeeID :
    '
    CREATE TABLE Staff (
    PersonPK TEXT(10) NOT NULL,
    EmployeeID TEXT(10) NOT NULL,
    CONSTRAINT pk_Staff PRIMARY KEY (PersonPK),
    CONSTRAINT fk_Staff FOREIGN KEY (PersonPK)
    REFERENCES Person (PersonPK));

    '
    ' Create Table : 'Address'
    ' AddressID :
    ' PersonPK : (references Person.PersonPK)
    '
    CREATE TABLE Address (
    AddressID TEXT(10) NOT NULL,
    PersonPK TEXT(10) NOT NULL,
    CONSTRAINT pk_Address PRIMARY KEY (AddressID),
    CONSTRAINT fk_Address FOREIGN KEY (PersonPK)
    REFERENCES Person (PersonPK));


    Add some records to Person
    1 bill
    2 fred
    3 jan
    4 wilma
    5 barney
    6 tony

    then add some records to Staff
    1 1
    3 2
    5 3

    then add some records to Student
    2 1
    4 2
    6 3

    Now and try and delete a record from person.

    Wont work

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •