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

learn more… | top users | synonyms (1)

25
votes
15answers
14k views

How to implement a 'default' flag that can only be set on a single row

For example, with a table similar to this: create table foo(bar int identity, chk char(1) check (chk in('Y', 'N'))); It doesn't matter if the flag is implemented as a char(1), a bit or whatever. I ...
-1
votes
1answer
44 views

How to add default constraint in Oracle while altering column [closed]

I'm new in Oracle and my requirement is to port SQL Server statement to Oracle. I want to alter a column with adding a new constraint with a default value. SQL Server Query for the same: ALTER ...
0
votes
0answers
24 views

Can I write a constraint that requires two rows joined via a junction table to both have the same foreign key in Postgres? [duplicate]

I have a database of data collection instruments (e.g. questionnaire, test, etc.) based on the DDI standard. I am allowing categories to be reused within an instrument for multiple code lists, but ...
3
votes
1answer
171 views

Constrains relationship

I have the following design. Each user has it's own contacts, it's own hosts and assign those to a domain which belongs only to him. I want to insure that the host you assign to the domain belongs ...
0
votes
1answer
209 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 ...
0
votes
0answers
25 views

Cannot delete Postgresql constraints that were former unique keys

I have modified unique constraints. I am adding a field to every unique constraint. To do that, I need to drop the existing unique constraints and create the new ones. I am trying to delete foreign ...
4
votes
1answer
83 views

Foreign Key Constraint Set Back to Trusted

We have some production tables with numerous constraints that are not trusted at the moment. We're having them checked/trusted so they can be used again. We know that the optimizer has not been ...
2
votes
0answers
33 views

Report subscription deletion failed due to FK constraint

We have many SSRS report timed subscriptions (SQL Server 2008R2 SP4). One of our report developers tried to delete a report subscription, and got the following error: An error occurred within the ...
98
votes
6answers
33k views

When should I use a unique constraint instead of a unique index?

