mysql - Create a table and check for an attribute if it already exists in another table. Only then Save it

Question

I want to create a table with the values ?name‘ and ?immobilien-id‘. Both are the primary key. But there should be a restricition That i can only Save a ?name‘ with a value That already exists in my other table ?immobilienmakler‘. In ?immobilienmakler‘ i already created a ?name‘.

My try

CREATE TABLE verwalten (
  name STRING CHECK(Select name from immobilienmakler WHERE immobilienmakler.name = name),
  immobilien-id INTEGER NOT NULL,
  PRIMARY KEY(name, immobilien-id)
);

How can i put a IF EXIST into this Statement?

Answer 1

Just create a foreign key that refers to the field in the other table. That will enforce pre-existing values like you are asking.

Also, it's worth noting that if immobilien-id is guaranteed to be unique, that should be the only primary key instead of using a composite primary key. You want to keep them as simple as you can.

CREATE TABLE verwalten (
  immobilien-id NUMBER NOT NULL,
  PRIMARY KEY(name, immobilien-id),
  FOREIGN KEY(name) REFERENCES immobilienmakler(name)
);

Note: Number is more commonly used as it is also valid in oracle. It also allows fine-tuning of precision, which should be thought about.

Reference: https://www.w3schools.com/sql/sql_foreignkey.asp