Questions tagged [constraint]
A declarative mechanism such as a check or foreign key that enforces some data integrity rule in a database.
485
questions
0
votes
0
answers
13
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 ...
- 451
1
vote
0
answers
44
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
0
votes
1
answer
31
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
58
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],[...
- 177
5
votes
1
answer
190
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 ...
- 1,898
0
votes
1
answer
33
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
38
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 | ...
- 115
0
votes
1
answer
71
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
24
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
517
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() ...
- 101
1
vote
2
answers
356
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
51
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 ...
- 705
0
votes
0
answers
61
views
Postgres Constraints: Updated Value Must Be Greater
I'm struggling to find out whether there is a constraint I can impose on a row with an integer value, such that any update on that row must have an integer value strictly greater than the value that ...
- 101
1
vote
1
answer
92
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
0
answers
20
views
Enforce dynamic value correctly
I am working on quiz builder app with advanced features such as enforcing constraints on quizzes and/or quiz items. Here is the snippet of the ERD:
violated_on represents the timestamp when this ...
- 1
0
votes
1
answer
18
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
587
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 ...
- 15.8k
6
votes
2
answers
450
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
408
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 ...
- 197
0
votes
1
answer
262
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 ...
- 131
1
vote
1
answer
159
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
419
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 ...
- 197
4
votes
2
answers
445
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
51
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 ...
- 197
2
votes
2
answers
797
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
531
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
586
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
1
vote
0
answers
1k
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 ...
- 111
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 ...
- 123
4
votes
1
answer
420
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 ...
- 554
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
47
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
3
votes
2
answers
1k
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] ...
- 7,344
0
votes
3
answers
121
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 ...
- 165
0
votes
1
answer
40
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 ...
- 567
3
votes
0
answers
226
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. ...
- 41
2
votes
1
answer
143
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
49
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
1k
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 ...
- 147
0
votes
1
answer
643
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 ...
- 103
0
votes
1
answer
362
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&...
- 113
0
votes
1
answer
82
views
Does putting constraints decrease the required size to store my data in SQL Server?
I would like to know if putting check constraints decrease the required column size or not? For example, when I create an int column and a check constraint on it to store the numbers between 100 and ...
- 333
-1
votes
2
answers
337
views
Can still see CONSTRAINTs after running ALTER TABLE [mysch].[mytablename] NOCHECK CONSTRAINT ALL but why?
I am looking at a piece of code in a proc that seems to disable but never reenable constraints, namely
ALTER TABLE [mysch].[mytablename] NOCHECK CONSTRAINT ALL
but after the actions are taken in the ...
- 267
-1
votes
1
answer
368
views
How to generate constraint sql?
I'm looking for a method that can generate the creating sql, like PgAdmin's SQL tab while having selected the constraint. (how pg admin does it?)
I want to implement a generic method that can drop and ...
- 131
0
votes
1
answer
228
views
Create an empty table from an existing table keeping the default value constraint
I tried:
Select * Into <DestinationTableName> From <SourceTableName> Where 1 = 2
but the default value constraint is not created.
I have a smalldatetime column whose default value is ...
- 115
4
votes
1
answer
6k
views
Column constraint based on values in another column
I have a PostgreSQL table phase_steps, with the following example rows:
phase_step_id|step_type|step_status|
-------------+---------+-----------+
1| RESEARCH| |
2| ...
- 147
0
votes
2
answers
42
views
Designing complex cross table data integrity checks
I am trying to start working on an application, and while I have some code the time has come to work on my database model as well. In the next sections I will describe what my application should be ...
- 101
2
votes
2
answers
196
views
Enforce that every company has at least one CEO
I've a requirement to ensure that every company has at least one CEO.
Example database model with requirements db<>fiddle.
Basically, a CEO can't be removed from a company without naming another ...
- 137
5
votes
1
answer
3k
views
Constraint in ON CONFLICT clause has no associated index
I have this simple setup using PostgreSQL 13, latest dockerized:
CREATE TABLE stop (
id INT UNIQUE,
name TEXT,
PRIMARY KEY(id)
);
CREATE TABLE line (
id INT UNIQUE,...
- 151
1
vote
0
answers
552
views
How to design a relational database schema for music recordings?
I would like to design a simple relational database schema for music recordings and normalize it to the ultimate normal form with respect to the projection and join relational operators, which is ...
- 71