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)

1
vote
1answer
92 views

Should child comment be treated as separate table?

I'm building a commenting system, currently my table indicates which comment is child with parent id in 1 table, I would like to know what is the best way of doing this? Current Table Scheme (1 Table)...
8
votes
2answers
159 views

SQL Server equivalent of Oracle USING INDEX clause

Is there a SQL Server 2008 equivalent of the USING INDEX clause in Oracle? Specifically for the construct: CREATE TABLE c(c1 INT, c2 INT); CREATE INDEX ci ON c (c1, c2); ALTER TABLE c ADD CONSTRAINT ...
1
vote
1answer
53 views

Constrain/limit certain records in sql table definitions

Let's say I have posts on which users can leave a comment. And I want to limit the amount of comments to a certain post to 10, is it possible to add such constraint/restriction to the table? My guess ...
1
vote
1answer
55 views

Indirect constraints (foreign keys amongst three/four tables) [duplicate]

I have the next layout of tables: table COMPANY: - primary key pk_company table USERS: - primary key pk_users, - foreign key (fk_company) references COMPANY (pk_company) table GROUPS: <br> - ...
1
vote
1answer
65 views

Enforce a single NULL end date per foreign key value using an index

I want to implement a Check Constraint across a date range and foreign key. I have a table created as: Primary Key Foreign Key DateStart DateEnd The ForeignKey can appear many times in the table ...
5
votes
2answers
914 views

Conditional Foreign Key Relationship

I currently have a foreign key between two entities, and I would like to make that relation conditional to the entityType of one of the tables. Here's the hierachy of tables, this is done via FK ...
1
vote
1answer
51 views

Using REGEX_LIKE to Implement a Check Constraint

Background I am working on the database design for an application that tracks information about database development projects at my company. The project management office (PMO) assigns a psued-...
17
votes
4answers
463 views

Entity relationship problem

I have 4 tables related like this (it's an example): Company: ID Name CNPJ Department: ID Name Code ID_Company Classification: ID Name Code ID_Company Workers: Id Name Code ID_Classification ...
11
votes
3answers
445 views

Constraint to enforce “at least one” or “exactly one” in a database

Say we have users and each user can have multiple email addresses CREATE TABLE emails ( user_id integer, email_address text, is_active boolean ) Some sample rows user_id | ...
5
votes
1answer
445 views

How to enforce a constraint over multiple tables?

I have the following five tables: PRODUCT FEATURE PROVIDER PROVIDER_PRODUCT PROVIDER_FEATURE The logic is as follows: A generic PRODUCT can have one or more FEATUREs. There are different ...
3
votes
1answer
156 views

Enabling Constraint Silently Fails

Why would SQL Server silently fail to re-enable a foreign key constraint? I recently attempted to re-enable a number of constraints using ALTER TABLE. Most enabled successfully; however, minority of ...
2
votes
1answer
84 views

Can Indexes mitigate untrusted Constraint

I using sp_blitz and found two tables that have foreign keys that is untrusted. tried to enable the trust with: ALTER TABLE MyTableName WITH CHECK CHECK CONSTRAINT MyConstraintName; GO But got this ...
0
votes
1answer
59 views

Is unique index combined with a “not null” constraint equivalent to a PK constraint?

I have these legacy table definitions (from here): CREATE TABLE [dbo].[JobItems] ( [ItemId] UNIQUEIDENTIFIER NOT NULL, -- lots of other columns CONSTRAINT [PrimaryKey_GUID_HERE] ...
0
votes
1answer
96 views

Create an “int” column forced between 0 and 3

I would like to create a table with one column that would be of type "integer", and locked between 0 and 3. Is this possible? My goal would be to have this column so that I could just execute a ...
3
votes
3answers
176 views

Cap on number of rows matching a condition in Postgres

Say I have a db schema like this user_id text photo_id text date_created timestamp and I want to enforce a constraint that says "a user can only have 30 photos". A naive way to implement this would ...
2
votes
1answer
167 views

Foreign Key Constraint over two tables

I am trying to put the following entity-model into practice: We have a list of Payments, each Payment belongs to a certain store and is of a certain payment type (e.g. 10€ maintenance cost in Store1,...
0
votes
0answers
76 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
25 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
0answers
37 views

Reset default value for many foreign keys in SQL Server

I need to reset the foreign keys default values to null for a big database by using some sql queries. My research in the stackoverflow website got me to drop the existing default values contraints and ...
0
votes
2answers
37 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
53 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
73 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 ...
0
votes
0answers
157 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
66 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
114 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.
3
votes
1answer
186 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 ...
4
votes
1answer
5k views

How to check foreign keys related to a table

How to see foreign keys related to a table in MySql? Background : I wanted to drop a table in MySql which has a foreign key constraint. When I do it I get this: Error Code: 1217. Cannot delete or ...
2
votes
1answer
833 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
184 views

Multi-column 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 ...
2
votes
0answers
52 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
100 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
143 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
376 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
644 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
50 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
56 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
586 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. ...
1
vote
1answer
585 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
994 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
74 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
30 views

Can I enforce one field being dependent 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 dependent on another. For example Check2 may only be true if Check1 is true. Note: I'm ...
0
votes
2answers
28 views

Added a unique index, but cannot find it in TABLE_CONSTRAINTS

I'm running the following command in my DB: CREATE UNIQUE INDEX [UK_NAME1] ON [TABLE1] ([COLUMN1] ASC); Now, I'm running the following query: SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS ...
0
votes
3answers
2k 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 b....
0
votes
1answer
350 views

Setting a column's default value by adding a constraint

I wrote this query: ALTER TABLE test2 add constraint setting_value33 default male FOR namee; But I get this error when running it: You have an error in your SQL syntax; check the manual that ...
0
votes
0answers
135 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
1answer
210 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
266 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 ...
8
votes
1answer
8k views

How do I drop all constraints from all tables?

I want to drop all default constraints, check constraints, unique constraints, primary keys and foreign keys from all tables in a SQL Server database. I know how to get all the constraint names from ...