68005

Foreign key or null value

I have 2 tables: pages and menu

I want to have a pointer into menu table as foreign key to pages.id. The problem is that some menu rows don't have a link to page. When someone clicks in the link opens a submenu. How i do this in phpmyadmin?

The match i want is 1 to 1 or 1 to 0

Thanks

Maybe if i have a row to pages that has id=some_id with pages.body=null and all the menus that i want to have no submenu would have menu.pages_id=some_id Is this the right way to do that i want?

Answer1:

Using some magic value for menu.pages_id doesn't work because that value whatever it is must exist on some row in the pages table.

The right way to do this is to make menu.pages_id accept NULL. It's legal for a column to be nullable even if it's part of a UNIQUE constraint and a FOREIGN KEY constraint.

CREATE TABLE menu ( ... pages_id INT NULL, UNIQUE KEY (pages_id), FOREIGN KEY (pages_id) REFERENCES pages(pages_id) ) ENGINE=InnoDB;

Recommend

  • In a nested transaction, will inner transaction roll back if outer transaction is rolled back?
  • An element in $(document).ready is undefined in a certain situation
  • drupal 7 custom content hook_theme output
  • drop-down menu with errors
  • How to change the color of actionbar (actionbarsherlock)
  • How can I add a Submenu to the WordPress Admin Bar
  • how to handle mongodb's E11000 duplicate key error in ruby
  • How to accept hash parameters in routes
  • wp-admin can't access after moving
  • Restoring deleted mysql database
  • How to change the base_url in magento.,because my site were gone after i'm changing that on my
  • ActionBar three-dot dropdown opens at the wrong place
  • Access 2007 forms with parameterized RecordSource
  • Encoding: everything is UTF-8 but the DB output is displayed wrong. Any Ideas?
  • JQM (jQueryMobile) Problem with AJAX content and focus()
  • Entity Framework ObjectContext: Concurrency
  • Android custom URI scheme incorrectly encoded when type in browser
  • Using MouseListener to select a range of cells in a grid
  • HTML5 video only works in IE. The other browsers shows the black screen
  • Dart - Isolate Cross Window Communication
  • Webgrid not refreshing after delete MVC
  • Alternative To body {overflow:scroll;} That Will Prevent Page Jostling/Wriggling?
  • preg_replace Double Spaces to tab (\\t) at the beginning of a line
  • Play WS (2.2.1): post/put large request
  • Jquery UI tool tip close icon
  • Extracting HTML between tags
  • MongoDB in PHP using aggregate to group by _id is null not working
  • Why HTML5 Canvas with a larger size stretch a drawn line?
  • htaccess rewriting URLs with multiple forward slashes
  • Display Images one by one with next and previous functionality
  • Web-crawler for facebook in python
  • Warning: Can't call setState (or forceUpdate) on an unmounted component
  • Unanticipated behavior
  • Traverse Array and Display in markup
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • A cron job substitute?
  • Qt: Run a script BEFORE make
  • Can't mass-assign protected attributes when import data from csv file
  • How to Embed XSL into XML
  • Unable to use reactive element in my shiny app