How to allow NULL in foreign keys of a compound primary key

I want to have NULLs in foreign keys of a compound primary key. This is an example of what I would expect to be valid data.

product_id variant_id
123-123 ABC
123-123 NULL
456-456 ABC

I cannot figure out why the following SQL in postgres gives NOT NULL violation constraint me when inserting NULL as variant_id.

CREATE TABLE IF NOT EXISTS inventory.price (   product_id             UUID NOT NULL, -- this has to be always to a valid product   variant_id             UUID,          -- this could be NULL   amount                 MONEY NOT NULL,   created_at             TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),   -- Constraints   CONSTRAINT inventory_price_pkey PRIMARY KEY (product_id, variant_id),   CONSTRAINT inventory_price_inventory_product_fkey FOREIGN KEY (product_id)     REFERENCES inventory.product (id) MATCH FULL,   CONSTRAINT inventory_price_inventory_variant_fkey FOREIGN KEY (variant_id)     REFERENCES inventory.variant (id) MATCH SIMPLE,   CONSTRAINT inventory_price_amount_gt_0 CHECK (amount > '0'::money) ); 

And the inspection to information_schema confirms the non-nullable constraint.

column_name column_default is_nullable data_type
product_id NULL NO uuid
variant_id NULL NO uuid
amount NULL NO money
created_at now() NO timestamp with time zone

“Foreign key constraint is incorrectly formed”

-- MySQL Script generated by MySQL Workbench -- Fri Jan 22 17:34:03 2021 -- Model: New Model    Version: 1.0 -- MySQL Workbench Forward Engineering  SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';  -- ----------------------------------------------------- -- Schema mydb -- -----------------------------------------------------  -- ----------------------------------------------------- -- Schema mydb -- ----------------------------------------------------- CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ; USE `mydb` ;  -- ----------------------------------------------------- -- Table `mydb`.`base_calendars_exceptions` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`base_calendars_exceptions` (   `id` INT NOT NULL AUTO_INCREMENT,   `id_base_calendar_exception` INT NULL,   `name` VARCHAR(254) NULL,   `date` DATETIME NULL,   `start_hour` DECIMAL(4,2) NULL,   `end_hour` DECIMAL(4,2) NULL,   `free_week_days` SMALLINT(1) NULL,   `user_id` INT NULL,   PRIMARY KEY (`id`),   INDEX `ID_idx` (`id_base_calendar_exception` ASC) VISIBLE,   CONSTRAINT `ID_1`     FOREIGN KEY (`id_base_calendar_exception`)     REFERENCES `mydb`.`base_calendars_settings` (`ID`)     ON DELETE NO ACTION     ON UPDATE NO ACTION) ENGINE = InnoDB;   -- ----------------------------------------------------- -- Table `mydb`.`base_calendars_generated` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`base_calendars_generated` (   `ID` INT NOT NULL AUTO_INCREMENT,   `id_based_calendar_generated` INT NULL,   `c_date` DATETIME NULL,   `star_hour` DECIMAL(4,2) NULL,   `end_hour` DECIMAL(4,2) NULL,   `working_day` SMALLINT(1) NOT NULL,   `day` SMALLINT(2) NULL,   `day_name` VARCHAR(50) NULL,   `week` SMALLINT(2) NULL,   `month` SMALLINT(2) NULL,   `month_name` VARCHAR(50) NULL,   `quarter` SMALLINT(1) NULL,   `year` SMALLINT(4) NULL,   `day_of_year` SMALLINT(3) NULL,   PRIMARY KEY (`ID`, `working_day`)) ENGINE = InnoDB;   -- ----------------------------------------------------- -- Table `mydb`.`base_calendars_settings` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`base_calendars_settings` (   `ID` INT NOT NULL AUTO_INCREMENT,   `name` VARCHAR(254) NOT NULL,   `is_blocked` SMALLINT(1) NOT NULL,   `is_default` SMALLINT(1) NOT NULL,   `start_date` DATETIME NULL,   `default_start_hour` DECIMAL(4,2) NOT NULL,   `default_end_hour` DECIMAL(4,2) NOT NULL,   `first_day` SMALLINT(1) NULL,   `user_id` INT NULL,   PRIMARY KEY (`ID`)) ENGINE = InnoDB;   -- ----------------------------------------------------- -- Table `mydb`.`resource_calendar_settings` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`resource_calendar_settings` (   `ID` INT NOT NULL AUTO_INCREMENT COMMENT 'primary key',   `user_id` VARCHAR(45) NULL COMMENT 'ID of employee',   `name` VARCHAR(50) NULL COMMENT 'employee name',   `surname` VARCHAR(100) NULL COMMENT 'employee surname',   `type_of_leave` VARCHAR(100) NULL COMMENT 'time off type: vacation, sick leave, absence, other',   `start_time` DATETIME NULL COMMENT 'time off start date',   `end_time` DATETIME NULL COMMENT 'time off end date',   `comments` VARCHAR(256) NULL COMMENT 'comment about time off',   PRIMARY KEY (`ID`)) ENGINE = InnoDB;   -- ----------------------------------------------------- -- Table `mydb`.`shift_calendar` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`shift_calendar` (   `ID` INT NOT NULL AUTO_INCREMENT,   `user_id` INT NULL COMMENT 'od of employee',   `name` VARCHAR(50) NULL COMMENT 'name of employee',   `surname` VARCHAR(100) NULL COMMENT 'surname of employee',   `start_time` DECIMAL(4,2) NULL COMMENT 'employee start time',   `end_time` DECIMAL(4,2) NULL COMMENT 'employe end time',   `operator_id` INT NOT NULL COMMENT 'Id of operator who made changes',   `working_date` DATETIME NULL COMMENT 'datetime when employee work',   `working_hours` DECIMAL(4,2) NULL COMMENT 'working hours per day',   `is_weekend` SMALLINT(2) NULL,   PRIMARY KEY (`ID`),   INDEX `holiday_idx` (`is_weekend` ASC) VISIBLE,   CONSTRAINT `holiday`     FOREIGN KEY (`is_weekend`)     REFERENCES `mydb`.`base_calendars_generated` (`working_day`)     ON DELETE NO ACTION     ON UPDATE NO ACTION) ENGINE = InnoDB;   SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; 

