1

I am creating 2 tables like below, I want these two tables to have one to one relationship and whenever I insert a row to User I want the same PK to be inserted in the other table as well. Would this work with just INSERT INTO into 1 table User so that the same gets inserted to points as well.

CREATE TABLE if not exists User (
    id bigint AUTO_INCREMENT,
    country VARCHAR(128),
    PRIMARY KEY (id)
);

CREATE TABLE if not exists points (
    userid bigint UNIQUE NOT NULL,
    points bigint
);

ALTER TABLE ['points'] ADD CONSTRAINT FK FOREIGN KEY([userid])  REFERENCES [User]([id]);

Also I am using mysql 8.0.28, ALTER TABLE ['points'] ADD CONSTRAINT FK FOREIGN KEY([userid]) REFERENCES [User]([id]); this statement returns syntax error at []

What is the correct way to implement ? Also out of curiosity, how can I define the foreign key at table creation ?

2 Answers 2

1

Sqlare brackets are a sign that you copy your code from a site that explain how to do it in SQL Server.

For mysql you need backticks to escape reserved words and the syntax is also different

CREATE TABLE if not exists User (
    id bigint AUTO_INCREMENT,
    country VARCHAR(128),
    PRIMARY KEY (id)
);

CREATE TABLE if not exists points (
    userid bigint UNIQUE NOT NULL,
    points bigint
);

ALTER TABLE points ADD CONSTRAINT fk_user_id FOREIGN KEY (userid) REFERENCES `User` (id);
Records: 0  Duplicates: 0  Warnings: 0

fiddle

3
  • Thank you, I also want to know, if I INSERT INTO to the User table, will I have a row inserted automatically on points table or do i have to run separate queries for both ?
    – umarkaa
    Apr 3 at 17:17
  • you need to write a mysql trigger to auto mate the insert, but that is a separate question.
    – nbk
    Apr 3 at 17:19
  • just asked a new one for this,
    – umarkaa
    Apr 3 at 17:32
0

See LAST_INSERT_ID() for how to get the auto_increment value from the INSERT INTO user in order to feed it to points.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.