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.

update matching column value in anywhere in database sqlserver

I have assetid column in multiple table in my Database and Asset(main) table

I want to update all occurences of tables in all occurences of inserts in database assetid column where value has 1020 to 1

update AdminInterests set assetid = 1 where 1020 

I just want to how to apply this update for entire database all tables with one query

Psycopg2 using value SET before assign to other column

I have a table with columns as below: (id, col1, col2, col3, col4). I need to insert new row to this table, with given data is a dict: data = {'id': 1, 'col1': 1, 'col2':1, 'col3':1, 'col4': 1} for example.

I want to use the query below to insert data into table if id not existed, or update the table if id existed:

query =  '''     INSERT INTO tablename (id, col1, col2, col3, col4)     VALUES (%(id)s, %(col1)s, %(col2)s, %(col3)s, %(col4)s)     ON CONFLICT (id) DO UPDATE SET     col2 = tablename.col2 + 1;     col3 = col2/100 ''' result = cur.execute(query, data) 

In the code above, I want to assign col3’s value to (value of col2 calculated before)/100. How can I do that?

List all column names for multi-joined table

I have a slightly more complicated join:

SELECT person.given_name, person.family_name, person.age, person.sex, person.state,        person.zip, person.street, person.residential_number,        c1.name as citizen_of_country,        c2.name as hospitalization_country,        c3.name as infection_country  FROM patients      JOIN person on patients.person_id = person.id     JOIN country c1 on person."citizenOf_country_id" = c1.id     JOIN country c2 on patients.hospitalized_in_country_id = c2.id     JOIN country c3 on patients.infected_in_country_id = c3.id; 

.. and I’d like to somehow get the name of all columns in this new table. Based on some answers I found, I tried

SELECT DISTINCT column_name FROM information_schema.columns WHERE table_schema = 'public' AND [code]; 

Where [code] is the block above.. But it didn’t work, giving the error “Subquery must return only one column”. I’m new to databases so I’m not sure how to handle this correctly.

Find the nearest time from another table and add column

Please help. I need to create a view of Assembly details and when they have gone through a particular machine. I have two tables. Table 1 has the main assembly data and Table 2 has the machine data.

The final query has all Table 1 fields and the Machine field of Table 2 where the BuildDate (Table 1) is = to the WaveDate (Table 2) and the Wavetime (Table 2) is less than and nearest to the BuildTime (Table 1).

Hopefully the pictures make more sense than my description :/ Any help is greatly appreciated. Many thanks

enter image description here

Matrices in which row $i$ and column $i$ contain $1,\ldots,2n-1$ [closed]

For which values of $ n$ is there an $ n\times n$ matrix whose entries are $ 1,\ldots,2n-1$ such that for each $ 1 \leq i \leq n$ , the union of the $ i$ th row and $ i$ th column contains all numbers from $ 1$ to $ 2n-1$ ?

I know that this is possible whenever $ n$ is a power of 2. For example, here is a solution for $ n = 4$ : $ $ \begin{matrix} 1&2&3&4 \ 5&1&4&3 \ 6&7&1&2 \ 7&6&5&1 \end{matrix} $ $

Find the minimal subset of rows of some matrix such that the sum of each column over this rows exceeds some threshold

Let $ A$ be a an $ n\times m$ real valued matrix. The problem is to find the minimal subset $ I$ of rows (if there is any) such that the sum of each column $ j$ over the corresponding rows exceeds some threshold $ t_j$ , i.e. $ \sum_{i\in I}A[i,j]>t_j$ for all $ j\in\{1,\dots m\}$ .

Or, stated as optimization problem:

Let $ A\in\mathbb{R}^{n\times m}, t\in\mathbb{R}^m$ . Now solve \begin{align}\min_{\xi\in\{0,1\}^n}&\sum_{i=1}^n\xi_i\\text{s.t.}&\,A^\top\xi>t\,.\end{align}

Actually, i would need a solution only for $ m=2$ , but the general might be interesting too.

SQL: How to convert rows to column for every n’th row by ID

I’m trying to convert rows to columns.

I have the following columns: user_id, meta_key, meta_value

i have the following rows: 'first_name', 'last_name', 'stillingsbetegnelse'

Right now i can generate the rows and coloumns sorted and from a sepcific user_id. In my case it’s from 15. This is with the following querery

> SELECT user_id, meta_key, meta_value FROM clk_f76242643e_wp_usermeta > ORDER BY user_id DESC; where meta_key IN ('first_name', 'last_name', > 'stillingsbetegnelse') AND user_id > 15; 

And the result is:

user_id     meta_key                meta_value 16          first_name              Laxgdfds 16          last_name               Narentdsaa 16          stillingsbetegnelse     Andet 18          first_name              Riedsa 18          last_name               Joe 18          stillingsbetegnelse     Andet 

(all information is anonymized)

How do i get the following output

first_name    last_name         stillingsbetegnelse Laxgdfds      Narentdsaa        Andet Riedsa        Joe               Andet 

I have tried methods with Pivot, but i’m getting an error

    select         p.*     from clk_f76242643e_wp_usermeta     pivot(         max([meta_value])         for [meta_key] in ([first_name], [last_name], [stillingsbetegnelse]) ) as p