Questions tagged [constraint]
A declarative mechanism such as a check or foreign key that enforces some data integrity rule in a database.
502
questions
0
votes
1
answer
25
views
How can I find all violations of a proposed foreign key on all columns with a specific name?
Let's say I have multiple tables with a column foobar. And I wish to make to make all these columns reference foobar.id as a FOREIGN KEY. The problem is I know there are violations in this schema from ...
- 64.2k
1
vote
1
answer
91
views
Should unique filtered indexes include their filtering column?
Context
Suppose that you have a unique filtered index on a table. Said index exists only to enforce a constraint. A classic example of such an index would be one making sure that a non-nullable bit ...
- 685
2
votes
3
answers
593
views
Are unique filtered indexes considered an antipattern for enforcing constraints?
I have a table, such as the following. Let's called it Fools.
FoolID
FoolValue
IsActiveFoolValue
1
Ultra Foolish
0
1
Super Foolish
1
2
Quite Foolish
1
A business rule is that for each FoolID, we ...
- 685
0
votes
1
answer
117
views
What is best way to change primary key of large table with minimal downtime?
Due to latch contention on large table with more than 1 billion records, I would like to reorder primary key definition from big int column to composite primary key. I know of two ways of doing this......
- 21
0
votes
1
answer
38
views
primary key referened by only single foreign key of another table among multiple tables that can reference it
Let's say I have table Account that contains username and password of users to login:-
account (
account_id BIGINT PRIMARY KEY,
username VARCHAR(30),
password VARCHAR(30)
)
-- I have two ...
- 103
3
votes
2
answers
289
views
Why does MySQL generate a `key` statement in DDL for composite primary keys?
I am using MySQL version 8.3.0.
I can create a “many to many” relationship between persons and teams tables with the following SQL statements:
create table persons (
id bigint not null,
last_name ...
- 356
3
votes
3
answers
481
views
Enforce non-unique combination of columns, where combination is required
I have the following (and more non-related) columns in my MSSQL database table: Id, ImageUrl and ImageId.
The combination of ImageUrl and ImageId can occur multiple times. ImageUrl belongs to ImageId ...
- 141
1
vote
1
answer
55
views
Since SQL-92, what new kinds of RDBMS constraints have been invented and which RDBMS implement them?
Backstory: I'm having to do some computer archeology for a data integration project, involving getting data out a multiple-decades-old old Progress, now OpenEdge, database application for AS/400.
...
- 593
1
vote
1
answer
52
views
Unique constraint for single structure that simulates relations between tables
Imagine I have several tables in SQL Server 2016:
Cat
Cat carrier
Cat transit box
Cat car
Every object can be stored in higher level, but not necessarily directly one above with N-to-1. So a cat can ...
- 11
0
votes
1
answer
36
views
Lookup tables: check by type id or type name?
TLDR: Should I check an entity's type by lookup_table.id or lookup_table.type_name?
I've learned that lookup tables are almost always preferrable to Enums due to their flexibility. However, I have ...
- 1
1
vote
2
answers
66
views
Select unique rows based on condition stored in another table
I want to select rows based on conditions that is fetched from the database. The tables structure are mentioned below.
# Questions Table:
╔════════════╦═══════════════╦════════╗
║ id ║ ...
- 11
0
votes
2
answers
107
views
SSIS Package hanging with two Constraints pointed at one Task: Can you have multiple constraints pointing to one Task?
In the example I pasted in the image, I have 5 Tasks. I don't think it's important what kind of Tasks I'm using--but I could be wrong, of course--because the main issue seems to be how the constraints ...
- 275
0
votes
1
answer
31
views
How do I write sensible FOREIGN KEY CONSTRAINTS if the PRIMARY KEYS from two tables can reference multiple of the other table's PK
I am setting up a database for a university and I'm struggling to setup sensible FOREIGN KEY CONSTRAINTS (and even tables) because I have two tables in which the PRIMARY KEYS of each can relate to ...
0
votes
1
answer
81
views
SQL Server using table's Primary Key as part of Foreign Key
The situation is I have a Tenant table, and a tenant based status lookup table. Each with their own autonumber Primary keys.
Now I want to add a column to the Tenant table for a default Status that ...
- 15
0
votes
1
answer
49
views
How I can place further constraints upon foreign key?
In sql server I have the following table:
CREATE TABLE mydb.dbo.files (
id bigint IDENTITY(1,1) NOT NULL,
name nvarchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
folder bigint ...
0
votes
0
answers
71
views
How to replace (drop and rename) a table without triggering constraints in postgres
I have a table (A) that I need to reprocess from time to time.
Doing that using update or upsert is really slow, so, to make things faster, I decided to create a new table (B), copy all the rows from ...
- 135
2
votes
2
answers
3k
views
CASCADE DELETE per statement
Can I execute something like this in Postgresql, that is, execute CASCADE DELETE for just one statement?
DELETE CASCADE FROM foo WHERE bar = 'baz';
My goal is to delete not just the selected record(s)...
3
votes
1
answer
87
views
Enforce unique constraint without denormalization
Option 1
I have a database schema that looks something like this:
https://dbfiddle.uk/JKCTjadD
A Task belongs to a Service (like in ECS)
A Task can be reassigned to another Service.
TaskConfig tracks ...
- 133
0
votes
1
answer
42
views
Is commit of first insert necessary if second insert has constraint depedent on first insert?
Using MariaDB I am running an insert on one table and then trying to run another insert on second table. Due to a constraint the second insert should only succeed if the first one was successful.
If I ...
- 103
1
vote
1
answer
156
views
Lazily Evaluate Insert Arguments If Constraint Violated
create table a (
b text not null unique,
c text not null
);
-- Sleeps for 1 second as expected.
insert into a (b, c) values ('d', pg_sleep(1));
-- Sleeps for 1 second even though record is ...
- 113
1
vote
1
answer
200
views
Why does VALIDATE CONSTRAINT on PG 14.7 take a ShareLock?
I'm following this answer to add a NOT NULL constraint to an existing DB table with ~100M rows. However, when I try running the backend process takes out a ShareLock.
ALTER TABLE mytable VALIDATE ...
- 305
1
vote
0
answers
95
views
Foreign key constraint wrongly gives the error in mariadb
We are using AWS RDS for mariadb.
There are several tables out of which I am mentioning two which are causing the issue:
Table 1: PRODUCT_OFFERING
Table 2: CHARACTERISTIC
Related by foreign key as ...
- 111
1
vote
1
answer
563
views
Slow deletes in Postgresql with foreign key constraints [duplicate]
I have a number of tables relating to an entity and delete them in a transaction to make sure all are removed simultaneously:
BEGIN;
SET CONSTRAINTS ALL DEFERRED;
delete from table1 where entity_id = ...
- 21
0
votes
2
answers
240
views
DATE Constraint is ignored on insert
I must be missing something somewhere..
Azure SQL
DispatchedOn is a DateTime column
ALTER TABLE [table] WITH CHECK ADD CONSTRAINT [CK_ReportDate6MonthRollGreater] CHECK ((CONVERT([date],[...
- 187
5
votes
1
answer
217
views
Single Predicate Check Constraint Gives Constant Scan but Two Predicate Constraint does not
I can create the following constraint on the AdventureWorks table Person.Person:
ALTER TABLE Person.Person ADD CONSTRAINT ConstantScan CHECK (LastName <> N'Doesn''t Exist')
This tells SQL Server ...
- 2,033
0
votes
1
answer
60
views
Set Operation Exclusion Constraint for Multiple Tables All at Once
In multiple of my tables, I have a deleted column for tracking things which have been deleted, instead of directly deleting them from the database.
As such, I would like to create a constraint for ...
- 121
1
vote
2
answers
58
views
How to enforce entity existence across N-tables - postgres
Let's say we decided to split user table in two, one will have data related to authentication, another basic user description:
user_table
user_id | name
1 | Max
2 | Alex
3 | ...
- 135
0
votes
1
answer
680
views
Making use of ENUM in CONSTRAINT
I am creating a PostGIS database in which a lot of schemas should have the same constraints. Meaning in the future, similar tables should have the same limitations of values entered.
My constraint ...
- 3
0
votes
1
answer
31
views
Why constraint fails on preventing the insert statement execution?
I have the following table:
-- laravel.spies definition
CREATE TABLE `spies` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`surname` varchar(255) NOT NULL,
...
1
vote
4
answers
4k
views
Change the default value of a string column to real uuid()
When I use show create table Posts I see this column:
`Slug` varchar(400) NOT NULL DEFAULT 'uuid()'
This means that the default value is a string. I want the default string to be a real uuid() ...
- 159
1
vote
2
answers
1k
views
Foreign key constraint not showing in sysobjects but cannot be created due to conflict with itself
I have an Microsoft SQL Server 2019 Enterprise Edition database where I had a relationship between two tables: Properties and Photos with Properties being the primary key and Photos being the foreign ...
- 143
1
vote
1
answer
77
views
is there unix timestamp range function in PostgreSQL 13
I have a table that stored user subscribe information, now I want to add a constraint to make the user did not subscribe the product multiple times in the same peroid of time. What I am tried to do ...
- 815
1
vote
1
answer
189
views
Constraint checking that foreign key matches with column in same table as primary key
I have the following SQL schemas.
-- name is primary key
Programs (name, abbr)
-- idnr is primary key
Students (idnr, name, login, program)
program → Programs.name
-- name is primary key
...
- 113
0
votes
1
answer
29
views
how to import several columns from diferent tables/databases into one
To be a bit clear I have 3 different tables with several columns each I want to kind of create a final table to help me explain myself better
Table A:
id
a2
a3
a4
a5
a6
1
11
12
13
14
15
Table B:
...
0
votes
1
answer
2k
views
best and quickest way to rename the primary key of a table
when I need to rename a table and consequently its primary key (and all other constraints) as well I use the following simple example script:
if object_id('dbo.Radhe1',N'U') is not null
drop table ...
- 16.4k
6
votes
2
answers
821
views
Make two columns unique both ways
I'm creating a database which stores football matches.
Each round, any given football club may only play once (home or away).
For example, after the insert of
INSERT INTO Match (Round, Home, Away) ...
- 63
4
votes
1
answer
718
views
UPSERT based on unique combination of (INTEGER, NULL), (NULL, INTEGER)
My table, for simplicity, looks like this:
id | foreign_key_1 | foreign_key_2 | value
As clear from the names, the fields foreign_key_1 and foreign_key_2 refer to PKs in two other tables.
The tricky ...
- 209
0
votes
1
answer
474
views
In PostgreSQL, what to do after VALIDATE CONSTRAINT succeeds on a constraint marked with NO VALID?
So, I get the rough gist of changing constraints on existing columns with PostgreSQL:
Use NO VALID to ensure the new constraint does not lock the table.
Migrate the old data at your leisure.
Run ...
- 133
1
vote
1
answer
314
views
How can I load a custom format dump from pg_dump into an RDS while ignoring constraint checks during the import?
I have a number of constraints:
I have binary custom format dump from Heroku that I need to load via pg_restore.
I need to load this onto an AWS RDS instance, where I don't have full superuser (only ...
- 113
3
votes
2
answers
936
views
Implement foreign key constraint when there are several parent tables
It might be the case that my initial design is flawed, but let's start from there and see whether there is a good enough approach.
I have two entities, say, A and B that are very similar but not ...
- 209
4
votes
2
answers
626
views
Unique Constraint on 2 columns or single column and null exists
Within a multi-tenant database we have a table that contains some system values and some tenant values defined as follows.
CREATE TABLE [dbo].[ItemTypes] (
[Id] INT IDENTITY ...
- 145
0
votes
1
answer
109
views
Make sure that such a field combination exists in another table when inserting
Table A:
id | name | user_id
Table B:
id | type
Table A_B
id | table_a_id | table_b_id | user_id
Table A_B is a standard linking table for a many-to-many relationship except that probably using a ...
- 209
2
votes
2
answers
1k
views
Self-referential record, potentially referencing the same row, with a uuid pkey and a not null and foreign key constraint?
Scenario
A table holds records that represent a chain (or, more accurate yet, any number of independent chains, respectively).
In addition to referencing the direct ancestor in the chain, a record ...
- 123
0
votes
1
answer
868
views
DEFERRABLE not working as expected in Postgres
My app has the requirement that every table has a "created_by" column that references to the id column of user table. Even the user table. That means the created_by column would be a ...
- 165
4
votes
1
answer
639
views
When exactly are IMMEDIATE constraints checked and what exactly is a "statement" in Postgres?
I'm currently confused on the exact timing of IMMEDIATE constraint checks. Hopefully, the following example captures my confusion:
create table a (
id int primary key
);
create table b (
id int ...
- 63
3
votes
1
answer
2k
views
Postgres constraints: EXCLUDE (name WITH =) vs partial unique index
I am refactoring my database constraint for such a table:
CREATE TABLE products (
name text NOT NULL,
status text NOT NULL
);
A former engineer working on the project had such a constraint:
ALTER ...
- 131
3
votes
1
answer
2k
views
Constraint for 2 foreign keys to have same secondary column value
Suppose I have a table such as Users that has a id and a company_id
I want to make a 2nd table that has pairs of user id's ex: (primary_key_id, user1_id, user2_id).
The user id's must be in the same ...
- 143
5
votes
1
answer
726
views
What is the current state (2022) of using UDFs in a CHECK constraint in SQL Server?
SQL Server allows the use of User-Defined Functions (UDFs) in CHECK constraints - which has a number of use-cases - which I won't get into - but I was recently faced with implementing a non-trivial ...
- 593
0
votes
0
answers
13
views
Is it correct to say that such constraint is called: emptiness integrity constraint?
I'm taking a database course, and I'm currently learning about integrity constraints. What happens is that in the teacher's material there is a constraint that is called: emptiness integrity ...
- 1
0
votes
1
answer
80
views
Impose limits on number of records stored in a table
Assume we have a table with following schema:
favorites{
UserID
CategoryID
}
How can I enforce a policy which ensures each users has at least 3 categories and at most 5 categories. Is it ...
- 101