Questions tagged [constraint]
A declarative mechanism such as a check or foreign key that enforces some data integrity rule in a database.
493
questions
1
vote
2
answers
31
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 ║ ...
0
votes
0
answers
25
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 ...
0
votes
1
answer
24
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
65
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 ...
0
votes
1
answer
45
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
38
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 ...
2
votes
2
answers
453
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
81
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 ...
0
votes
1
answer
35
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 ...
1
vote
1
answer
141
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 ...
1
vote
1
answer
134
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 ...
0
votes
0
answers
44
views
EER Diagramming - Are unlabeled specialization branches overlapping or disjoint by default?
My understanding is when documenting specialization constraints (i.e. Disjointness Constraint) in EER diagrams you label the subclass branches as "o" for overlapping and "d" for ...
1
vote
0
answers
73
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 ...
0
votes
1
answer
245
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 = ...
0
votes
2
answers
155
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],[...
5
votes
1
answer
201
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 ...
0
votes
1
answer
46
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 ...
1
vote
2
answers
50
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 | ...
0
votes
1
answer
349
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 ...
0
votes
1
answer
26
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,
...
0
votes
3
answers
2k
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() ...
1
vote
2
answers
656
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 ...
1
vote
1
answer
61
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 ...
1
vote
1
answer
151
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
...
0
votes
1
answer
26
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
1k
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 ...
6
votes
2
answers
577
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) ...
4
votes
1
answer
602
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 ...
0
votes
1
answer
376
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 ...
1
vote
1
answer
236
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 ...
3
votes
2
answers
665
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 ...
4
votes
2
answers
550
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 ...
0
votes
1
answer
64
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 ...
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 ...
0
votes
1
answer
700
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 ...
4
votes
1
answer
627
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 ...
1
vote
0
answers
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 ...
2
votes
1
answer
1k
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 ...
5
votes
1
answer
625
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 ...
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 ...
0
votes
1
answer
62
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 ...
3
votes
2
answers
2k
views
Is there a way to add INCLUDE for a UNIQUE NONCLUSTERED index?
we have a table with 4 columns ( example ):
create TABLE [dbo].[myTable]
(
[Id] BIGINT NOT NULL,
[Id2] SMALLINT NOT NULL,
[Id3] SMALLINT NOT NULL,
[IdUnique] ...
0
votes
3
answers
144
views
Constraint to force pair of IDs
I would like a constraint to force a pair of IDs.
For example, I have a table with two columns.
car_id
tire_id
However, a specific car_id must always be paired with the same tired_id. Meaning a car ...
0
votes
1
answer
44
views
What constraint can't be written as a table constraint?
I am reading the Postgres 7.3 documentation to add a constraint to a PostgreSQL table.
It says:
To add a not-null constraint, which cannot be written as a table constraint, use this syntax:
ALTER ...
3
votes
0
answers
227
views
Does MySQL always run foreign key constraint checks when a row is updated? Or only when a relevant column is changed?
I have this question and I haven't found specific documentation to confirm the behavior and am unaware of how to manually check this myself.
Consider I have table A with b_id foreign key to table B. ...
2
votes
1
answer
166
views
How to enforce UNIQUE constraint for sixth normal form in PostgreSQL
I have a table person which I am planing to normalize to the sixth normal form.
The table has attributes username, email, and phone. The reason I want to go to the sixth normal form is keeping the ...
2
votes
0
answers
51
views
How to enforce NOT NULL for sixth normal form in PostgreSQL
I have a table person which I am planing to normalize to the sixth normal form.
The table has attributes username, email, and phone. The reason I want to go to the sixth normal form is keeping the ...
2
votes
4
answers
2k
views
NONCLUSTERED index seek on mslid is slow
I have an issue in SQL Server where a NONCLUSTERED index seek is performing poorly.
Below is the actual execution plan
https://www.brentozar.com/pastetheplan/?id=Sk3-4JGAK
How can I enhance ...
0
votes
1
answer
1k
views
Possible to create a constraint that is unique per foreign key?
MySql, Table Repos Fk on Users.Id, A User can have many Repos however each Repo must have a unique Id, per User not per table, what is the best way to ensure that Repos Id is unique for a given User ...
0
votes
1
answer
474
views
How can I prevent a rows with a particular attribute from being deleted in a cascade?
I have the following table
CREATE TABLE IF NOT EXISTS node (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
type TEXT CHECK(type IN ("DIRECTORY", "EXECUTABLE", "DOCUMENT&...