2022 Developer Survey is open! Take survey.

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
0 votes
1 answer
25 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 ...
user avatar
  • 101
0 votes
0 answers
87 views

MySQL 8 falsely giving "column x cannot be null" error on update

I'm running the below query on a MySQL 8 managed server hosted at DigitalOcean! UPDATE games_codes SET result=5 WHERE id=1; When I run this query, I'm getting the following error. Column field ...
user avatar
3 votes
2 answers
305 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] ...
user avatar
  • 7,100
0 votes
3 answers
32 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 ...
user avatar
  • 151
0 votes
1 answer
27 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 ...
user avatar
3 votes
0 answers
32 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. ...
user avatar
1 vote
1 answer
37 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 ...
user avatar
1 vote
0 answers
29 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 ...
user avatar
2 votes
4 answers
208 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 ...
user avatar
0 votes
1 answer
27 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 ...
user avatar
  • 103
0 votes
0 answers
18 views

Create SQL Server foreign key link to multiple tables?

Can I have a constraint check if a column value is in either of two other columns? As an example, if I have a website and the users of the website should be staff or students, can I check the username ...
user avatar
0 votes
1 answer
18 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&...
user avatar
  • 103
0 votes
1 answer
63 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 ...
user avatar
  • 323
0 votes
0 answers
19 views

Is it possible to have a custom constrain in sqlite?

We store the user-id, category and few other misc pieces of information in a table. One of the categories, let's say 10th category is special and there should be only 1 record per user-id with ...
user avatar
  • 113
-1 votes
2 answers
99 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 ...
user avatar
-1 votes
1 answer
46 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 ...
user avatar
  • 131
0 votes
1 answer
64 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 ...
user avatar
  • 113
1 vote
1 answer
872 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| ...
user avatar
0 votes
2 answers
29 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 ...
user avatar
2 votes
2 answers
136 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 ...
user avatar
1 vote
1 answer
737 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,...
user avatar
  • 111
1 vote
0 answers
284 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 ...
user avatar
1 vote
1 answer
244 views

How to resolve duplicate constraint names in Postgres?

On Postgres 9.6, I have a table where a foreign key constraint and a unique constraint have the same name (ultimately due to long distinct names being silently truncated to 63 chars). I'm trying to ...
user avatar
  • 121
-1 votes
1 answer
31 views

Why PG 13 does not allow deferring statement trigger constraints?

I noticed in PG 13 docs that only row constraint triggers can be deferrable. Why not statement constraint triggers?
user avatar
  • 301
0 votes
2 answers
45 views

Restrict insertion of new row if a row exists with certain field values

Using a Postgres DB. I have a table let say t with columns txn_type, txn_id. I want to add a constraint where it restricts to insert new row if I have a row with particular values existing in table. e....
user avatar
1 vote
1 answer
122 views

SSIS failure flow error. The decision after a sequence container failure does not proceed with the correct flow

I have an SSIS sequence container that extracts data from individual source database tables into a Stage layer. However, I need to know when a given calculation crashes in order for the given error to ...
user avatar
0 votes
3 answers
52 views

How to set constraint sql 2 colume, Data is not available elsewhere and must be used only

How to set constraint sql 2 colume, Data is not available elsewhere and must be used only. Example is OK NAME REV ID PI A 0 1 PA001 A 1 1 PA001 Example is Error NAME REV ID PI A 0 1 PA001 A 1 1 ...
1 vote
1 answer
57 views

Add constraint to table creation in SQL

I have to create a table Product with elements P, PType, PName, Price and Origin. I need to have the constraint that all products with PType "Meat" must have "Europe" as Origin. I ...
user avatar
2 votes
2 answers
629 views

MySQL best way to define a couple of columns as both null or both not-null

On a MySQL DB, I would like to find a way to force a couple of columns be both null or both not null at the insert / update time. This means that on each record, both columns are null or both columns ...
user avatar
  • 131
1 vote
1 answer
73 views

How can I define a one to many constraint between columns of the same table?

I'm creating an IDs table in PostgreSQL to represent the relationship between multiple foreign IDs - call them a, b and c. I happen to know that the foreign IDs have one-to-many relationships between ...
user avatar
3 votes
2 answers
195 views

How to implement an association with restrictions

I'm working on an SQL database of useful plants. I'd like to be able to allow users to annotate the plants with a set of hierarchical categories and tags. For example: category: medicine body part: ...
user avatar
-1 votes
1 answer
80 views

Postgresql uses slower constraint over faster index

I have a few tables (25m records +) which have a very INSERT, UPDATE and DELETE biased workload (typically 50,000 to 100,000 operations a day) where the query planner seems to make an odd choice of ...
user avatar
  • 1
1 vote
3 answers
150 views

Find filtered indexes on an Oracle table

