Skip to main content
31 votes

Constraint - one boolean row is true, all other rows false

Variant 1 Since all you need is a single row with standard = true, set standard to null in all other rows. Then a plain UNIQUE constraint works, since null values do not violate it: CREATE TABLE ...
Erwin Brandstetter's user avatar
30 votes

MATCH FULL vs MATCH SIMPLE in foreign key constraints

FULL vs SIMPLE vs PARTIAL While the chosen answer is correct, if this is new to you, you may want to see it with code -- I think it's easier to grok that way. -- one row with (1,1) CREATE TABLE foo (...
Evan Carroll's user avatar
  • 63.7k
21 votes

Constraint - one boolean row is true, all other rows false

You can use a Filtered index create table test ( id int primary key, foo bool ); CREATE UNIQUE INDEX only_one_row_with_column_true_uix ON test (foo) WHERE (foo); --> where foo is ...
McNets's user avatar
  • 23.8k
14 votes

DELETE statement conflicted with the REFERENCE constraint

That is the whole point of foreign key constraints: they stop you deleting data that is referred to elsewhere in order to maintain referential integrity. There are two options: Delete the rows from ...
David Spillett's user avatar
10 votes
Accepted

the REFERENCES privilege is only about creating a foreign key constraint? Practical use cases?

If you have the ability to create a foreign key constraint, you are actually getting permission to check for the existence/non-existence of a certain value in a certain column (or set of columns) of a ...
joanolo's user avatar
  • 13.5k
7 votes
Accepted

Is having relationships between separate databases bad practice?

The only issue is that there isn't really a convenient way to enforce these relationships. You can use a boatload of triggers, or store the master table in each instance database and simply expose all ...
Aaron Bertrand's user avatar
7 votes

the REFERENCES privilege is only about creating a foreign key constraint? Practical use cases?

What joanolo describes is a valid consideration. A user can achieve partial SELECT privileges on the referenced table if (s)he is allowed to create a FK reference. More importantly, a user that can ...
Erwin Brandstetter's user avatar
6 votes
Accepted

Determinism inside an INSTEAD OF trigger

I thought about using ROW_NUMBER() in the trigger as a sort of pseudo-column... Stop thinking this. There is no guarantee of row order in the inserted or deleted pseudo-tables. You may observe it "...
Paul White's user avatar
  • 86.4k
6 votes
Accepted

Complex Delete Cascade Including Self-Referencing Table

I think I have captured what you need in this basic design: db<>fiddle ElementBase Self-fk for the hierarchy: CREATE TABLE dbo.ElementBase ( id integer NOT NULL, parent_id integer NOT ...
Paul White's user avatar
  • 86.4k
5 votes
Accepted

Referential integrity when two FK columns need to match themselves in the same table?

You need two things: to add an extra foreign key from Book that references Row. to modify the foreign key from Book to Shelf so it includes RowID and becomes a composite foreign key: (RowID, ShelfID)...
ypercubeᵀᴹ's user avatar
4 votes
Accepted

Deleting would-be orphans when creating FK in Postgres

As has been suggested in comments, the standard tool pg_dump might serve you better. It takes a consistent snapshot and preserves referential integrity during backup automatically - given the source ...
Erwin Brandstetter's user avatar
4 votes

the REFERENCES privilege is only about creating a foreign key constraint? Practical use cases?

To extend on Erwin's answer, there's a third case why the creation of foreign key constraints may require special permissions: a foreign key grants users of the table the ability to test for ...
Bergi's user avatar
  • 514
4 votes

Foreign key constraint on array member?

I just made something similar for a colleague. Essentially I made a hidden table that contained one row for each (user,role) pair with suitable constraints. The user table was then a view of the ...
Max Murphy's user avatar
4 votes

Referential integrity when two FK columns need to match themselves in the same table?

In your question, you have asked (among other things): "Is there a better design method for this?" I usually start off by drawing an ERD, and only if this has been mulled over and discussed and re-...
stefan's user avatar
  • 2,507
4 votes
Accepted

Inconsistency in Relational Databases

To directly answer your questions: "Do relational databases ensure consistency in reads?" A: In short, when the ACID principals, and specifically the Consistency part, were defined, this ...
J.D.'s user avatar
  • 38.1k
3 votes
Accepted

Preferred design to avoid circular/multiple update paths

The solution I've seen has been to remove the foreign key constraint, and to maintain relational integrity via triggers. On the foreign (or "child") table, you need INSERT and UPDATE triggers. if the ...
RDFozz's user avatar
  • 11.6k
3 votes
Accepted

How do triggers and check constraints relate to maitaining database rules

You haven't specified a tag in your question so I'll assume that you're asking a generic question despite the SQLite reference (if you're asking a SQLite-specific question, please update your tags). ...
Justin Cave's user avatar
  • 20.3k
