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

learn more… | top users | synonyms

0
votes
1answer
74 views

Create an “int” column forced between 0 and 3

I would like to create a table with one column that would be of type "integer", and locked between 0 and 3. Is this possible? My goal would be to have this column so that I could just execute a ...
8
votes
3answers
134 views

Constraint to enforce “at least one” or “exactly one” in a database

Say we have users and each user can have multiple email addresses CREATE TABLE emails ( user_id integer, email_address text, is_active boolean ) Some sample rows user_id | ...
1
vote
1answer
328 views

Adding foreign keys constraint on databases used

We have a server with about 20 databases, all duplicates of the same original db, and we just noticed the original is missing all the foreign keys constraint for some unknown reason. So of course all ...
3
votes
1answer
125 views

How to enforce a constraint over multiple tables?

I have the following five tables: PRODUCT FEATURE PROVIDER PROVIDER_PRODUCT PROVIDER_FEATURE The logic is as follows: A generic PRODUCT can have one or more FEATUREs. There are different ...
3
votes
1answer
121 views

Enabling Constraint Silently Fails

Why would SQL Server silently fail to re-enable a foreign key constraint? I recently attempted to re-enable a number of constraints using ALTER TABLE. Most enabled successfully; however, minority of ...
2
votes
1answer
60 views

Can Indexes mitigate untrusted Constraint

I using sp_blitz and found two tables that have foreign keys that is untrusted. tried to enable the trust with: ALTER TABLE MyTableName WITH CHECK CHECK CONSTRAINT MyConstraintName; GO But got this ...
2
votes
3answers
43 views

Cap on number of rows matching a condition in Postgres

Say I have a db schema like this user_id text photo_id text date_created timestamp and I want to enforce a constraint that says "a user can only have 30 photos". A naive way to implement this would ...
0
votes
1answer
46 views

Is unique index combined with a “not null” constraint equivalent to a PK constraint?

