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
1answer
24 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
35 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 ...
0
votes
1answer
266 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 ...
2
votes
1answer
40 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). ...
10
votes
2answers
6k views

What does 'ibfk' stand for in MySQL?

If I create a foreign key constraint for table 'photos' in phpmyadmin, I later see that the constraint is named 'photos_ibfk_1', and the next constraint is called 'photos_ibfk_2', etc. From this I ...
0
votes
1answer
63 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
23 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 ...
13
votes
1answer
5k views

PostgreSQL EXCLUDE USING error: Data type integer has no default operator class

In PostgreSQL 9.2.3 I am trying to create this simplified table: CREATE TABLE test ( user_id INTEGER, startend TSTZRANGE, EXCLUDE USING gist (user_id WITH =, startend WITH &&) ); ...
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
0answers
125 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
55 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
3answers
1k 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 ...
2
votes
1answer
106 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 ...
0
votes
1answer
79 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.
2
votes
1answer
536 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 ...
1
vote
0answers
39 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
77 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
114 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
290 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 ...
1
vote
1answer
284 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 ...
0
votes
1answer
49 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
50 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 ...
2
votes
2answers
287 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
0answers
46 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 ...
1
vote
1answer
522 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
52 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 ...
1
vote
1answer
1k views

SQL Azure: Drop Constraint and Index

This may be a stupid question, but I'm not a DBA and just wanna make sure of what I'm doing before I make a mistake. Here is my question: When dropping Constraints, are their indexes dropped as well? ...
0
votes
0answers
96 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 ...
40
votes
3answers
33k 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), ...
0
votes
0answers
136 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
179 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 ...
3
votes
2answers
252 views

Finding violations of the symmetry constraint

Suppose I have a table Friends with columns Friend1ID, Friend2ID. I chose to represent each friendship with two records, say (John, Jeff) and (Jeff, John). Thus, each pair of friends should show up ...
-1
votes
2answers
60 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
174 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
627 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 ...
2
votes
1answer
347 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
63 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
34 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 ...
1
vote
1answer
307 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
1k 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
102 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
10k 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
143 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 ...
6
votes
2answers
3k 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
51 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
188 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
83 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
36 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
714 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 ...