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
48 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 ...
2
votes
3answers
37 views

Constrain a postgres timestamp to “End of day”?

Given a table named bananas and a timestamp without time zone column named end_time and some psql clients have set timezone to 'UTC' and other psql clients have set timezone to 'US/Eastern' and the ...
4
votes
2answers
36 views

How to enforce the structural constraints of rectangularly arrayed data?

(I'm particularly interested in answers that view the question below as a special case of the question: how should a RDBMS enforce structural constraints that are more specific than "one-to-many" and ...
2
votes
2answers
61 views

Adding standard set of constraints (rules) to PostGIS `raster` type column

Following on from my related prior question, let me elaborate this topic a bit. raster2pgsql is a raster loader executable that loads GDAL supported raster formats in PostGIS. It has a -C flag ...
1
vote
2answers
81 views

Creating a unique constraint on a PostGIS 'raster' type column

I am using the following command to add constraints to one of the raster image in PostGIS-2.1.3 (PostgreSQL-9.1.14). ALTER TABLE schema1.table1 ADD CONSTRAINT enforce_scalex_rast unique (rast); But ...
0
votes
0answers
41 views

cascade delete doesn't work even though constraint is added

I created the following table: CREATE TABLE message_link ( id serial NOT NULL, thread_id integer, message_id integer, user_id integer, read boolean NOT NULL, CONSTRAINT message_link_pkey ...
1
vote
1answer
19 views

How to put this particular constraint on this link table in MySQL? [duplicate]

I have the following three tables in MySQL: Store, Groups, StoreGroup. A store can belong to several groups. StoreGroup is the link table that records the groups a store belongs to. The Groups table ...
1
vote
1answer
36 views

Restrict two specific column values from existing at the same time

I have a PostgreSQL example table where at most one row that is not of type 'c' should be allowed. I would appreciate any help creating a constraint that will enforce this. CREATE TABLE example ( ...
9
votes
2answers
284 views

foreign key constraint violation issue

I have identified 3 situations. A student with no enrollments. A student with enrollments but no grades. A student with enrollments and grades. There is a trigger on the enrollments table to ...
0
votes
0answers
86 views

SQL Server - constraint to prevent overlapping dates [duplicate]

I saw this wonderful article called "CHECK Constraint To Avoid Overlapping Data" It basically boils down to this: CREATE TABLE Reservations (ReservationID int NOT NULL PRIMARY KEY, ...
1
vote
0answers
32 views

Clarify unique constraint and index, cannot grasp the concepts to 100% [duplicate]

When reading about UNIQUE, I understand the concept but when it comes to the practical part of it, I get confused and I'm unsure of what it means. What I do understand, is that there are two ways to ...
0
votes
1answer
22 views

PostgreSQL: How does the implicit index work when using “COPY” for loading bulk rows into a newly created table?

What I know for fast loading bulk rows into a new table is: Create the table(without creating the index) Use "COPY" to load data from a file into the table Create the index What if I added a ...
0
votes
1answer
51 views

SQL Server - Is it possible to forbid creation of default constraint name?

I was having a looking on this article about default constraints and I have a question. Is there a configuration in SQL Server 2008 that forbid naming creation of new default constraints declared ...
0
votes
2answers
64 views

Msg 2627 Violation of PRIMARY KEY constraint

trying to make a database for teams in a tv show here. but when I try and insert data into tblShowteam the following error made its appearance. Msg 2627, Level 14, State 1, Line 2 Violation of ...
0
votes
1answer
273 views

Cannot add foreign key constraint while creating table

My Error Code is 1215 which finds problem with the foreign key constraint. I know all fields are the same data types. I can't see where the problem is. Any help would be much appreciated! Thanks! ...
1
vote
1answer
121 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
29 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
70 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
214 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
107 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 ...
2
votes
1answer
71 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
25 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
140 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
112 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
145 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
71 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
37 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
99 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
39 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
26 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
447 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
0answers
79 views

Adding check constraint in Oracle Data Modeller

How can I add a check constraint in data modeller that binds values of one attribute to another? I have two attributes of date type, the entity being the course, one is for start date, the second for ...
0
votes
1answer
68 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
0answers
86 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
1k 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
87 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
65 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
91 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
292 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
137 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
31 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
95 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
573 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
141 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
171 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
87 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
138 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
526 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
64 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
137 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 ...