Questions tagged [constraint]

A declarative mechanism such as a check or foreign key that enforces some data integrity rule in a database.

Filter by
Sorted by
Tagged with
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 ║ ...
Rohit's user avatar
  • 11
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 ...
user3621633's user avatar
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 ...
heeeresjohnny's user avatar
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 ...
mcsov's user avatar
  • 15
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 ...
Dimitrios Desyllas's user avatar
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 ...
Sassa's user avatar
  • 135
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)...
Timur Shtatland's user avatar
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 ...
riwu's user avatar
  • 133
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 ...
loris's user avatar
  • 103
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 ...
Mario Ishac's user avatar
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 ...
Felipe's user avatar
  • 293
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 ...
Arthur Putnam's user avatar
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 ...
Arjun Lajpal's user avatar
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 = ...
Will Newton's user avatar
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],[...
tbasallo's user avatar
  • 177
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 ...
SE1986's user avatar
  • 1,960
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 ...
psygo's user avatar
  • 121
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 | ...
ZiiMakc's user avatar
  • 125
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 ...
KoGIS's user avatar
  • 3
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, ...
Dimitrios Desyllas's user avatar
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() ...
Mohammad Miras's user avatar
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 ...
Daniel's user avatar
  • 143
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 ...
Dolphin's user avatar
  • 775
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 ...
NoName123's user avatar
  • 113
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: ...
Alejandro85's user avatar
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 ...
Marcello Miorelli's user avatar
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) ...
pear's user avatar
  • 63
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 ...
Don Draper's user avatar
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 ...
rationaltiger24's user avatar
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 ...
DaleSwanson's user avatar
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 ...
Don Draper's user avatar
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 ...
Justin's user avatar
  • 145
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 ...
Don Draper's user avatar
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 ...
Johannes Pille's user avatar
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 ...
Alex's user avatar
  • 165
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 ...
user2959071's user avatar
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 ...
Andrew Polukhin's user avatar
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 ...
Eric Johnson's user avatar
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 ...
Dai's user avatar
  • 574
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 ...
Bah Soh's user avatar
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 ...
milad's user avatar
  • 101
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] ...
Racer SQL's user avatar
  • 7,376
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 ...
Archmede's user avatar
  • 165
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 ...
Joey Yi Zhao's user avatar
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. ...
user3509128's user avatar
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 ...
Eerik Sven Puudist's user avatar
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 ...
Eerik Sven Puudist's user avatar
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 ...
ahmed elbarbary's user avatar
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 ...
fuzzybear's user avatar
  • 103
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&...
Robert W's user avatar
  • 113

1
2 3 4 5
10