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
vote
1answer
3k views

How to insert data into tables which reference each other?(MySQL)

I'm a student and right now I have a problem that I couldn't solve by myself(after searching on the Internet). I'm not going to give my «homework» because I want to learn what I'm doing but I will ...
1
vote
1answer
177 views

How to force MySQL Workbench to use constraints in my file?

I'm currently working on a project that requires I write my own constraints for my database (to show I know how they work). I'm attempting to test them in MySQL Workbench however the programs settings ...
0
votes
2answers
280 views

SQL Check Constraint that requires a not null value in a column based on the value of another column

I have a table with the following two columns OnSite BIT NOT NULL ClientId INT NULL I want to add a constraint that requires ClientId to be NOT NULL when OnSite is true(1). I could not find ...
0
votes
2answers
1k views

Primary key within JSON data in Postgresql

I have a table t, with a column called json, of type JSON. Within the JSON there is a natural key: > SELECT json->'id' AS id FROM t LIMIT 1; id ...
1
vote
1answer
179 views

Alternatives to NULL for missing data [duplicate]

I am using PostgreSQL and trying to write value constraints. Is it possible to record missing data as distinct from NULL values in a numeric field without using an arbitrary numeric value? For many ...
3
votes
1answer
98 views

constraint in oracle

I'm trying modeling things like this using oracle: An account can have 1~5 members So I'll have a account table and a member table with a FK of account. But is that possible to check if the account ...
0
votes
1answer
30 views

Require uniqueness with group by

I'm not sure how to couch this question. I have a table that has multiple columns including an id column, a foreign key column 'JobId', and a 'GxpId' column. I have a requirement that a 'JobId' can ...
2
votes
3answers
3k 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 ...
1
vote
1answer
434 views

How do I validate all my constraints in postgresql?

It seems like I have been bitten by the first bug described in the release notes of postgresql 9.3.4: http://www.postgresql.org/docs/devel/static/release-9-3-4.html I now have e.g. duplicate primary ...
0
votes
1answer
898 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?
2
votes
1answer
87 views

Confused on how a value gets added with the DEFAULT constraint

A recent definition I've read about Default Constraints: DEFAULT is specified for a column to assign a column a specific value if no value is specified when a row is added. I believe I've seen code ...
2
votes
1answer
41 views

How can I model and enforce constraints on a categorical item contained in a collection?

Scenario: There is a collection of items where each item belongs to 1 category. There is a collection of buckets which may contain at most 1 item from each category. Oh, and an item may belong to at ...
2
votes
1answer
231 views

Two relations to same table in SQL Server

I've got two tables. When I delete a row in Table1, I want to set the ID of that row in all the rows of Table2 to NULL. I've got two relationships on Table1. The problem is: I can only set one delete ...
0
votes
0answers
56 views

Two different unique constraints for 1 table?

I must enforce uniqueness within my table, But I cannot seem to find anywhere online(mysql manual,forums,stack overflow) talk about two unique constraints within one table. Why you might ask I need ...
0
votes
1answer
33 views

design DB review and check constraint for postalcode help?

Hi. I created this database to just to sharpen my skill. I have one Question - when I create I make the FOREIGN KEY personID in Addresses table but I changed it to be AddressID in Persons table ...
1
vote
0answers
925 views

User defined table type with a check constraint

I have a user defined table type where I want to make sure that a certain column is within a given range. This range however is defined in another table. So I thought I can use a check constraint on ...
0
votes
1answer
87 views

Is it possible to have a unique key condition based on another value?

I have a table with the following columns: ID, STRING_VALUE, ENUM_VALUE, CATEGORY_IDENTIFIER I'd like to create a condition that won't allow two identical ENUM_VALUEs, but only if their ID is also ...
2
votes
1answer
302 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
1answer
5k views

MYSQL - Problem with foreign keys on same table

In Mysql 5.5, I have a table, in which I store some informations about "events".. In order to have a chronological order, I made two columns that reference to the previous and to the next event. So I ...
2
votes
1answer
166 views

Does a SQL server unique constraint partially or fully fail a multi-row write?

If some rows of a writing query fails a unique constraint, will only those writes be rejected, or will the entire query fail?
0
votes
0answers
144 views

Error adding constraint foreign key to bridge table

Hi there I am fairly new to sql development. I am current following the head first into mysql book and is on chapter 6. On chapter 6 there is a table in third normal form but it does not contains the ...
0
votes
2answers
181 views

Accept null value on parent column in multipart relationship

EDIT: I've come up with one poor solution (below) that requires some denormalization, but does avoid the risks of UDFs in CHECK constraints. I would appreciate your help if you have a better answer. ...
2
votes
1answer
2k views

Two-column foreign key constraint only when third column is NOT NULL

Given the following tables: CREATE TABLE verified_name ( id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, UNIQUE (name, email) ); CREATE ...
3
votes
1answer
410 views

On which filegroup will constraints be created by default when adding them to a table on a filegroup and how should they be separeated?