Want to run the following sql but i got error Error Code: 1005. Can’t create table mydb.shift_calendar (errno: 150 "Foreign key constraint is incorrectly formed") Dont understand where is problem column is_weekend and working_day have the same type smalint(1)

Disk space efficient foreign key index for insert intensive scientific database?

I’m working tuning a scientific database whose associated simulation is very insert intensive (i.e., run for a long time inserting data, execute summary query at the end). One of the tables is starting to cause some problems since the table size is 235 GB with index sizes of 261 GB, and the server only has 800 GB so we would like to free up a bit of space.

Currently there is one foreign key reference (integer data) that is stored as a clustered b-true. This has been good for the summary queries, but likely isn’t helping the disk space issues.

Is there a more disk efficient way of storing this foreign key index? Would it make sense to switch over to a hash index instead of the b-tree index?

Can a FOREIGN KEY be referring to the same table as the “source”?

I was sure that I had done this countless times, but I can’t find any previous use of me doing this. But it seems to make perfect sense, so I assume that it must be possible? However, the manual says:

A foreign key constraint specifies that the values in a column (or a group of columns) must match the values appearing in some row of another table.

Emphasis mine. Source: https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-FK

I have a table for bookkeeping, and whenever a transaction "corrects" or "updates" a previous one, I have a column called "corrects id" which is a bigint and, to me, makes perfect sense to have as a FOREIGN KEY as such:

..., FOREIGN KEY ("corrects id") REFERENCES bookkeeping (id) 

Note: "corrects id" is a column in the "bookkeeping" table, and id is the primary key (bigserial) of the same table.

Surely this is correct? So why does the manual say that it has to be another table? And why is the name "FOREIGN" keys if you can refer to the same table?

Best approach for extending a foreign relationship for an existing table

I’m not sure the title accurately reflects my question. I have an existing Rate table that has an identity column key and contains a [Rate] column. col1 + col2 + col3 don’t uniquely identify a row:

+--------+------+------+------+------+ | RateId | col1 | col2 | col3 | Rate | +--------+------+------+------+------+ 

I have a new table that needs to match a rate. The new table has 3 of the required columns to make a match but those columns will return a number of rows in the Rate table. To uniquely identify a rate for the new table I need to match on MaterialTypeId and UnitTypeId where UnitTypeId can be null.

+------------+------+------+------+----------------+------------+ | MaterialId | col1 | col2 | col3 | MaterialTypeId | UnitTypeId | +------------+------+------+------+----------------+------------+ 

What is the best approach to resolving this? I could add the two additional columns to the Rate table but that would not be relevant for the current uses of the Rate table, i.e. the two additional columns would be null for all existing rows.

I could introduce an intermediate table and join on MaterialTypeId & UnitTypeID which would return multiple rows and then join using the existing columns to uniquely identify the rate:

