Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2011
    Posts
    5

    Unanswered: How to create a foreign key to a table from metaschema ?

    I want to create tables that will keep some additional data on schemas and tables. And I need referential integrity. So my tables must link to tables from schema information_schema.
    The trouble is that those tables are not real tables but views. So usual foreign key cannot be created.

    Example. My table schemas must link to table information_schema.SCHEMATA.

    What can you propose ?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by boraldo View Post
    What can you propose ?
    you can carry on without the foreign keys, yes?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2011
    Posts
    5
    Quote Originally Posted by r937 View Post
    you can carry on without the foreign keys, yes?
    No, without referential integrity system becomes weak.
    I search for ways to store information about schemes and tables in some way that keeps referential integrity.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by boraldo View Post
    No, without referential integrity system becomes weak.
    it's funny, isn't it, that millions of other mysql customers are happy with the referential integrity provided by the software right out of the box, and you feel you need to supplement it somehow...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2011
    Posts
    5
    Quote Originally Posted by r937 View Post
    it's funny, isn't it, that millions of other mysql customers are happy with the referential integrity provided by the software right out of the box, and you feel you need to supplement it somehow...
    I suppose those millions don't need to store metadata about tables.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yeah, that was my point

    you should go ahead and store your metadata if you need to -- nothing wrong with that

    that's why i said "you can carry on without the foreign keys, yes?"

    i'm not suggesting you do away with foreign keys in your regular database, just none between your metadata and the information_schema tables
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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