Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2002
    Location
    Australia
    Posts
    6

    Question Unanswered: Relationship between tables of two database

    Hi All'

    I'm in process of upgrading our Microsoft Access Database to SQL Server 2000. We have one front end database that links all the backend databases. But there are some databases which shares tables with other databases. Currently the refrential integrity is being done by VBA codes in the forms itself (bad na!).

    Example
    ----------
    Database: Vehicle
    Tables in "Vehicle" database are VechileType (v_type, v_desc) and VehicleInventory (v_RegNo, v_Type, customerID)

    Database: Customer
    Tables in "Customer" database are CustomerType(c_type, c_desc) and CustomerInventory (customerID, customerName, c_type).

    This is just example...there are many (more than 10!) tables in each database. So, I do NOT want to place everything in a single database.

    Now I'm looking solution for creating trigger that ensures the Refrential Integrity on "customerID" field in both VehicleInventory and CustomerInventory tables. eg user can not delete customerID from CustomerInventory table if its record exist in VehicleInventory table.

    CAN ANY ONE HELP ME........................

    Thanks

    cheers'

    -avi

  2. #2
    Join Date
    Jun 2002
    Posts
    1

    Re: Relationship between tables of two database

    Avinash, why don't you create primary key and foreign key constraints to implement referential integrity. That's a better approach than triggers.

    gyan.


    Originally posted by avishesh
    Hi All'

    I'm in process of upgrading our Microsoft Access Database to SQL Server 2000. We have one front end database that links all the backend databases. But there are some databases which shares tables with other databases. Currently the refrential integrity is being done by VBA codes in the forms itself (bad na!).

    Example
    ----------
    Database: Vehicle
    Tables in "Vehicle" database are VechileType (v_type, v_desc) and VehicleInventory (v_RegNo, v_Type, customerID)

    Database: Customer
    Tables in "Customer" database are CustomerType(c_type, c_desc) and CustomerInventory (customerID, customerName, c_type).

    This is just example...there are many (more than 10!) tables in each database. So, I do NOT want to place everything in a single database.

    Now I'm looking solution for creating trigger that ensures the Refrential Integrity on "customerID" field in both VehicleInventory and CustomerInventory tables. eg user can not delete customerID from CustomerInventory table if its record exist in VehicleInventory table.

    CAN ANY ONE HELP ME........................

    Thanks

    cheers'

    -avi

Posting Permissions

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