Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [constraint]

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

1
vote
2answers
38 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 ...
1
vote
1answer
21 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
36 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
45 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
54 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
96 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 (...
0
votes
0answers
51 views

Circular reference when designing DB

I have created the following ER-diagram. On UserHistory, I have added the following constraints: FK UserID => UserID (in User) FK TeamID => TeamID (in Team) onDelete: cascade onUpdate: cascade On ...
2
votes
0answers
50 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 ...
2
votes
2answers
99 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
193 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
30 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
63 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
22 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
22 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
83 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
117 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
147 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
81 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
180 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) . ...
1
vote
2answers
197 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
42 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 ...
0
votes
1answer
618 views

MySQL WorkBench SQL : Cannot Add Foreign Key Constraint (Error 1215) while creating database

Problem: I am receiving the Error 1215: Cannot add foreign key constraint error while running my MySQL schema code generated from the EER Diagram I used to model my database. The Error occurs ...
0
votes
1answer
88 views

Create a unique constraint on a column based on distinct value from another column in the same table

I want to make a column unique, but it has to be unique based off a distinct value from another column in the table. Consider the following table: CREATE TABLE locations( locationId INT IDENTITY(1,...
0
votes
0answers
221 views

H2: Drop index which belongs to a constraint

We have a schema "Logger" that contains two tables: LogEntry (PK: Id) LogExceptions (Combined PK:LogEntryId,LineIndex) LogProperties (Combined PK:LogEntryId,PropertyKey) The schema has a foreign key ...
4
votes
2answers
240 views

Advantages to column constraints over table constraints

Is there any advantages whatsoever, other than style to writing this, CREATE TABLE foo ( a int PRIMARY KEY, b int ); And then, CREATE TABLE bar ( a int REFERENCES foo, c int ); Over, ...
3
votes
2answers
126 views

Name of not null constraint is missing in error message

The relation was created in Postgres 9.6 with use of the following commands: CREATE TABLE import_event ( import_event_id BIGINT NOT NULL, filename VARCHAR (200) CONSTRAINT ...
0
votes
1answer
28 views

Restrict the occurrence of a value according to the respective value in another column in a PostgreSQL table

I'm using a PostgreSQL table to link taxonomic names with the respective taxon concept as follows: create type name_status as enum ('accepted','basionym','synonym'); create table temp ( concept_id ...
7
votes
3answers
301 views

Foreign Key with a Constant

Let's say I have a table A, that has two columns: one is an ID for ThingA, and one is an ID for ThingB. The primary key is (ThingA, ThingB). Next, I have a second table, but this time it's restricted ...
0
votes
1answer
27 views

How to replace foreign reference in PostgreSQL? [closed]

Suppose I have a foreign reference column which is pointing to an item A, how do I change it to item B? I would like to modify all records whose foreign reference column is A to B.
5
votes
1answer
311 views

How to add a named constraint in Oracle SQL?

I have a table. I need to add a NOT NULL constraint to one of the columns. This is my best attempt at a solution: alter table clookup add CONSTRAINT lookup_9 NOT NULL (clookup_col) This produces ...
2
votes
1answer
200 views

Remove extra email characters to enforce uniqueness

Gmail ignores . inside an email address e.g abc@gmail.com and a.b.c@gmail.com are same Gmail also considers same account if account has any number after + symbol. e.g xyz@gmail and xyz+1@gmail.com are ...
1
vote
2answers
354 views

What is the difference between CHECK and CONSTRAINT CHECK?

is there any difference between the follow exemples? ProjNum int CONSTRAINT nn_ProjNum NOT NULL CONSTRAINT C_ProjNum CHECK (ProjNum >= 10) and ProjNum int NOT NULL CHECK (...
1
vote
1answer
79 views

Representing a many-to-one relationship in a database diagram

I have two tables loadingDetail, and stowageDetail: loadingDetail | stowageDetail [PK] loadingID (int) | [PK] StowageID (int) [PK] transactionID (in) | [PK] room (int) ...
0
votes
1answer
60 views

Postgres: Read CONSTRAINTS definition [duplicate]

I'm able to read CONSTRAINTS in the Information Schema, but how can I get all parameters associated with that CONSTRAINTS to get a full CONSTRAINTS definition by using SQL ? For example, SELECT * ...
1
vote
2answers
1k views

Validating the GST Identification Number (GSTIN)

The following image describes the format of the GST Identification Number: The first 2 digits denote the unique State Code in accordance with the Indian Census 2011. For instance, the State Code of ...
1
vote
3answers
167 views

Table column references partial key of table B

In my database (MS SQL Server) I use language specific texts. So I defined table Texts as follows: CREATE TABLE [dbo].[Texts]( [Id] [int] NOT NULL, [language] [nchar](5) NOT NULL, [text] ...
0
votes
1answer
87 views

Order to disable constraint in a SQL Server Database

I have a DB that every day delete all the data and load new one at the night. To do this an SP disable all constraint with sp_msforeachtable execution. I created some new objects (Schemas, SP, Tables, ...
1
vote
0answers
129 views

In PostgreSQL, how to deal with error messages that show almost no context?

Today I realized a number of points where PostgreSQL (v10.3) is rather lackluster and sparse in its error messages. The first point is illustrated by this code: drop schema if exists X cascade; ...
1
vote
1answer
47 views

Does the table constraint CHECK gets executed on deletions?

I guess not but if someone has an idea perhaps could give me a hand. I'm trying to restrict the deletion of a row of a table under some circumstances that requires to query against other tables. An ...
4
votes
2answers
65 views

FK constraint fails while inserting into child on Disjoint tables

I'm trying to test the concept of disjoint tables in MySQL, so I created some tables: CREATE TABLE item ( id int NOT NULL AUTO_INCREMENT, type ENUM('label', 'checkbox') NOT NULL DEFAULT 'label', ...
0
votes
2answers
44 views

How do I duplicate constraints from one existing table to another existing table with an identical structure?

I'm using PostgreSQL. I have two tables, table_a and table_b. table_b is a structural copy of table_a but it was created without the constraints of table_a. At a later date, I want to add the ...
8
votes
2answers
3k views

Constraint - one boolean row is true, all other rows false

I have a column: standard BOOLEAN NOT NULL I would like to enforce one row True, and all others False. The are no FK's or anything else depending on this constraint. I know I can accomplish it with ...
6
votes
1answer
97 views

Can I specify a name for the index which SQL Server creates for my unique constraint?

In SQL Server I deploy a table with a UNIQUE constraint: CREATE TABLE [dbo].[Something] ( [IdentityId] UNIQUEIDENTIFIER UNIQUE NOT NULL DEFAULT NEWID() -- Whatever else... ) and this makes ...
1
vote
1answer
176 views

Composite constraint of numrange and boolean

I have a current constraint like this: ALTER TABLE myschema.my_table ADD CONSTRAINT my_constraint EXCLUDE USING gist((<uuidcol>::text) WITH =, numrange(<start>, <end>) WITH &&...
1
vote
1answer
173 views

TSQL Re-Enabling Contraints take a long time

Is there any way I can speed up the re-enabling of constraints? As part of a job to update several tables in our Warehouse, which is being done out of hours, the re-enabling of constraints on said ...
26
votes
3answers
4k views

How do I enforce a write-once then read only database table in SQL?

Is it even possible? My use case is a ledger table, with a requirement that once a record is created, it should be read-only, i.e. no-one should be able to edit or delete it. This only applies to the ...
6
votes
1answer
375 views

Partially-Unique Check Constraints

I have an old Access table which is being migrated to SQL Server, and in it there's a field called "LinkedID". This field was mostly unused until ~5 years ago, so for many historical records it is ...
0
votes
2answers
579 views

Add array table constraints, empty or null if other column is false

I'm adding constraint on an existing table jobs: DROP TABLE IF EXISTS jobs CASCADE; DROP TYPE IF EXISTS job_state; CREATE TYPE job_state AS ENUM ( 'UNSTARTED', 'STARTED', 'FAILED', 'COMPLETED' ); ...
1
vote
1answer
196 views

How to add constraint on a varchar column to have at least one non numeric character

I am trying to implement some constraints on tables so that there is no tampering of the data from what is expected. One of the constraints is on a varchar column. It should at least contain one non-...
1
vote
1answer
151 views

Knex pass over db FK constraints

when I try to insert values into my FKs in the DB Browser for SQLite I get the constraints error, but when I run the seeders with knex (npm package) it pass over the FK constraints and it inserts when ...