1

i have created 2 tables at mysql then i am adding a new constraint for a foreign key between them , here are the tables .

create TABLE post
(
   post_id NUMERIC (5) NOT NULL ,
   post_username varchar (30) NOT NULL ,
   post_category_name VARCHAR (30) NOT NULL ,
   post_date TIMESTAMP ,
   post_reporting numeric (3) ,
   post_text varchar(2000) ,
   post_photo varchar(200)
);
alter TABLE post add CONSTRAINT po_pk PRIMARY KEY (post_id , post_username , post_date) ;

/*comment*/
create table comment
(
  comment_username varchar(30) NOT NULL ,
  comment_post_id NUMERIC(5) NOT NULL ,
  comment_post_username VARCHAR (30) NOT NULL ,
  comment_post_date TIMESTAMP,
  comment_date TIMESTAMP ,
  comment_text varchar(2000) ,
  comment_photo varchar(200)
);
alter table comment add CONSTRAINT co_pk PRIMARY KEY (comment_username,comment_date,comment_post_date,comment_post_id,comment_post_username);

then , when i add a new constraint for the foreign key as following 

ALTER TABLE comment ADD CONSTRAINT com_ps_un_fk FOREIGN KEY (comment_post_date)
REFERENCES post(post_date);

i receive (errno: 150 "Foreign key constraint is incorrectly formed") , both attributes are having the same datatype of TIMESTAMP i also tried another probabilities for both of them such as making them both NULL , NULL DEFAULT CURRENT_TIMESTAMP and the same as NOT NULL but nothing seemed to work .

Amr
  • 13
  • 3

1 Answers1

0

post_date is a part of a primary key. So, it can't be used as a key independently.

You have to make an index key on it. then, you would be able to make the relation:

ALTER TABLE `post` ADD INDEX(`post_date`);

ALTER TABLE comment ADD CONSTRAINT com_ps_un_fk FOREIGN KEY (comment_post_date)
REFERENCES post(post_date);

Fiddle

Mojtaba
  • 4,852
  • 5
  • 21
  • 38