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
36 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
28 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 ...
2
votes
1answer
29 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). ...
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
15 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 ...
0
votes
0answers
40 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
37 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 ...
0
votes
1answer
49 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.
1
vote
1answer
45 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 ...
2
votes
1answer
89 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 ...
2
votes
1answer
109 views

Multi-row 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 ...
1
vote
0answers
20 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
45 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
70 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
110 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 ...
1
vote
1answer
106 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 ...
0
votes
1answer
48 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
34 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 ...
0
votes
0answers
31 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 ...
2
votes
2answers
97 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
1answer
113 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 ...
1
vote
1answer
121 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
38 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 ...
0
votes
3answers
373 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 ...
0
votes
0answers
54 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 ...
0
votes
0answers
53 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
70 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 ...
-1
votes
2answers
56 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
200 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
342 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
167 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
48 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
32 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 ...
0
votes
2answers
117 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 ...
0
votes
0answers
43 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
142 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
69 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 ...
2
votes
1answer
35 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: ...
1
vote
1answer
301 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
437 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
76 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
317 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
189 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
178 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
32 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
185 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
5k 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 ...