Questions tagged [constraint]
A declarative mechanism such as a check or foreign key that enforces some data integrity rule in a database.
505 questions
0
votes
0
answers
29
views
ORA-02292: integrity constraint (CITY_COUNTRY_FK) violated - child record found
I am trying to make my all table columns in lowercase but the following error appears:
SQL> UPDATE COUNTRY SET country_code = LOWER(country_code);
UPDATE COUNTRY SET country_code = LOWER(...
- 17
0
votes
0
answers
61
views
Is it frowned upon to have check constraints only applicable to a subset of rows?
At my place of work we are running into an issue, we have many parameters stored in a table like so.
ModelParameterValueID
ModelParameterID
ModelID
CurrentValue
Updated Date
187883
81
10849
TEMPSTR
...
- 1
0
votes
2
answers
35
views
How to only allow a boolean to be true if another boolean in the same row is true in MariaDB?
I have two boolean columns in my table. One called IsUnlisted and one called IsPrivate. I only want to allow IsPrivate to be 1 (TRUE) if IsUnlisted is 1 (TRUE) as well using a check constraint. I am ...
- 13
0
votes
1
answer
31
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.7k
1
vote
1
answer
103
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 ...
- 801
2
votes
3
answers
661
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 ...
- 801
0
votes
0
answers
193
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
41
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
299
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
498
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
62
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.
...
- 603
1
vote
1
answer
60
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
41
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
89
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
180
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
34
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
87
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
101
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
4k
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)...
- 105
3
votes
1
answer
89
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
51
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
161
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
241
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
114
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
741
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
273
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
223
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,105
0
votes
1
answer
63
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
72
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
789
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
35
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
82
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 ...
- 825
1
vote
1
answer
204
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
32
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.6k
6
votes
2
answers
938
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
771
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
521
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
370
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
989
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
648
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
154
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
923
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
651
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