I have these legacy table definitions (from here): CREATE TABLE [dbo].[JobItems] ( [ItemId] UNIQUEIDENTIFIER NOT NULL, -- lots of other columns CONSTRAINT [PrimaryKey_GUID_HERE] ...
2
votes
1answer
119 views

Foreign Key Constraint over two tables

I am trying to put the following entity-model into practice: We have a list of Payments, each Payment belongs to a certain store and is of a certain payment type (e.g. 10€ maintenance cost in ...
2
votes
1answer
124 views

Multi-column uniqueness constraint, checking a field has one of two values

I'd like to enforce that only one row in a database matches value A in column 1, and either value B or value C in column 2. A small example create table builders ( id text serial ); create table ...
3
votes
1answer
340 views

Postgres 9.3 - deletes terribly slow for 10 minutes after dropping indexes/constraints

If I run a script which drops indexes (17 of them, CONCURRENTLY makes no difference except that without it I get the occasional deadlock), then constraints (20 constraints on 18 tables) and then run a ...
0
votes
0answers
46 views

MariaDB error on alter table - table removed - constraint name is blocked

Today, we experienced a strange behavior in 5.5.41-MariaDB (InnoDB): An ALTER TABLE (add constraint) statement failed with error (mysql server has gone away after that). Then the database table ...
0
votes
1answer
22 views

Verify conditions before creating a constraint

I've taken over an application database that has been grown from an Access db. As such, the database is in need of some significant refactoring. The first step that I'm planning is adding a number of ...
0
votes
2answers
29 views

Basic SQL question about setting constraints

In code such as the following, what is the need to write constraint pk_dept primary key (deptno) if just simply primary key (deptno) works? Is pk_dept something the database uses internally, since it ...
0
votes
1answer
147 views

Can't add a foreign key constraint in a table on Postgresql

I'm trying to add a foreign key of "prefectures" table in "centresdevote" table. But I'm having this error while trying to add a foreign key to "centresdevote" table. I had no problem adding foreign ...
2
votes
1answer
32 views

Detect circular relations in a distribution table

I have the following two tables, that will define distribution rates from items on table A to other items on table A (I think I can say B defines a many-to-many relationship for A with itself). ...
10
votes
2answers
5k views

What does 'ibfk' stand for in MySQL?

If I create a foreign key constraint for table 'photos' in phpmyadmin, I later see that the constraint is named 'photos_ibfk_1', and the next constraint is called 'photos_ibfk_2', etc. From this I ...
0
votes
1answer
57 views

SQL Server transactional replication issue with database

We have on the server a database and we want to replicate a database on another server. we've set a transactional replication, but we have issues with the default constraints on the tables: On the ...
2
votes
0answers
17 views

SQLITE Foreign key has to be re-added? [closed]

Currently I have a mysql database on my computer which I convert to sqlite database. On the sqlite database, I have transaction_detail table with a foreign key to transaction_master table. The ...
12
votes
1answer
4k views

PostgreSQL EXCLUDE USING error: Data type integer has no default operator class

In PostgreSQL 9.2.3 I am trying to create this simplified table: CREATE TABLE test ( user_id INTEGER, startend TSTZRANGE, EXCLUDE USING gist (user_id WITH =, startend WITH &&) ); ...
0
votes
1answer
1k views

What is the difference between covering and overlapping constraints use in DBMS?

What is the difference between covering and overlapping constraints use in DBMS?
0
votes
0answers
48 views

What order do foreign key constraints run in? Why does my foreign key not work after import?

I have two tables tags and tag_memberships in a PostgreSQL database. On the tag_memberships table, I have two constraints. The first is this: ALTER TABLE tag_memberships ADD CONSTRAINT ...
2
votes
1answer
41 views

Can column uniqueness be commutative?

I have a table in a Postgres DB where col1 and col2 are foreign keys referring to the same column. I want only unique combinations of (col1, col2), i.e. if (1,2) is entered then (2,1) should be ...
23
votes
2answers
11k views

Check constraint only one of three columns is non-null

I have a (SQL Server) table that contains 3 types of results: FLOAT, NVARCHAR(30), or DATETIME (3 separate columns). I want to ensure that for any given row, only one column has a result and the other ...
0
votes
3answers
486 views

check key if exists in other table without fk constraint

Lets say i have: table a (id_a int); table b (id_b int, id_a int); I want to check if b.id_a exists in "a" before a new row in "b" will be inserted. Normally I would just add a fk constraint on ...
1
vote
1answer
53 views

Multi-tenant database constraints

I have a multi-tenant app in PostgreSQL that has a repeated tenant_id column in each table to help isolate the tenants from each other. My question is, is there any sort of way that I can use ...
0
votes
1answer
53 views

How can we drop a constraint without losing the automatically created index?

How can we drop a constraint without losing the automatically created index? If it's possible using a single query.
2
votes
1answer
132 views

Deferrable Foreign Key Constraints

What are the consequences of making a foreign key constraint DEFERRABLE INITIALLY IMMEDIATE instead of NONDEFERRABLE? This answer on StackOverflow mentions the performance impact that comes with no ...
1
vote
0answers
21 views

Compound key that only allows nulls if they are null in the referenced table

I want to create a compound foreign key constraint that only allows a null value in one of the columns if there is a matching row with a null value in the referenced table. Additionally, the ...
0
votes
0answers
48 views

Cleaning up orphaned metadata in information_schema

OK - so the server (Percona 5.6 in this case) 'went away' in the middle of declaring a constraint like so: alter table mytable add constraint fk_mytable_myothertable foreign key (myothertable_id) ...
0
votes
2answers
73 views

In mysql, find all instances of a named column that are not foreign keys

This one has had me trumped for a while! I want to run a query to retrieve all instances of a column (from information_schema, I guess) that ARE or ARE NOT foreign keys. Now, let me back of a little ...
1
vote
1answer
130 views

Postgres - copy table INCLUDING CONSTRAINT doesn't include constraint

How come that statement don't copy constraints if I explicitly ask for it? CREATE TABLE backup_schema.permissions (LIKE public.permissions INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES ...
1
vote
1answer
150 views

trigger or check constraint insert values based upon existence of others

For simplicity (and since it captures the essence of my problem), suppose I have a table with data. CREATE TABLE my_practice_table ( my_id NUMBER(12), field_2 VARCHAR2(20), field_3 ...
0
votes
1answer
49 views

Creating a constraint

I created a report that records entries from a table which has 4 columns 1 Shipper ID 2 SHipper Reference 3 loading 4 Completion My primary keys are columns 1 and 2 there are no foriegn keys, ...
0
votes
0answers
38 views

Is there a workaround for the lack of foreign keys constraint to a partitioned table in PostgreSQL?

I have a big table A (~30 million lines) at the core of my model. Another table B has a foreign key referencing A. I would like to partition A using the well-known inheritance hack. Is there a ...
2
votes
2answers
123 views

Unique across tables

I have a table containing users. Each user has a primary email and a flag indicating if the user is deleted or not (we never hard-delete users). However, each user can also have additional emails. ...
0
votes
0answers
33 views

Constraints on a junction table of many entity types?

Let's say I have the following database table and columns: Event: event_id, event_name School: school_id, school_name Automobile: automobile_id, automobile_name User: user_id, username ...
1
vote
1answer
200 views

Enforce a column as unique based on another column value

I want to make a column unique, but only if a different column is a specific value. Consider the following table: CREATE TABLE [SampleTable] ( [Id] INTEGER NOT NULL IDENTITY(1,1) ...
0
votes
1answer
40 views

What does a CONSTRAINT have to do with my unique index?

I have to indexes on my table. The first was created by Django and the second by me. I'm not completely certain what the extra CONSTRAINT means in the first index and am wondering how I would change ...
0
votes
1answer
16 views

Field Prerequisite 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 prerequisite on another. For example Check2 may only be true if Check1 is true. Note: I'm ...
1
vote
1answer
1k views

SQL Azure: Drop Constraint and Index

This may be a stupid question, but I'm not a DBA and just wanna make sure of what I'm doing before I make a mistake. Here is my question: When dropping Constraints, are their indexes dropped as well? ...
0
votes
0answers
59 views

Cannot drop constraint in SQL server 2008

When I try to delete a constraint, it gives me the following message: "The constraint is used by merge replication for identity management and cannot be dropped directly. Execute ...
35
votes
3answers
26k views

PostgreSQL multi-column unique constraint and NULL values

I have a table like the following: create table my_table ( id int8 not null, id_A int8 not null, id_B int8 not null, id_C int8 null, constraint pk_my_table primary key (id), ...
0
votes
0answers
58 views

Email Column Constraint to Contain '@' character

How do you create an inline constraint for a Email Address Field that requires there be an "@" character? Pretty much I want there to be a restriction to the field so that the data entered must have ...
2
votes
1answer
80 views

How to enforce an “exactly-one-per-group” constraint?

I have a table x that I've defined like this: CREATE TABLE x ( xid INTEGER NOT NULL PRIMARY KEY, yid INTEGER NOT NULL REFERENCES y(yid), is_principal BOOLEAN NOT NULL ); This definition ...
0
votes
0answers
31 views

Can I create a constraint to a subset of data? [duplicate]

Here's a simplified view of my database |---------------| |-----------| | ORGANIZATION | | CONTACT | |---------------| |-----------| | OrgId | | ContactId | | OrgName ...
3
votes
2answers
222 views

Finding violations of the symmetry constraint

Suppose I have a table Friends with columns Friend1ID, Friend2ID. I chose to represent each friendship with two records, say (John, Jeff) and (Jeff, John). Thus, each pair of friends should show up ...
-1
votes
2answers
57 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
237 views

Performance of single vs multiple column unique constraint

I'm using PostgreSQL 9.3. I want to understand if I have an option to make a constraint unique across the entire table vs. unique across a subset of the table (i.e. by using 2 columns in the unique ...
1
vote
1answer
168 views

Enforce a constraint problem

This business model is for a case management database. This is closely modeled on the idea of a file folder representing the phase and a sequential checklist representing the stages. A case consists ...