+--------+----------------+------------+ | RateId | MaterialTypeId | UnitTypeId | +--------+----------------+------------+ 

Is that the correct approach?

Foreign key references on column definitions are ignored. Feature, not a bug — why?

Good old references constrains. They work like a charm when defined at the table level.

create table foo (id int primary key);  create table bar (id int, foreign key(id) references foo(id)); insert into bar values (1); -- ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (...) 

But if you come from another ecosystem and are used to occasionally define foreign key constrains at the column level, this is what happens:

create table baz (id int references foo(id)); insert into baz values (1); -- happily takes a value that isn't there in foo select id from baz; -- 1 

What happens is that the references has been recognized, but ignored.

It turns out that this is not a bug. The MySQL documentation says they do it, and that’s all you need to know:

MySQL parses but ignores “inline REFERENCES specifications” (as defined in the SQL standard) where the references are defined as part of the column specification. MySQL accepts REFERENCES clauses only when specified as part of a separate FOREIGN KEY specification.

The MariaDB documentation is slightly more verbose on their rationale:

MariaDB accepts the REFERENCES clause in ALTER TABLE and CREATE TABLE statements, but that syntax does nothing. MariaDB simply parses it without returning any error or warning, for compatibility with other DBMS’s. However, only the syntax described below creates foreign keys.

Now what could be the use for this “feature” that helps “compatibility” with other DBMS — and the standard — by silently breaking the very purpose of the reference, while at the same time, correctly implementing it does not look like a big effort since foreign key constrains are indeed enforced when declared at the table level? And don’t tell me this cannot be fixed because people rely on the fact that foreign constrains can be broken when declared at the column level.

Please help me make sense out of this.

How can a Unicorn establish a foreign location as its own lair, when it’s already the lair of a Lich?

The party’s Cleric and Wizard have worked together to summon and bind a Unicorn to their side for a year and a day. Now they get to enjoy an intelligent mount with legendary actions, teleportation, and extra healing. It can even have a lair of its own.

The party has gone inside a Lich’s domain. Is there anything the party can have the Unicorn do so that it can establish this very same location as its own domain, such that it becomes the lair of both the Lich and the Unicorn? If so, what does that process look like?

As an important note, this particular Lich is very pointedly not interfering with the party’s preparations up until they meet. This campaign is more of a hack and slash, so there’s not really any deeper underlying reason as to why. What is important is that it doesn’t violate the rules.

Here’s the subcomponents of this question, as I see it:

  1. Can any creature turn any location into its lair?

  2. Can more than one creature treat the same location as their lair?

  3. What is the process for a creature turning an area into its lair?

I believe the answers to (1) and (2) are yes because there isn’t a specific rule that I know of that forbids this, so the main question here is (3). However, if you can cite a rule that shows the answer to (1) or (2) is a “no,” I believe that in this scenario it renders the succeeding questions moot, so that’s also an acceptable answer.

Importing foreign characters fails

Hi @Sven,
Two things today:

1. I need to replace keywords, anchors, and secondary anchors in some projects. I tried to do it with importing keywords and anchors but what I got in the projects after importing are ??????, ???????, ??????. The language is korean.
While I was typing this I thought of importing “Exported data fields” which seemed to be working. It places the anchor in {} instead of comas but I guess that’s fine.
2. After setting a “Folder with data files to use” how can I set it back to the default directory after I duplicate a project. I can’t delete anything in the data field.
Thanks!

Force create foreign key even if reference does not exist

I have two tables foo and bar, bar has column a that references foo.id as a foreign key. When creating table bar before foo, the error column "a" referenced in foreign key constraint does not exist is given. This is obvious and intended. However, the creation of these two tables are handled by two different entities that of which I cannot control. It’s guaranteed that no data will be added to either table until both are successfully created, but the creation itself may happen out-of-order. Is there a way to force the creation of bar even though foo does not exist?

Foreign Key Constraint does not exist ERROR

I keep getting this error in my psql database;

