0

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?

Benny Benson
  • 33
  • 6
  • You might want to check this out: https://stackoverflow.com/questions/167576/check-if-table-exists-in-sql-server – Neofytos Konstantinidis Dec 31 '20 at 16:26
  • Not related to your question, but using a dash in a column name requires you delimit the column in back-ticks. You could use an underscore `_` without that restriction. See https://dev.mysql.com/doc/refman/8.0/en/identifiers.html – Bill Karwin Dec 31 '20 at 18:41

1 Answers1

2

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

mjlitz
  • 148
  • 10
  • The subquery in the check constraint does not look good: it is not supported in MySQL (nor in other databases that I know). The foreign key is enough, you don't need the check constraint on top of it. – GMB Dec 31 '20 at 18:43