We have multiple filegroups, that look something like this: PRIMARY (default) app_data app_index When I create a table on filegroup app_data CREATE TABLE Person ( [PersonID] INT NOT NULL ...
0
votes
1answer
34 views

Conditional when creating a domain

How can I define or change a domain based on another attribute value? F.e. domain for salary is a numeric value > 500. But if a date the person was hired > January 1st, 2013, then the salary must be ...
1
vote
1answer
101 views

Create constraint for stopping mass update to a table

I want to create a constraint so that if more than 5 rows in a table are getting updated with an UPDATE statement it should warn the user / block the query.
0
votes
0answers
1k views

CHECK constraint ignored during INSERT SELECT

I have the following 2 tables (simplified): CREATE TABLE dbo.[StagingTable] ( HostName [nvarchar] (max) NULL ,UserName [nvarchar] (max) NULL ,AccountType [nvarchar] (max) NULL ) CREATE TABLE ...
1
vote
1answer
193 views

Unique constraint on multiple nullable columns Sql Server

We have a PriceCombo table with the following columns: id (not null), a_id(null),b_id(null),c_id(null), discount(not null). We are using this table to figure out which price to assign various combos ...
1
vote
0answers
321 views

postgresql multi column unique or check constraint

First a word of pleading. To say I am a newbie to postgresql would be an insult to the newbies! I have been working on this for only a day or so. I have found several questions on this site, and ...
2
votes
1answer
114 views

How can I change Referential Integrity from a Unique Key Constraint to a Primary Key?

(without dropping foreign keys) I have migrated a SQL Server CE database to SQL Server 2012, however, referential integrity was enforced via unique key constraints on the surrogate INT identity ...
2
votes
1answer
291 views

PostgreSQL 9.2 tstzrange null/infinity CONSTRAINT CHECK

I'd like to limit a PostgreSQL 9.2 tstzrange to valid dates at both ends. No NULLs nor 'infinity'. Various revisions of this SQL isn't constraining '-/+infinity' input: CREATE TABLE bill ( id ...
0
votes
0answers
778 views

PostgreSQL Cascade Delete not working

What am I missing here? I have two tables: CREATE TABLE source ( "sourceid" serial, "state" character varying(255) REFERENCES states (state), "leadtime" character varying(255), CONSTRAINT ...
2
votes
1answer
80 views

What can do a table constraint that a column constraint can't do? [closed]

Had an exam today. One question disturbed me: Q: What can a table constraint do that a column constraint can't do? A: My answer was that only a table constraint can declare a composite primary key. ...
1
vote
1answer
166 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 ...
2
votes
1answer
113 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 ...
1
vote
1answer
4k views

Oracle, Create table with inline constraint declarations using a different tablespace

I have two questions about inline constraints declarations for Oracle tables: Is it a bad practice? If so, why? How could one declare a different tablespace for the primary key and index like it's ...
1
vote
1answer
817 views

MySQL: update instead of delete if foreign key constraint?

I have a bit of a strange question. I know about insert on duplicate key update. My question is, is there something similar for deletes that fail because of foreign key constraints? For example: ...
2
votes
2answers
183 views

MySQL - unique constraints design

I am using MySQL and looking to solve a problem with relational data. I am not sure it is possible but wanted to ask the community. Please consider the diagram: I would like to add a unique ...
3
votes
3answers
3k 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 ...
3
votes
1answer
752 views

Complex constraint across all data in a table

We have a table to record processing that occurs on a system and we need to ensure that only a single row has an 'in process' status. I want to ensure that the result of this is always either zero or ...
2
votes
2answers
210 views

Constraint on Composite Type

How can I create a constraint on a sub-field of composite type? Pseudocode create type axis( major_axis float, minor_axis float, angle float constraint angle_constraint check(angle ...
4
votes
2answers
2k 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 database ...
2
votes
2answers
79 views

Lock hints on preconditions

Assuming for a moment that FK references are not available, what lock hints should be placed on the preconditions of the following example to prevent deadlocks and inconsistent data? (This is a ...
2
votes
1answer
2k views

prevent MySQL Workbench to add index for foreignkey constraint when it is first part of primary

I know for each foreign key we should make an index on its column, But it is not necessary when that column in first part of PRIMARY index. MySQL accepts this query: CREATE TABLE IF NOT EXISTS ...
1
vote
1answer
993 views

Constraint based rules engine

I am evaluating software systems which configure products that tout constraint based versus rules based engines. Can someone explain (with maybe an example or two) to a non-expert what the difference ...
1
vote
1answer
357 views

MySQL import LOCK doesn't prevent PRIMARY key issues

I am using Django and only want to backup mysql data (not it's structure; Django will do that, and it will otherwise mess up Django-South's migration checks). Some info; my tables are almost all ...
2
votes
2answers
80 views

Postgresql constrains on FK

I am trying to design a (part of a) database which has to accomplish the following: There is a students table, containing a bunch of students. There are educations in the database. Each student can ...
1
vote
1answer
135 views

How can I achieve a unique constraint with two fields? [duplicate]

I have a table with e.g. Name and IsDeleted fields. I want to add a row constraint so that only one Name value can have IsDeleted as 'false'. There can be many duplicate Name values, but they must all ...
0
votes
0answers
121 views

Why does mysql show Primary key constraint on a View?

As far as I know views cannot have a primary key. However the result of SELECT FROM KEY_COLUMN_USAGE says differ. PRIMARY | S_MOBILE_ITEM_VIEW | ID | ...
3
votes
2answers
3k views

Constraints based on other columns

Is it possible to limit what values are allowed in a coulumn based off of other values in the row? For example, my table: ID Test_mode Active -- --------- ------ 1 1 Null 2 0 ...