bikefacility=# ERROR:  syntax error at or near "c" bikefacility-# LINE 1: c bikefacility-#         ^ bikefacility-# bikefacility=# ALTER TABLE bicycle ADD CONSTRAINT fk_maintenance_contact_person FOREIGN KEY (maintenance_contact_person) REFERENCES maintenance(maintenance_contact_person); ERROR:  syntax error at or near "ERROR" LINE 1: ERROR:  syntax error at or near "c"         ^ bikefacility=# ERROR:  column "maintenance_contact_person" referenced in foreign key constraint does not exist bikefacility-# bikefacility=# ALTER TABLE bicycle ADD CONSTRAINT fk_rental_period FOREIGN KEY (rental_period) REFERENCES rental(rental_period); ERROR:  syntax error at or near "ERROR" LINE 1: ERROR:  column "maintenance_contact_person" referenced in fo...         ^ bikefacility=# ERROR:  column "rental_period" referenced in foreign key constraint does not exist bikefacility-# bikefacility=# ALTER TABLE bicycle ADD CONSTRAINT fk_terminal_id FOREIGN KEY (terminal_id) REFERENCES terminal(terminal_id); ERROR:  syntax error at or near "ERROR" LINE 1: ERROR:  column "rental_period" referenced in foreign key con...         ^ bikefacility=# ERROR:  column "terminal_id" referenced in foreign key constraint does not exist 

Here is the code I’m using