How can I find if an index is filtered? Filtered indexes (borrowing the term from SQL Server, or known as "partial indexes" in PostgreSQL) can have very high performance when they focus on ...
user avatar
0 votes
0 answers
299 views

Best practices for Postgres foreign key constraint definition

Is there any material difference between these two ways of defining foreign key constraints? CREATE TABLE table1 ( id INT PRIMARY KEY, other_id INT REFERENCES table2(id) ) CREATE TABLE table1 ...
user avatar
  • 143
1 vote
1 answer
2k views

Postgres: Unsafe use of new value of enum type

I want to alter a constraint after I have added a new value to an enum I use. So I originally have an enum called activity_state where I want to add a new value. But I also need to change the ...
user avatar
  • 111
2 votes
3 answers
348 views

Do I need to VALIDATE CONSTRAINT?

I create a foreign key constraint and know that there are no violations, so I mark it as NOT VALID to skip the check. From the documentation: But the database will not assume that the constraint ...
user avatar
  • 237
0 votes
1 answer
45 views

Postgres: find all FK constraints associated with a given PK

Given some input PK column, e.g. ani.animals.id, I want to find all FK constraints names associated with that PK. I also need table and schema's names where each particular FK constraint is located. ...
user avatar
  • 115
0 votes
1 answer
33 views

MySQL constraint to prevent combinations of columns

I have a table that represents files in directories. dir | file ---------------- dir_A | file_0 dir_A | file_1 dir_A | file_1 dir_B | file_2 dir_C | file_3 ... | ... I want to put a constraint on ...
user avatar
1 vote
0 answers
97 views

Order of foreign keys/cascades processing in SQL Server

I have a parent table and 2 (or more) children tables with foreign key defined with some cascade option. So when an entry from a parent table is being deleted, SQL Server will check 2 (or more) ...
user avatar
10 votes
1 answer
2k views

Is the query optimizer able to optimize away IS NOT NULL conditions if the column has a NOT NULL constraint?

I am trying to work out ways to improve the performance on a very slow query. It has more obvious problems but one thing I noticed is that one of the conditions in the WHERE clause is 'AND t.data IS ...
user avatar
  • 331
-2 votes
1 answer
31 views

CHECK CONSTRAINT isn't identifying breaches of a constraint but DBCC CHECKCONSTRAINTS does

I have a table full of data and I have a constraint on a field in that table that is setup to ensure the total of 2 fields equals a 3rd field. For example: ALTER TABLE [dbo].[Table1] WITH NOCHECK ADD ...
user avatar
  • 13
2 votes
2 answers
91 views

Almost unique constraint in MySQL

Is is possible to have a key in MySQL 5.7, which is unique for all values except one. Let's say -1 is a not assigned wildcard. Is it possible to have a table with the following structure user | car | ...
user avatar
  • 133
0 votes
1 answer
1k views

Why is ADD CONSTRAINT ... NOT VALID taking a long time?

I added NOT NULL constraints to some columns like this, expecting the command to be very fast because of NOT VALID. However, it took about 60-100 seconds, and seemed to lock the table for the whole ...
user avatar
1 vote
1 answer
97 views

Make sure date of departure is different than date of arrival?

I have the following tables: Flights (id_flight) Airports (id_airport) Departures_Arrivals (id_flight, id_airport), which is the Junction Table between Flights and Airports and it has the following ...
user avatar
  • 113
0 votes
1 answer
2k views

UPPERCASE Constraint in SQL

What will be the query to add UPPERCASE Constraint to a column of pre-existing table? The question demands to ensure that the entries are in UPPERCASE.
user avatar
  • 11
0 votes
0 answers
650 views

Postgresql 12 Truncate partition with foreign key

I am building a datawarehouse using Postgresql (12) and think I should be using partitions on the most populated tables, for performance and maintanability reasons. I have something along the lines ...
user avatar
  • 432
0 votes
1 answer
191 views

How to view contraints of a cloumn in postgresSql?

I applied a constraint but forgot its name. Now I want to drop it using ALTER TABLE [TABLENAME] DROP CONSTRAINT [CONSTRAINTNAME] . If there is a way to drop all constraint from a column that should ...
user avatar
3 votes
1 answer
105 views

Complex key constraint: allow only if uuid is new or another column matches

For my use case I am trying to get something like this to work with Azure Data Factory (ADF) but I'm also interested in this question from a theoretical perspective. Is it possible to do something ...
user avatar
  • 143
0 votes
1 answer
250 views

PostgreSQL - How to list all foreign key link to Primary key of a table

I have a table res_users with primary key id I would like to list all the foreign key link to id from all the tables in my database.
user avatar
0 votes
1 answer
31 views

Delete/Modify row in one table based on a condition

I have a table structure like create table EMPLOYE ( CodeEmploye varchar2(100) not null, NAS varchar2(100), CONSTRAINT employe_pk primary key (CodeEmploye) ); create table SALAIRE ( ...
user avatar

1
2 3 4 5
10