When I want a column to have distinct values, I can either use a constraint create table t1( id int primary key, code varchar(10) unique NULL ); go or I can use a unique index create table t2( id ...
2
votes
0answers
50 views

Constraint violation in a trusted constraint

I have a table with a constraint on it that has an existing record that is in violation of that constraint. While this is possible if someone applied the constraint without a check and after ...
0
votes
0answers
26 views

Schema Locks blocking during Alter Table Add Constraint?

I am dealing with a customer using MS SQL Server with Entity Framework to generate their database upgrades. EF generates a "Migration Script", which in this case is a fairly straightforward collection ...
2
votes
3answers
124 views

Can't add Foreign Key Constraint

As far as I can tell it should work, I went through other similar questions but it seemed to be a different thing causing the same error. CREATE TABLE Job ( JobNumber INT NOT NULL, JobName ...
1
vote
1answer
31 views

Limiting the input to a field as some pattern

i have a table student having field admission no. i want to restrict its input to the pattern '-/----/--'. If the input does not match this format then it should show an error. I tried doing this ...
0
votes
1answer
43 views

Error text: “Key … is not present in table” even though the key is there?

Using Postgresql 9.5 on Ubuntu 16.04. Importing large .csv files into a new database using COPY FROM. The database is created by the same script that attempts to import the .csv files, so I know the ...
1
vote
0answers
60 views

database creation [closed]

I'm just starting using data bases and I want to create a table which will store some values and 2 other tables in it. If I were using java for instance I would create an array list of table document ...
1
vote
2answers
70 views

Read only postgres table, except by trigger

Suppose the following situation: I have a product_stock table and a stock_transaction_log table. Currently whenever there is an increase/decrease in a product's stock, I update the product_stock ...
5
votes
1answer
153 views

Data inconsistency prohibition if a table refers to another via two many-to-many relationships

I have following database design (by => foreign key constraints are depicted): Company[id] CompanyRealm[id, company_id=>Company.id]many2many Project[id, company_id=>Company.id] ProjectRealm[id, ...
-1
votes
1answer
67 views

-2147483648 value in integer type column despite entry mask [closed]

I have a desktop application with a textbox field that is a RadMaskedNumericInput with #6 masked value, meaning it can only have 6 digits. I store it in a database column of int type. When I examine ...
0
votes
0answers
28 views

Foreign keys not trusted but they were not disabled [duplicate]

Is there any other way that a foreign key would be marked as "is_not_trusted" besides disabling them and re enabling them incorrectly? They were created without the NOCHECK option initially (meaning ...
0
votes
0answers
33 views

Cannot drop constraint due to FK references

I have detected a duplicate index on an id field on one of our tables, as that column is the PK, but there's also a separate UNIQUE index on such column, which is useless. But I cannot delete the ...
7
votes
2answers
4k views

Why does Postgres generate an already used PK value?

I'm using Django, and every once in a while I get this error: IntegrityError: duplicate key value violates unique constraint "myapp_mymodel_pkey" DETAIL: Key (id)=(1) already exists. My ...
1
vote
1answer
49 views

Limit the number of records depends on the part of primary key

I created database for football matches. I have 2 tables with a many-to-many relationship, so I also created a junction table: Referee CREATE TABLE referee ( id_referee INT IDENTITY PRIMARY KEY, ...
0
votes
1answer
41 views

MySQL InnoDB constraint does not work

I stumble upon strange behavior with innoDB constraint, and cannot find cause of it. I have tables with data. Below listed their structures: CREATE TABLE `contents` ( `id` int(10) unsigned NOT ...
3
votes
1answer
27 views

enforcing data integrity for sequential relations

These are my tables: create table trips ( trip_id serial primary key, trip_nm text ); create table trip_segments ( segment_id serial primary key, departure_ts timestamp with time zone, -- ...
1
vote
1answer
81 views

SQL Error: OA-12991 - Unable to Drop a Column after removing all referenced Multi-Column constraints

I am new to Oracle DB and am in need of some help. I would like to drop a specific column but i am getting a specific error. The issue specifically is: SQL Error: ORA-12991: column is reference ...
0
votes
1answer
23 views

complex mysql constraints over foreign keys

It seems rational to me to stop users or bad codes from inserting invalid data, but I don't remember to see this anywhere! Consider the following tables How I can make sure an order is always ...
0
votes
1answer
55 views

Don't know why MySQL is not creating table with primary key constraint [closed]

I can't understand why MySQL showing this error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '...
0
votes
1answer
2k 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
1answer
54 views

Constraint to prevent overlapping ranges in an array field in PostgreSQL

If I have a tztrange field in a table, I can make sure that no two rows have values that overlap using an exclusion constraint. To make sure that no two appointments can be scheduled at the same time, ...
2
votes
1answer
39 views

sqlite3 foreign keys ignored

I am working on a small sqlite3 database for a small simple mailserver. I have created a new sqlite3 database and add executed this to pürepare it for data: BEGIN TRANSACTION; CREATE TABLE `mailboxes`...
4
votes
1answer
107 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 ...
3
votes
1answer
70 views

Constraint to prevent overlapping Time periods

I have a pretty simple table that stores a StartTime and an EndTime, both with the data type of Time(0). I need to enforce a constraint that prevents any overlapping. 'Touching' time periods are OK, ...
1
vote
1answer
49 views

Check constraint

check (phone like'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]') This is to check the phone number whether it has exactly 10 digits. But I have no idea about placing number as '[0-9]'. It ...
3
votes
4answers
5k views

Unique constraint across two columns

I need to add a constraint with two columns that says if any given value is present in one of the columns, then: 1) It cannot be duplicated in the same column. 2) It cannot be duplicated in the ...
1
vote
1answer
17 views

Deleting data from some but not all tables

I've got a moderately large sql server db for which some data is imported from an external system, and some is entered through the front end or set up when the app is set up. I want to delete all the ...
0
votes
1answer
30 views

Constraint Information - PostgreSQL 9.2

I was trying to delete some data from the table dm.billables: 1 - Select the id - PASS SELECT * INTO junk.clientid_10306 FROM dm.billables WHERE account_id = 10306; 2 - Delete the data from the ...
2
votes
1answer
71 views

Add a primary key constraint to an existing unique column

I've seen lots of information around adding a new primary key column to a large database table, but I have a somewhat different situation. I have a large table (~90,000,000 records), that has no ...
0
votes
1answer
16 views

Adding field constraints on child tables based on parent table

I need to add constraints to a particular field in all the entries of a child table with a foreign key to a single row in parent table. What is the best way to do it? For example, my parent table has ...
0
votes
0answers
37 views

Reset default value for many foreign keys in SQL Server

I need to reset the foreign keys default values to null for a big database by using some sql queries. My research in the stackoverflow website got me to drop the existing default values contraints and ...
2
votes
1answer
73 views

Cascade deletes from multiple tables

I have a fairly standard restaurant model with a restaurant table in a PostgreSQL DB. All restaurants have ratings (which consist of average, vote_count and vote_sum) but in order to avoid repeating ...
3
votes
1answer
183 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 ...
5
votes
2answers
547 views

Foreign Keys doesn't work as I expect

Here are my tables; CREATE TABLE `borclular` ( `id` int(11) NOT NULL AUTO_INCREMENT, `tc` char(11) COLLATE utf8_turkish_ci NOT NULL, `borclu` varchar(255) COLLATE utf8_turkish_ci NOT NULL, ...
2
votes
4answers
221 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 ...
4
votes
3answers
19k views

Listing indexes and constraints

I am looking at a SQL Server database for an application I have inherited. I have not looked into SQL Server for approximately 10 years, so please bear with me. The database table I am looking at has ...
5
votes
2answers
377 views

CREATE as SELECT with CONSTRAINT?

I have a piece of SQL that creates a TABLE form a SELECT statement in PostgreSQL ; I want to add constraints to the table so that for example a column cannot be null. I cannot find valid SQL for this ...
1
vote
0answers
61 views

How do I disable constraint checking during schema migration?

How do I disable FOREIGN KEY checking for the time of database schema migration? Currently I found engine-based commands like: PostgreSQL: SET CONSTRAINTS ALL DEFERRED; MSSQL: EXEC ...
4
votes
1answer
28 views

Enforcing that a user should have one or more email addresses

I'm trying to design an application where a user can authenticate oneself with one password and one or more email addresses. I'm not sure how to enforce this constraint. The tables so far are simple: ...
4
votes
1answer
69 views

How can I exclude range overlaps if one range may be empty?

Suppose I want to ensure that, for any expected height in feet, I have a single, unambiguous classification. I create this table: CREATE TABLE heights_in_feet( size TEXT NOT NULL, min INTEGER NOT ...
12
votes
1answer
6k views

MATCH FULL vs MATCH SIMPLE in foreign key constraints

I've noticed a MATCH SIMPLE or MATCH FULL option in phpPgAdmin, but I can't find a description in the docs. The default is MATCH SIMPLE. How do they function?
1
vote
2answers
683 views

With MS SQL Server are the generated constraint names predictable?

When you create your primary key, and foreign key constraints in a CREATE TABLE script, there are named constraints that are created, such as FK__RecentlyVi__ScId__2764765D ... are these constraints ...