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
2
votes
0answers
50 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 ...
-2
votes
0answers
19 views

Constraints Enforcement [on hold]

Which of the following statements are TRUE? Select all that apply. A. Any primary key constraints can always be enforced by column constraints B. Any foreign key constraints can always be enforced ...
0
votes
1answer
49 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
0answers
34 views

Foreign key constraint not working properly [on hold]

I have the following table. CREATE TABLE `Users` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `username` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `first_name` ...
0
votes
1answer
15 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
17 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
48 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
47 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
27 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
25 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
38 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
504 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
59 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
57 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
83 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
27 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
55 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
vote
1answer
37 views

Can I restrict a SQL Server spatial field to hold values of specified spatial types and SRIDs?

I am trying to understand geography data types so I can integrate my database more closely with GIS processes. From my reading, it appears that a geography field can hold many different types of ...
1
vote
0answers
21 views

Referential integrity based on data more than a table away

Notes on tables A, B, C, D, E B.aid and C.aid reference A.aid. D.cid references C.cid and D.did is the primary key. D is a C. cid and did are pre-assigned and I cannot change them (they need to ...
13
votes
2answers
615 views

Can SQL Server create collisions in system generated constraint names?

I have an application which creates millions of tables in a SQL Server 2008 database (non clustered). I am looking to upgrade to SQL Server 2014 (clustered), but am hitting an error message when under ...
0
votes
1answer
24 views

How to add a constraint to a table using a partial index?

I have a simple table: create table item ( lan text not null, disabled date ); My aim is to ensure uniqueness for the lan column over all rows, where disabled is null. I created a partial ...
1
vote
2answers
36 views

How to get number of PK used as FK?

How to get number of PK used as FK? Table 1 has a column ID (PK). Table 2 & 3 has a column ID (FK - refers Table 1 PK). How to get Value 1 (PK) has used in table 2 & 3 or not without ...
2
votes
2answers
78 views

Add Constraint to check value exists in a non-unique composite key column

I have the following table, which has a composite primary key: CREATE TABLE dbo.Seqs( SequenceKey nvarchar(10) NOT NULL, UsageSequence smallint NOT NULL, col1 bit NOT NULL, col2 ...
1
vote
2answers
54 views

How does ON DELETE CASCADE relate to JSON?

For relational databases, there is the notion of ON DELETE CASCADE. When working with JSON, how do these two concepts relate? Is ON DELETE CASCADE supported for JSON? Can you give an example of a ...
0
votes
1answer
49 views

Cannot import table due to zombie constraint

I tried to refresh my copy of the production database by emptying and importing a SQL file. I've done it hundreds of times, only this time the import failed. I tried dropping & recreating the ...
1
vote
2answers
48 views

Is there Such Thing As The Opposite of Unique Constraint

I want to be constrain my database with what is essentially the opposite of a UNIQUE constraint. I want to only be able to add a new row if some combination of fields has been added before. Note: I ...
1
vote
1answer
28 views

Constrain input values

I am working on limiting input in certain fields in my Postgres database, and I have a working solution using ENUM like so: CREATE TYPE label AS ENUM ('foo','bar'); However, I was browsing around ...
0
votes
0answers
240 views

SQL Default CONSTRAINT not working

I have the table below with some basic information with two defaulted columns CREATE TABLE [dbo].[INSPECTION]( [InspectionID] [int] IDENTITY(1,1) NOT NULL, [LocationID] [int] NOT NULL, [...
1
vote
2answers
137 views

How to name a constraint which was unnamed during the table creation?

I had created the table by specifying the columns as primary key and not null without giving them any constraint names and I have populated the data. I want to give names to them now. How do I do ...
4
votes
3answers
105 views

Enforcing distinct number of trailing spaces

I'm working on converting a SQL Anywhere (ASA) 5.5 based application and its database to Microsoft SQL Server. Existing is a table with a primary key clustered on two columns, code_type char(4) and ...
0
votes
2answers
173 views

Duplicate result when I select with distinct values from foreign key constarint

I want to select values from two different tables which have one to many relationship between them. During select it's retrieving duplicate values even when I use distinct and group by. Above table is ...
1
vote
1answer
32 views

Constraint via Foreign Key

I have the following schema in SQLite: CREATE TABLE calendars ( id INTEGER PRIMARY KEY, title TEXT NOT NULL ); CREATE TABLE entry_types ( id INTEGER PRIMARY ...
0
votes
1answer
88 views

cascade constraints in Oracle

Can we use the cascade constraints instead of specifying FK in our code I mean can we Use this execute immediate 'ALTER TABLE @PCWSCHEMA@pcwscannermaster DROP CASCADE CONSTRAINTS'; instead of the ...
0
votes
1answer
151 views

Auto incrementing a foreign key value the same as the primary key of the parent table in SQL when inserting data?

I have the following two connected tables as shown below in the Microsoft SQL Server. When inserting data from visual studio, the foreign key in the Reservations table (Customer_ID) should be auto ...
2
votes
1answer
140 views

Making a column unmodifiable/non-updatable after its row's initial creation?

What is the most idiomatic way to make a column unmodifiable/non-updatable after its initial creation via INSERT in Oracle? CREATE TABLE my_table ( id NUMBER NOT NULL CONSTRAINT ...
1
vote
1answer
244 views

Select subquery CHECK constraint DB2

I have 2 tables: TVSHOW and ACTOR. I want to reject records if the inserted actor in TVSHOW does not exist in the ACTOR table using DB2. db2 "ALTER TABLE TVSHOW ADD CONSTRAINT check_actor CHECK (...
2
votes
0answers
52 views

Is this “idiosyncratic” method to manage constraints declarations beneficial?

I just wanted to get feedback from actual DBAs, based on their experience and knlowledge of database theory, in regards to this. At my company, we have a large .sql file that holds all of our schema ...
3
votes
2answers
646 views

Find all `NaN` values in a postgresql database

Numerics and floats can store NaN https://www.postgresql.org/docs/current/static/datatype-numeric.html I have a working program with a PostgreSQL database that does not have any constraint about NaN. ...
2
votes
2answers
883 views

truncate table with foreign keys

I have to truncate a table which gave me the below error. ORA-02266: unique/primary keys in table referenced by enabled foreign keys. I fixed that by checking the below, SELECT p.table_name "...
0
votes
0answers
57 views

Deadlock when dropping multiple constraints on multiple tables at the same time

Background We have a data warehouse process that calls the following code: exec sp_executesql N'EXEC maintenance.sp_fk_statements @P1,@P2,@P3', N'@P1 varchar(3), @P2 varchar(9), @P3 ...
0
votes
0answers
162 views

How to create HierarchyId node constraint without GetAncestor table column?

I have a table similar to the following that holds hierarchies from difference sources (SourceId). CREATE TABLE dbo.Hierarchies ( SourceID INTEGER NOT NULL, NodeHierarchyId HierarchyId NOT ...
0
votes
1answer
30 views

Error on Check Contraint that is on a Column with a Default Constraint

Inspired by this article, I wanted to test the idea before I considered implementing it. However, I get this error Msg 547, Level 16, State 0, Line 14 The INSERT statement conflicted with the ...
0
votes
1answer
27 views

Do I Need Constraints

We created a database template that gets executed during the installation of a web application (that we also created). After the application ran in a live scenario for a while, it inherited ...
0
votes
1answer
257 views

Getting around Primary Key requiring non-nullable columns

Word Problem I need to be able to create a table that has a column which is a foreign key and is nullable, but is also part of the (composite) primary key. Obviously the problem is that (at least ...
1
vote
2answers
542 views

Creating Conditional Rules for Column Values

I am currently working on the following table in SQL Server Table: order_status_logs log_id INT NOT NULL IDENTITY(1,1) order_id INT NOT NULL status_id INT NOT NULL log_date ...
1
vote
1answer
447 views

Clarification on Cardinality and Participation

I'm currently redesigning a schema with 20 or so tables and my first task is to create an ERD from the existing tables/constraints/relationships. This has proven more difficult than I expected because ...
0
votes
2answers
143 views

Can't empty all my database in SQL Server [duplicate]

In my SQL Server I tried to truncate all tables, but there have some constraint. So I running this EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all" -- Disable all constraints for ...
1
vote
1answer
355 views

How to upload multiple csv files to sql server without constrains

I backed up an old SQL Server DB with .CSV files for each table. The old DB is now gone; the .CSV files are all I have. Now I want to create new DB and upload all those csv file (my old db removed) . ...
2
votes
2answers
810 views

PostgreSQL - Enforcing unique constraint on date column parts

I'm trying to enforce a unique month and year combination on a table with a data column. e.g. create table foo ( mydate date ); I want to enforce that only one row per month and year is valid i....
1
vote
0answers
72 views

How to find which foreign key constraints a table row is associated with?

(There are similar sounding questions out there at first glance, but none any that are actually identical from what I've seen so far) I'm on PostgreSQL 9.4.7 In narrow terms of my use case (good ...