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
2answers
26 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 ...
2
votes
2answers
119 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 ...
1
vote
1answer
83 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,...
0
votes
0answers
96 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 ...
1
vote
1answer
28 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 ...
-1
votes
1answer
29 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?
0
votes
0answers
11 views

Constraints number is differents after migrating from postgres 9.6 to 12.5

I have 2 postgres versions : 9.6 and 12.5 I have loaded the same database in them ( the famous dvdrental database) I was checking the differences between the 2 databases running multiple queries for (...
0
votes
2answers
34 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....
1
vote
1answer
33 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 ...
0
votes
3answers
50 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
1answer
42 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 ...
2
votes
2answers
231 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 ...
1
vote
1answer
38 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 ...
3
votes
2answers
185 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: ...
-1
votes
1answer
37 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 ...
1
vote
3answers
109 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 ...
0
votes
0answers
124 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 ...
1
vote
1answer
510 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 ...
0
votes
2answers
45 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 ...
0
votes
1answer
32 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. ...
0
votes
1answer
29 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 ...
1
vote
0answers
63 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) ...
9
votes
1answer
1k 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 ...
-2
votes
1answer
27 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 ...
1
vote
2answers
58 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 | ...
0
votes
1answer
529 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 ...
1
vote
1answer
68 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 ...
0
votes
1answer
1k 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.
0
votes
0answers
315 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 ...
0
votes
1answer
18 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 ...
3
votes
1answer
101 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 ...
0
votes
1answer
57 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.
0
votes
1answer
30 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 ( ...
1
vote
0answers
23 views

What is the meaning of MariaDB `mysql`.`servers` table constraint?

I am using SPIDER engine to access a remote table from within my MariaDB database, which requires a server definition in mysql.servers table created via CREATE SERVER statement. I noticed that this ...
1
vote
1answer
37 views

Adding constraint to an existing table column

I have a table that doesnt have any constraint on them like this CREATE TABLE SomeTable ( Id int NOT NULL, Type varchar(50), ) insert into SomeTable values (12,'Exchange-Student'); Now I ...
1
vote
1answer
44 views

Is it possible to enforce corresponding hierarchies?

I constructed the following toy example to illustrate my question. The left side of the schema describes a an abstract product consisting of its parts. The right side of the schema describes actual ...
1
vote
0answers
15 views

Only one truthy bool value for one foreign key in postgre [duplicate]

Lets assume we have 2 database tables (entites). The first is named A and the second one is named B. There are 1:N (where N > 0) relationship (so every record in table A is in relation with 1+ records ...
0
votes
1answer
36 views

How to allow only a certain type of foreign key in a table

I'm designing a new DB. I have employee and order table. order table has deliverer_id to indicate who delivered the order. But only employee who is "deliverer" should be allowed. I thought of 3 ...
0
votes
1answer
374 views

Add a constraint to a column on an Oracle table so that only positive values are allowed

I'm not allowed to drop the table, or delete the columns. The task is to add a constraint to a column possibly by using an ALTER statement so that it disallows any negative numbers. Is this possible? ...
-1
votes
2answers
53 views

Foreign key twice in same table for different columns?

Server: MariaDB 10.3.21 Client: MariaDB 10.4.12 Given the following structure/data: DROP TABLE IF EXISTS `main`; CREATE TABLE `main` ( `name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, `...
2
votes
1answer
159 views

combined contents of 2 columns can't contain duplicates

This is a hard question to ask. I find it difficult to put into words. I have two columns where the content of column A can not appear in column B and vice versa. So, the combined contents of both ...
0
votes
1answer
52 views

How do I impose a CONSTRAINT on a TEXT column in PostgreSQL 12?

I want a column with a single text character as a classifier so it's not worth the overhead creating another whole table and relating to it. How do I constrain the column to a list of acceptable ...
1
vote
2answers
79 views

Why is this PostgreSQL not null constraint not catching this insert?

I have a table for users that includes two columns for contact information. A user can provide a phone number, an email, or both, but not neither. I have the table set up with the following ...
1
vote
1answer
4k views

FOREIGN KEY Constraint Failed: SQlite3

I have two tables, created as below, sqlite> CREATE TABLE Artists( ...> ArtistId INTEGER PRIMARY KEY, ...> ArtistName TEXT NOT NULL ...> ); sqlite> CREATE TABLE Albums( ...&...
0
votes
1answer
35 views

Query data that does not fulfill constraints in table in Oracle DB

In Oracle I have a lot of tables in a given schema. Some of the table constraints in the schema have been disabled. I have to know for all tables in a given schema with any of the constraints ...
0
votes
1answer
23 views

Constraints between tables on a variable

im aiming to add a constraint on the number of bicycles allowed to be stored in each terminal - the number differing between terminals. These are my original tables create table terminal( ...
0
votes
1answer
550 views

Postgres: foreign key constraint for components of a composite type?

In Postgres, is it possible to set up a foreign key contraint involving components of a composite type, in a situation like the following one? Suppose I have CREATE TABLE main ( -- a and b are ...
2
votes
1answer
1k views

Compound unique constraint with NULL values

With the following table structure CREATE TABLE favorite_comments( id SERIAL PRIMARY KEY NOT NULL, opinion_id BIGINT REFERENCES opinions(id), review_id BIGINT REFERENCES reviews(id), ...
1
vote
0answers
69 views

Constraint creation on a large table is too slow

There's a table with 723M rows, large amount of data and not partitioned. We're adding a constraint to the table as follows: ALTER TABLE ONLY my_table ADD CONSTRAINT partial_constraint EXCLUDE USING ...
0
votes
1answer
112 views

Postgres: Can I associate index with FK constraint?

Postgres FK constraint and index are independent. Recently I was looked for unused indexes and found trick to eliminate indexes needed for constraint enforcing by: WHERE NOT EXISTS (SELECT 1 ...

1
2 3 4 5
9