3 votes
Accepted

Make a boolean column unique per group

The superior implementation is to save the default choice with the row in the parent table. Then a new default replaces the old one automatically. No trigger required, no chance to break referential ...
Erwin Brandstetter's user avatar
3 votes
Accepted

DB Design. Tracking history without losing referential integrity

This is a long-standing problem with lots of awkward solutions like the ones you referenced. The best ones still leave the problem of referential integrity. How do you reference an entity that changes ...
TommCatt's user avatar
  • 2,139
3 votes

How to implement a 'default' flag that can only be set on a single row

Here's a solution for MySQL and MariaDB using virtual columns that's a bit more elegant. It requires MySQL >= 5.7.6 or MariaDB >= 5.2: MariaDB [db]> create table foo(bar varchar(255), chk boolean);...
3 votes
Accepted

FK to table with non-unique values via a on commit refreshing materialized view?

You can still use GROUP BY. create table t1 (c1 varchar2(3), c2 number); insert into t1 values ('EUR', 100); insert into t1 values ('EUR', 200); insert into t1 values ('USD', 150); commit; select * ...
Balazs Papp's user avatar
  • 40.8k
3 votes
Accepted

Enforcing existence of records while avoiding circular references

I think that your problem could be solved by imposing a new Foreign Key constraints in your tables: User (UserId, UserOther) PK(UserId) Customer (CustomerId, CustomerOther, UserAdminId) PK(CustomerId)...
Renzo's user avatar
  • 4,080
3 votes
Accepted

Keeping reference in an array format

Your suggested design violates first normal form, which states that every attribute must be atomic. If you store multiple items as a CSV string, you are going to experience many challenges querying it ...
SQLRaptor's user avatar
  • 4,098
3 votes
Accepted

Referential Integrity - Indirect Foreign Key In "Depth"

I personally don't like the idea of pushing the Job.ID value on all the other child tables Why not? The obvious solution is to make JobID the leading PK/clustered index column on all the child ...
David Browne - Microsoft's user avatar
3 votes
Accepted

Large hexadecimal PRIMARY KEY - how to index substrings of the PK field?

<TL;DR> Regular expressions can be a very powerful tool for various data storage, manipulation and indexing requirements - regexes' capabilities in conjunction with GENERATED columns can be ...
Vérace's user avatar
  • 30.2k
3 votes

Inconsistency in Relational Databases

I'll try to answer only 1: When the data is distributed across multiple servers (ex 1 master- n slaves configuration) Do relational databases ensure consistency in reads? I mean a committed read is ...
2 votes

Many to Many and Weak Entities

Unfortunately I don't have enough rep to comment on ypercubeᵀᴹ's answer, so I'll post an alternate answer instead - I agree with that answer in general but think the primary key and unique contraints ...
Gord Stephen's user avatar
2 votes

DB Design. Tracking history without losing referential integrity

Add an IsDeleted bit field to the table and set that to TRUE when you delete the row, and have a rule in the stored procedure or application code to not display IsDeleted rows. That way the data isn't ...
Randolph West's user avatar
2 votes

Optional Parent-Child relationship

Just make the Tour optional? The below code is for SQL Server, but you should be able to port this to other flavors of SQL. Essentially, the TourID column can be nullable, indicating the Gig is not ...
Hannah Vernon's user avatar
  • 70.2k
2 votes

DELETE statement conflicted with the REFERENCE constraint

You can get IDs to delete only once, store them in temporary table and use to delete operations. Then you have better control what are you deleting. This operation shouldn't fail: SELECT sa.ID INTO #...
Paweł Tajs's user avatar
  • 1,353

Only top scored, non community-wiki answers of a minimum length are eligible