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
0answers
5 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
161 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 ...
1
vote
1answer
24 views

MySQL unique index only if column has specific value

I have a table where I use a boolean column deleted to signify whether a specific record has been 'deleted' by the user (meaning, it appears in the trash list instead of the normal list). These items ...
1
vote
1answer
142 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 ...
0
votes
1answer
352 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?
1
vote
1answer
28 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 ...
0
votes
1answer
29 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 ...
0
votes
1answer
28 views

Constrain Partial Values in Timestamp

I'm working on a some Create Table statements for a project I have. I have two columns (StartDate, EndDate) where the data type is Timestamp. One of the requirements is to ensure that the date is ...
6
votes
2answers
3k views

How can I help SQL Server recognize my indexed view column is NOT NULL-able?

I have the following indexed view defined in SQL Server 2008 (you can download a working schema from gist for testing purposes): CREATE VIEW dbo.balances WITH SCHEMABINDING AS SELECT user_id ...
1
vote
1answer
90 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
223 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 ...
3
votes
1answer
94 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
6k views

Stored Procedure Create Table from Variable with Variable Constraint

I have managed use a store procedure to create a copy of a table with a variable name. But I am struggling to understand how to incorporate a constraint into the stored procedure. The problem: The ...
0
votes
2answers
60 views

How to I establish a 1 to 1 relationship

I have a table Nurse: CREATE TABLE [dbo].[Nurse]( [EmployeeId] [int] IDENTITY(1,1) NOT NULL, [WardId] [int] NOT NULL, CONSTRAINT [PK_Nurse] PRIMARY KEY CLUSTERED ( [EmployeeId] ASC ...
4
votes
2answers
793 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 ...
0
votes
0answers
34 views

How to combine a default value with a new value?

I need to start the value of a column with CON, so when I insert a new value, it needs to "combine" both default and new value; for example: In the CONTACTO_ASP2_TAB table, the column NOMBRE has to ...
2
votes
1answer
73 views

Why does MySQL increment the auto_increment value when there's a constraint violation?

I have a table in a MySQL database with one column id, which has auto_increment and the Primary Key. There's also a Unique constraint in this table, on different columns. I noticed that when I try to ...
0
votes
1answer
51 views

I see a constraint in my table thats not in INFORMATION SCHEMA?

When I expand the database "Constraints" object, I see 2 of them named DF_DocumentD_Docum_3F466844 and df_documentdistqty, but when I select to view everything from the "Information Schema.TABLE ...
22
votes
3answers
14k 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), ...
2
votes
1answer
27 views

Is it possible to enforce a Unique Constraint on a Dataset comprised of joins

I am playing around with an idea to describe friendships in a system, and I am wondering about constraints. I currently have a database that looks like this: Users: ...
2
votes
3answers
132 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 ...
5
votes
2answers
53 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
124 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 ...
0
votes
1answer
74 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 ...
1
vote
2answers
131 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
85 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
23 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
66 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
2k 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
87 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, ...
5
votes
4answers
882 views

Making a column immutable in MySQL

I want to make a column in a relation unmodifyable for consistency reasons. The backstory is that I have a n:n relationship where the "connecting" relation has additional values. I don't want that ...
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
33 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
67 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 ...
39
votes
5answers
13k 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 ...
0
votes
1answer
993 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
820 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 ...
0
votes
2answers
118 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 ...
3
votes
1answer
443 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 ...
0
votes
2answers
577 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
138 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 ...
0
votes
1answer
28 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
637 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
183 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 ...
2
votes
1answer
77 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
38 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
141 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 ...
10
votes
3answers
7k views

check constraint does not work?

I have the following table. create table test ( id smallint unsigned AUTO_INCREMENT, age tinyint not null, primary key(id), check (age<20) ); The problem is that the CHECK ...
0
votes
0answers
46 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
30 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 ...