Questions tagged [check-constraints]

That part of declarative referential integrity (DRI) where a column's permitted range of values is enforced by a Boolean predicate.

Filter by
Sorted by
Tagged with
5
votes
2answers
2k views

CHECK constraint for array column to verify length > 0

I'm playing with postgres table validation rules and trying to set a CHECK constraint for an array column. An idea is to allow only arrays with length > 0. Here is how I want to implement it: create ...
1
vote
1answer
638 views

Implement check constraint for varchar (7) to enforce YYYY-MM

I am considering the following with regards to implementing a check constraint on a column I am calling year_month. My objective is to have a check constraint to enforce YYYY-MM. My intention is to ...
2
votes
1answer
432 views

Check Constraints on SQL Server Database advisable for number range & year & month?

I'm busy designing a website and I have a requirement for the following columns in my SQL Server database: allocation (values from 0 to 100) year (only) month (only) I have read about check ...
1
vote
1answer
1k views

Why does adding a simple CHECK constraint take so long on Postgres

I'm using PostgreSQL 9.6.1 on Amazon RDS I'm adding a CHECK constraint on an already indexed column named timestamp_ of a table called event_click that contains about 1 million rows. ALTER TABLE ...
5
votes
1answer
3k views

Postgresql partitioned table timestamptz constraint problem

