CODEWITHSUNDEEP
mysqlstored-procedures
sasi
sasi

Reputation: 4318

mysql stored procedure not accepting Foreign key Constraint

I had created a table using StoredProcedure in mysql and i had applied primary key constraint also when i tried to apply foreign key constraint..iam getting error 'Cant create table dbname.tablename'..and here is my Simple StoredProcedure

delimiter |
CREATE PROCEDURE SP_CREATE_TABLE_SAMP()
BEGIN
CREATE TABLE  anan_user  (
   user_id  bigint(15) NOT NULL AUTO_INCREMENT,
   name  varchar(70) DEFAULT NULL,
   last_name  varchar(70) DEFAULT NULL,
   gender  varchar(10) DEFAULT NULL,
   username  varchar(40) DEFAULT NULL,
   password  varchar(40) DEFAULT NULL,
   dateofbirth  date DEFAULT NULL,
   email  varchar(100) DEFAULT NULL,
   phone  varchar(25) DEFAULT NULL,
   mobile  varchar(25) DEFAULT NULL,
   address  varchar(250) DEFAULT NULL,
   pincode  varchar(15) DEFAULT NULL,
   state_id  bigint(15) DEFAULT NULL,
   district_id  bigint(15) DEFAULT NULL,
   constituency_id  bigint(15) DEFAULT NULL,
   profile_Img  varchar(100) DEFAULT NULL,
   is_pwd_changed  varchar(10) DEFAULT 'false',
   registered_time  timestamp NULL DEFAULT NULL,
   updated_date  timestamp NULL DEFAULT NULL,
  PRIMARY KEY ( user_id )

  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
END;

how to give foreign key constraints in stored procedures?

Upvotes: 0

Views: 930

Answers (1)

Andrew C
Andrew C

Reputation: 699

Check your syntax against the MySQL reference on foreign key constraints. It would also be helpful for you to post what you tried, and how you're trying to set the foreign key constraint.

An example constraint definition is:

CREATE TABLE parent (id INT NOT NULL,
                 PRIMARY KEY (id)
) ENGINE=INNODB;
CREATE TABLE child (id INT, parent_id INT,
                INDEX par_ind (parent_id),
                FOREIGN KEY (parent_id) REFERENCES parent(id)
                  ON DELETE CASCADE
) ENGINE=INNODB;

The relevant syntax is the FOREIGN KEY (parent_id) REFERENCES parent(id).

Upvotes: 2

Related Questions