CREATE TABLE member (   member_id INTEGER PRIMARY KEY,   member_fname VARCHAR(15) NOT NULL,   member_lname VARCHAR(15) NOT NULL,   member_status VARCHAR(15) NOT NULL,   member_address VARCHAR(10) NOT NULL,   member_email VARCHAR(30) NOT NULL );  CREATE TABLE bicycle (   bicycle_id INTEGER PRIMARY KEY,   bicycle_brand VARCHAR(25) NOT NULL,   bicycle_model VARCHAR(25) NOT NULL,   bicycle_colour VARCHAR(15) NOT NULL,   bicycle_type VARCHAR(20) NOT NULL,   bicycle_size VARCHAR(10) NOT NULL,   bicycle_availability VARCHAR(20) NOT NULL  );  ALTER TABLE bicycle ADD CONSTRAINT fk_bicycle_pickup_date FOREIGN KEY (bicycle_pickup_date) REFERENCES rental(bicycle_pickup_date); ALTER TABLE bicycle ADD CONSTRAINT fk_maintenance_contact_person FOREIGN KEY (maintenance_contact_person) REFERENCES maintenance(maintenance_contact_person); ALTER TABLE bicycle ADD CONSTRAINT fk_terminal_id FOREIGN KEY (terminal_id) REFERENCES terminal(terminal_id); ALTER TABLE bicycle ADD CONSTRAINT fk_rental_period FOREIGN KEY (rental_period) REFERENCES rental(rental_period);  CREATE TABLE company (   company_id INTEGER PRIMARY KEY,   company_name VARCHAR(20) NOT NULL,   company_contact VARCHAR(50) NOT NULL,   company_address VARCHAR(50) NOT NULL );  CREATE TABLE sponsor (   sponsor_id INTEGER PRIMARY KEY,   sponsor_name VARCHAR(15) NOT NULL,   sponsor_contact VARCHAR(30) NOT NULL,   sponsor_period DATE NOT NULL,   sponsor_address VARCHAR(50) NOT NULL,   sponsor_fee DECIMAL (6, 2) NOT NULL );  ALTER TABLE sponsor ADD CONSTRAINT fk_company_name FOREIGN KEY (company_name) REFERENCES company(company_name); ALTER TABLE sponsor ADD CONSTRAINT fk_bicycle_availability FOREIGN KEY (bicycle_availability) REFERENCES bicycle(bicycle_availability);  CREATE TABLE terminal (   terminal_id INTEGER PRIMARY KEY,   terminal_address VARCHAR(50) NOT NULL,   terminal_minstorage VARCHAR(50) NOT NULL,   terminal_maxstorage VARCHAR(50) NOT NULL );  ALTER TABLE terminal ADD CONSTRAINT fk2_maintenance_contact_person FOREIGN KEY (maintenance_contact_person) REFERENCES maintenance(maintenance_contact_person); ALTER TABLE terminal ADD CONSTRAINT fk2_bicycle_availability FOREIGN KEY (bicycle_availability) REFERENCES bicycle(bicycle_availability); ALTER TABLE terminal ADD CONSTRAINT fk_bicycle_id FOREIGN KEY (bicycle_id) REFERENCES bicycle(bicycle_id);  CREATE TABLE rental (   rental_no INTEGER PRIMARY KEY,   rental_period DATE NOT NULL,   bicycle_pickup_date DATE NOT NULL );  ALTER TABLE rental ADD CONSTRAINT fk2_bicycle_id FOREIGN KEY (bicycle_id) REFERENCES bicycle(bicycle_id); ALTER TABLE rental ADD CONSTRAINT fk2_terminal_id FOREIGN KEY (terminal_id) REFERENCES terminal(terminal_id);   CREATE TABLE bill (   bill_id INTEGER PRIMARY KEY,   bill_date DATE NOT NULL,   bill_total_amount DECIMAL(6, 2) NOT NULL,   payment_no INTEGER NOT NULL,   payment_date DATE NOT NULL,   payment_method VARCHAR(15) NOT NULL );  ALTER TABLE bill ADD CONSTRAINT fk3_bicycle_pickup_date FOREIGN KEY (bicycle_pickup_date) REFERENCES rental(bicycle_pickup_date);   CREATE TABLE maintenance (   maintenance_id INTEGER PRIMARY KEY,   maintenance_contact_person VARCHAR(15) NOT NULL,   maintenance_phone_number INTEGER NOT NULL,   maintenance_fee DECIMAL(6, 2) NOT NULL );  ALTER TABLE maintenance ADD CONSTRAINT fk3_bicycle_id FOREIGN KEY (bicycle_id) REFERENCES bicycle(bicycle_id); ALTER TABLE maintenance ADD CONSTRAINT  fk3_terminal_id FOREIGN KEY (terminal_id) REFERENCES terminal(terminal_id);    CREATE TABLE visitor (   visitor_id INTEGER PRIMARY KEY,   daypass_no INTEGER NOT NULL,   daypass_date DATE NOT NULL,   visitor_phone_number INTEGER NOT NULL,     visitor_fname VARCHAR(15) NOT NULL,   visitor_email VARCHAR(40) NOT NULL,   visitor_address VARCHAR(80) NOT NULL );  ALTER TABLE visitor ADD CONSTRAINT fk4_bicycle_id FOREIGN KEY (bicycle_id) REFERENCES bicycle(bicycle_id); ALTER TABLE visitor ADD CONSTRAINT fk_bill_id FOREIGN KEY (bill_id) REFERENCES bill(bill_id);  CREATE TABLE invoice (   invoice_id INTEGER PRIMARY KEY );  ALTER TABLE invoice ADD CONSTRAINT fk_customer_type FOREIGN KEY (customer_type) REFERENCES customer(customer_type); ALTER TABLE invoice ADD CONSTRAINT fk_rental_no FOREIGN KEY (rental_no) REFERENCES rental(rental_no); ALTER TABLE invoice ADD CONSTRAINT fk2_rental_period FOREIGN KEY (rental_period) REFERENCES rental(rental_period); ALTER TABLE invoice ADD CONSTRAINT fk5_bicycle_id FOREIGN KEY (bicycle_id) REFERENCES bicycle(bicycle_id); ALTER TABLE invoice ADD CONSTRAINT fk_bicycle_brand FOREIGN KEY (bicycle_brand) REFERENCES bicycle(bicycle_brand); ALTER TABLE invoice ADD CONSTRAINT fk_bicycle_model FOREIGN KEY (bicycle_model) REFERENCES bicycle(bicycle_model); ALTER TABLE invoice ADD CONSTRAINT fk_bicycle_size FOREIGN KEY (bicycle_size) REFERENCES bicycle(bicycle_size); ALTER TABLE invoice ADD CONSTRAINT fk2_bill_id FOREIGN KEY (bill_id) REFERENCES bill(bill_id); ALTER TABLE invoice ADD CONSTRAINT fk_bill_date FOREIGN KEY (bill_date) REFERENCES bill(bill_date); ALTER TABLE invoice ADD CONSTRAINT fk_bill_total_amount FOREIGN KEY (bill_total_amount) REFERENCES bill(bill_total_amount);   CREATE TABLE customer ( customer_id INTEGER PRIMARY KEY ); ALTER TABLE customer ADD CONSTRAINT fk_member_id FOREIGN KEY (member_id) REFERENCES member(member_id); ALTER TABLE customer ADD CONSTRAINT fk_visitor_id FOREIGN KEY (visitor_id) REFERENCES visitor(visitor_id); ALTER TABLE customer ADD CONSTRAINT fk_member_status FOREIGN KEY (member_status) REFERENCES member(member_status); ALTER TABLE customer ADD CONSTRAINT fk3_bill_id FOREIGN KEY (bill_id) REFERENCES bill(bill_id); ALTER TABLE customer ADD CONSTRAINT fk6_bicycle_id FOREIGN KEY (bicycle_id) REFERENCES bicycle(bicycle_id);  

I have a feeling the error ” foreign key does not exist ” is going to show up a lot. Could someone help me out, please? What’s wrong with my code? Thank you in advance!