The table reports is partitioned table by day like reports_20170414,reports_20170415 Constraint SQL is defined as follow CHECK ( rpt_datetime >= '2017-04-14 00:00:00+00'::timestamp with time ...
1
vote
2answers
3k views

List of values accepted by a column

Table Animal has following columns: SR_ID First_Name Last_Name PET The PET column has a check constraint; hence it only accepts three values DOG, CAT, FISH. Sample Data +-------+-------------+-------...
1
vote
2answers
1k views

Constraint for multiple foreign key yet it's mutually exclusive (PostgreSQL)

Loan table associated to due table. also Invoice table associated to due table. In due table either one of them foreign key should present and other one should be null. What kind of constraint ...
3
votes
3answers
3k views

SQLite, ASCII A-Z Check Constraint

I have the following table which I'm trying to limit the column "prefix" to ASCII alphabetical characters. However, I am still able to insert other characters after using the following constraint. Why ...
4
votes
2answers
130 views

Creating range for special fields in SQL Server

I have two columns that look like this: Start_Post End_Post ---------- -------- 102+20.45 153+19.22 120+21.25 220+25.30 ... ... And I want to introduce a constraint to each column: ...
5
votes
1answer
2k views

Sqlite Create Table with Check column from other table

I have two tables: position CREATE TABLE IF NOT EXISTS position( id_position INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, min REAL NOT NULL CHECK (min > 0), max REAL CHECK (max &...
20
votes
1answer
2k views

Postgres: How is SET NOT NULL “more efficient” than CHECK constraint

In PostgreSQL docs for Constraints, it says A not-null constraint is functionally equivalent to creating a check constraint CHECK (column_name IS NOT NULL), but in PostgreSQL creating an explicit ...
5
votes
1answer
4k views

Check constraint on nullable data, how to approach?

This is one of the tables in my database. Constraints omitted for clarity. CREATE TABLE [Person].[Person] ( [ID] INT NOT NULL IDENTITY, [Forename] ...
5
votes
1answer
2k views

In SQL server is a check constraint applied before or after a default constraint on insert?

As per the title: In SQL server is a check constraint applied before or after a default constraint on insert? So taking the following table (Unnecessary columns omitted for brevity)... CREATE TABLE [...
3
votes
2answers
746 views

Can a PRIMARY KEY constraint (with a NONCLUSTERED INDEX) check for only certain values?

Can I set a (logical) PRIMARY KEY constraint, served by a (physical) NONCLUSTERED INDEX, so that it only checks certain values of the constrained and indexed column or another column in the relevant ...
0
votes
1answer
143 views

Query For All Constraints in a SQL Database Diagram

Is there a way to show constraints in a SQL Server db diagram? Like for example, in a card number, it shows there is a CHECK constraint etc..
1
vote
1answer
548 views

How do triggers and check constraints relate to maitaining database rules

I was looking at a tutorial on triggers for SQLite. The tutorial said that a use of triggers could be for data validation. The following code was given as an example: CREATE TRIGGER ...
0
votes
0answers
29 views

Constraint with respect to existing data

Talking about SQL Server. Table contains column A and B. For simplicity - no key. Both columns may contain any values. But with a limitation: if column A receives a value which was already used in ...
0
votes
1answer
118 views

CHECK constraint not implemented in MySQL, what about other DBMS? [closed]

MariaDB, which is based on MySQL, does not. Oracle, including Oracle XE 10g and 11g do. I'm curious as to other DBMS?
1
vote
1answer
102 views

how to get any record in any table that would violate a foreign key constraint that would prevent my delete?

basically I am looking for something like: before you can delete record x from my_table you need to: 1) delete x,y and z from table1 2) delete m and n from table2 3) delete a,b,c from table3 I ...
1
vote
1answer
291 views

CHECK constraint to limit certain rows

I have the task to create an EAV table with some constraints. Attributes are foreign keys and some values are also from other tables (not realized as foreign key, but as a CHECK constraint). On this ...
0
votes
1answer
63 views

MySQL Schema Design: One Possible Values Between Multiple Columns

I'm working on a schema for a MySQL database. I have tables which are derivatives of one another. For example, rather than a single "animals" table, I might have separate "mammals" and "birds" tables: ...
0
votes
1answer
279 views

Mysql columns mutual exclusion

I want to create a table which has 2 columns that would only allow one of them to have value in the same row. I tried with CHECK constraint but it not working correctly Ex: SQL Fiddle schema ...
1
vote
0answers
943 views

How to create a check between two attributes of two different tables

I have to create a check to know if the date in Livraison (Delivery in French) is >= Date in Commande (Order in French) I Hhad some issues on the SQL : We did it in BonDeLivraison (DeliveryTicket in ...
4
votes
1answer
1k views

Constraint on row value based on values in other rows

Is it possible to have a table with four columns: CREATE TABLE accrual ( add_date date NOT NULL, user_id integer NOT NULL, rate integer NOT NULL, amount numeric(7,3) ); and then ...
1
vote
0answers
209 views

pg_restore slow after adding a check function

I have a postgres database with 10 schemas, and recently I had to find a way for some tables between schemas to have common records. The way I did this was to remove the foreign key on some tables in ...
6
votes
1answer
1k views

Foreign key with additional constraints?

There is a table called Item(id, name, cost) and Orders(id, bill_id, item_id, units), which is created to track orders placed, where same bill_id means it belongs to a single order. How to impose an ...
4
votes
1answer
798 views

Surrogate vs composite key in hierarchical data structure

I'm currently evaluating a schema for a hierarchical data structure. My main problem is how I should design the schema to prevent inconsistent data (reference of foreign key in another hierarchy). The ...
8
votes
5answers
4k views

Enforce NOT NULL for set of columns with a CHECK constraint only for new rows

I have a table and need to add a new column without a default value: Constraint: ALTER TABLE integrations.billables DROP CONSTRAINT IF EXISTS cc_at_least_one_mapping_needed_billables, ADD ...
1
vote
0answers
73 views

In Postgres, is it possible for me to have a table partitioning constraint based on data type?

Say I have the following schema for my Postgres database: CREATE TABLE sources ( source TEXT NOT NULL, PRIMARY KEY (source) ); CREATE TABLE ipaddrs ( addr INET ) INHERITS (source): CREATE ...
2
votes
0answers
510 views

SQL Server Check Constraint Lock Order

I'm having problems with SQL Server 2012 filetable. Filetable defines an check constraint on a path_locator field with definition as: /*ParentsAreDirectories*/ (filetable_parents_are_directories((...
4
votes
4answers
10k views

ERROR: check constraint is violated by some row

I've got the following error: ERROR: check constraint "cc_at_least_one_mapping_needed" is violated by some row Query: ALTER TABLE integrations.tax_aggregates DROP COLUMN IF EXISTS myob_id, ADD ...
0
votes
1answer
589 views

Check Constraints on Decimals In SQL Server 2012

I have the following check constraint on an NVARCHAR(255) column: ([weekReviewWk10]='A' OR [weekReviewWk10]>=(0) AND [weekReviewWk10]<=(25)) However, when someone enters, say, 23.5, SQL Server ...
2
votes
2answers
113 views

Does any SQL engine have navigation support in CHECK clauses?

Imagine a simple database to track personal spending. The most basic form of the model should let you create any number of accounts and any number of transactions (specifying the spending amount, date ...
0
votes
1answer
151 views

Do i need “CHECK CONSTRAINT” when i am using “WITH CHECK ADD CONSTRAINT”

I am working on SQL Server 2008 R2, where I generated a schema-only database script using SQL Server Management Studio. The generated script is as follows: ALTER TABLE [dbo].[ConsoleServer] WITH ...
3
votes
1answer
2k views

Table structure for validating data using category and subcategory fields

I am creating a simple budget application, and the primary table will be a transaction table that contains things like the amount of the transaction, date, account, the category and subcategory (the ...
1
vote
1answer
51 views

Can I enforce one field being dependent on another?

I would like to know if it's possible to have two fields (in this case bits) in the same table where one is dependent on another. For example Check2 may only be true if Check1 is true. Note: I'm ...
3
votes
2answers
5k views

PostgreSQL CHECK constraint is evaluated wrongly

Following schema allows to violate the CHECK constrait, but only once. Then the constraint is correctly enforced. drop database if exists example; create database example; create table example_table ...
0
votes
2answers
54 views

Check attribute table referenced foreign key

I have two tables a and b like this: a: id, type b: a_id, ... There is a limitation for which the references of a in b can only be those which have type equal to a limited set of values. Is it ...
0
votes
1answer
1k views

Validate a column to accept only certain age limit from birthdate given using check constraint [closed]

A business rule required my table to only registrater certain dob from bithDate column to be inserted or allow. I created the check constraint but i kind of having logical error i not able to spot ...
2
votes
1answer
590 views

“No Loop” Constraint for hierarchical data in postgresql

I've got table like this in postgres: id primary key parent_id foreign key on id some data columns I need no a constrain which prevents cycles on the parent relations. There might be more tree in ...
-1
votes
2answers
65 views

What happens when play with constraints [closed]

I'm inserting data from one DB to one other on another instance by using the following method: Create insert into scripts from the source DB. TargetDB: DROP constraints, insert data and Create ...
1
vote
1answer
258 views

Table-based custom type does not verify check constraints

Is there any way for a table-based custom type to obey the table check constraints, other than creating my own type based on the table? Assume the following table that serves as a source for the ...
6
votes
1answer
7k views

Change constraint on column based on value of another

Is it possible to change a constraint on a column in postgres based on the value of another column? E.g. (pseudocode): CREATE TABLE transactions( id SERIAL PRIMARY KEY, type TXN_TYPE NOT NULL,...
0
votes
1answer
324 views

Oracle Missing Comma error on check constraint

I'm attempting to create a table in Oracle, but keep getting the "ORA-00917":missing comma error at the end of my first constraint (P_IMAGEFILE_CHECK). I've tried adding and taking away commas but ...
1
vote
2answers
7k views

Compare Indexes/Constraints between two SQL Server databases

I have two databases on one SQL Server that I would like to compare Index/Constraints. The two databases should be identical but I have a suspicion that one database is missing a index or constraints. ...
22
votes
2answers
43k views

Disable all constraints and table checks while restoring a dump

I've obtained a dump of my PostgreSQL database with: pg_dump -U user-name -d db-name -f dumpfile which I then proceed to restore in another database with: psql X -U postgres -d db-name-b -f ...
3
votes
2answers
15k views

Check Constraint with Case Statement

I want to add a check constraint on a column if entered value for column is greater than 3 than it should be saved as 1 else entered value. For that scenario I am using below query but on insertion of ...
3
votes
1answer
10k views

Check constraint references another column in same table

I have this table but check constraint is throwing me an error: Column CHECK constraint for column 'MedicamentRegulated' references another column, table 'Medicaments'. I've seen this as allowed ...
4
votes
1answer
3k views

Check constraint for Canadian postal code?

Is there a way to check if a Canadian postal code is valid? Not sure if something like this would work: PostalCode varchar(10) CHECK(PostalCode>='t1w1v1' and PostalCode<='t9w9v9'
0
votes
1answer
764 views

Create Table with Int-attribute which can only use values between 6 and 36

Hello I'm learning for my databases examination and in one exercise they asking for creating a table with a attribute which can only use values between 6 and 36. How do I solve it? We used in the ...