SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Enthusiast
    Join Date
    May 2007
    Posts
    28
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    CREATE TABLE ... REFERENCE problem

    Code:
    CREATE TABLE meeting_primary (
    	
    	meeting_id INT NOT NULL AUTO_INCREMENT,
    	PRIMARY KEY (meeting_id),
    	meeting_title VARCHAR (150) NOT NULL,
    	meeting_title_sub VARCHAR (150),		#Sub-title for meeting
    	location_venue VARCHAR (50),			
    	location_city VARCHAR (25),
    	location_state VARCHAR (25),
    	location_country VARCHAR (25),
    	date_start DATE NOT NULL,			#Event must have a DATE
    	date_end DATE
    )ENGINE = InnoDB;
    
    CREATE TABLE meeting_info (
    	
    	meeting_id INT NOT NULL,
    	child_id INT NOT NULL,
    	PRIMARY KEY (meeting_id, child_id),
    	FOREIGN KEY meeting_id REFERENCES meeting_primary (meeting_id)
    		ON DELETE CASCADE
    
    	info_intro TEXT,
    	info_focus TEXT,
    	info_audience TEXT,
    	info_structure TEXT,				#Brief description of agenda
    	info_topics TEXT,
    	info_stcomm TEXT,				#Steering committee
    	info_sponsors TEXT,
    	info_reg TEXT,
    	info_contact TEXT
    
    ...
    I started using MYSQL last week. Now I m designing a meeting/conference CMS for a big university grant.

    Anyway what i m trying to do is to create one table (meeting_primary) for the basic meeting info's. And then another table for the detailed descriptions (about 10 TEXT columns).

    When people view these information on the webpage (under construction ), data's in the 2 tables will be combined together and displayed on one page.

    So I assume a simple table reference might do the trick.

    However when i run the code through my little phpmyadmin (hosted on a little VPS i own personally), it displays an error.

    Code:
    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'REFERENCES meeting_primary (meeting_id)
            ON DELETE CASCADE
        
    
        info_intro T' at line 6
    Help

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    you've got the syntax slightly wrong

    the correct syntax is
    [CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (index_col_name,...) [reference_definition]
    you're trying to designate the foreign key with

    ... FOREIGN KEY meeting_id REFERENCES ...

    so meeting_id is the name of the key, and you've omitted the parenthesized list of columns that make up the key

    try like this --

    ... FOREIGN KEY my_FK ( meeting_id ) REFERENCES ...

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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