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
1answer
33 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
23 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
33 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
13 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
13 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
95 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
0answers
11 views

PostgreSQL: Disable foreign constraints in order to speed up INSERT operation

I'm testing different approaches in order to speed up the INSERT operation and found that I have false assumptions about DEFERRABLE constraints. Suppose we have a table a with 2000000 rows and a table ...
0
votes
1answer
16 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
27 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
21 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
20 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
37 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
23 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
35 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? ...
0
votes
0answers
13 views

adding constraints in Azure sql database table

I created the Product table in Azure SQL database without a foreign key, I wanted to add foreign key constraints using ALTER statement and get the error: "Msg 2715, Level 16, State 6, Line 1 Column,...
0
votes
0answers
21 views

Splitting a large table using CTE with a constraint

I have a large table called "order". That table has a field record_type. I want to split the table up into different orders by record type. All of the tables for the different types of orders have the ...
-1
votes
2answers
40 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
84 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
41 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
38 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 ...
0
votes
1answer
332 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
29 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
21 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
116 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 ...
0
votes
0answers
24 views

generating a log for Cascade deletes

I have a database which supports a .Net application and i have several parent tables and child tables database design where several tables are linked using FK and there is cascade delete enabled. So ...
0
votes
1answer
137 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
36 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
35 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 ...
4
votes
1answer
93 views

Prevent drop column with index on it in PostgreSQL

In PostgreSQL when you drop column in a table, all indexes and constraints which use this column will be also dropped silently. If you overlook such situation, you can get serious problems in ...
0
votes
1answer
17 views

Enforce unique constraint on 1 of n values

Let's say I have a table like this. create table foo ( id serial primary key, kind smallint not null check (kind >= 0 and kind < 3) ); With that check constraint, Postgres will only ...
0
votes
1answer
18 views

How to ensure all records of a header are under the same fiscal year?

Say I have the following schema: BUDGET_YEAR_TARGET INTEGER ID BUDGET_DATE_TARGET INTEGER ID INTEGER BUDGET_YEAR_TARGET_ID--Foreign key DATE DATE How can I ensure that any records ...
-2
votes
1answer
43 views

SQL Check Constrain with ONLY IF statment

I have a table with a check constraint that enforce a not null for the data column if the havedata column is true. is there away to modify the constraint to not allow case 3 to happen. i.e Data is ...
1
vote
1answer
62 views

How do I reference a row which has two foreign keys as its primary key?

I am currently trying to setup a database managing players and teams for a competition. Players are competing in teams of two but may be shuffled. As every team combination can only exist once, I ...
2
votes
0answers
50 views

Design issue in transitive dependencies between tables

This is a made-up scenario, but I imagine that it can be of general interest, so I'll post it here. Imagine the following business rule: A course has a grade scale that determines what grades a ...
2
votes
0answers
60 views

Can you force sets of rows to be unique?

Let's say we have a table like this with a primary key of (nr, key): nr | key | val ---|-----|------ 1 | 1 | a 2 | 1 | a 2 | 2 | b 3 | 1 | a 3 | 2 | c Every set of rows (grouped by nr ...
0
votes
1answer
347 views

Covering and overlapping constraints

Given that there are 15 entities in E1, 20 entities in E2 and 25 entities in E3, what's the maximum and the minimum number of entities in E0? It's also known that the ISA hierarchy follows the ...
0
votes
1answer
19 views

Setting constraint so that new value in “Table A” must already exist in “Table B”, else return error?

I'm trying to set up some kind of constraint so that I can only enter values in "Table A", if that value exists in "Table B". I want this to act sort of like a selection list, where there are only a ...
1
vote
1answer
23 views

Re-adding constraint after ALTER fails on row that exists

I'm getting a failure trying to add a constraint that previously existed on a table after having dropped it to do an alter: mcqueen=# ALTER TABLE ONLY public.mcqueen_base_imagemeta2 mcqueen-# ADD ...
4
votes
1answer
200 views

Add INCLUDE term to primary key index in Postgres 11

I work with Postgres 11 and would like to change the index that backs the primary key of a large table (~750 million rows). The primary key is the bigint column id and I would like to include an extra ...
2
votes
2answers
63 views

How to have a “kinda” unique column

How can I have a MySQL database number column that only allows one 1, but infinite 0's? Some type of constraint or something. Elaboration To clarify what I mean and why I want this: Imagine you ...
0
votes
1answer
72 views

How to list all constraints of a table in PostgreSQL by schema?

Yes, I'm aware there is a question very similar to mine, the difference is that I want all constraints, including not null, which no answer is able to do.
0
votes
1answer
117 views

Exclusion constraint on recursive foreign key (on the example of a Tree)

I have the table with recursive foreign key. It's just hierarchical tree structure: CREATE TABLE tree ( id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, value ...
1
vote
1answer
70 views

Understanding the recreation of index in Table partitioning

I need your help. I have[AdventureWorks2014] database and I wanted to perform partitioning on [Production].[TransactionHistoryArchive] table based on [TransactionDate] column. [Production].[...
3
votes
1answer
2k views

SQLServer add a default value for a column [closed]

I am using MS SQL Server. I have an "action" table with an "action_type" column that I want to set a default value of "to do" for. My DDL in MS SQL Studio errors! I have read many many articles trying ...
1
vote
1answer
298 views

Constraint on multiple columns?

I have a table edges, which describes relations in a graph: CREATE TABLE IF NOT EXISTS edges ( src INT NOT NULL REFERENCES nodes(id) ON UPDATE CASCADE ON DELETE CASCADE ,tgt INT NOT NULL ...
1
vote
1answer
67 views

Purging of unreferenced data via unconditional DELETE, by leaning on foreign key constraints

I'm designing a database from which old data has to be purged regularly for legal reasons, and I'm trying to figure out the best way to organise the purging. Dealing with the main tables is trivial, ...
3
votes
1answer
107 views

Enforcing a “disjoint or identical” constraint

Suppose that F is an Oracle table with two columns, X and Y, such that the constraint CONSTRAINT unique_xy_pairs UNIQUE (X, Y) is satisfied. For each value [x] of F.X, let S[x] represent the set ...
0
votes
1answer
190 views

Add foreign key constraint to an existing table column, referencing a column in a newly created table

Let's say I have one table defined as this CREATE TABLE sales ( agent_id integer references agents(agent_id), sale_date date, amout numeric(10,2) ); Then an ETL process fills ...
1
vote
0answers
67 views

postgres boolean function and check constraint [duplicate]

I been using postgres for a while. Mostly simple stuff. Now trying to implement a small perl function to check for a single valid email address. My employees table should have either a single valid ...

1
2 3 4 5
9