Tag Info

New answers tagged

6

You don't need triggers or PL/pgSQL at all. You don't even need DEFERRABLE constraints. And you don't need to store any information redundantly. Include the ID of the active email in the users table, resulting in mutual references. One might think we need a DEFERRABLE constraint to solve the "chicken/egg" problem of inserting a user and his active email, ...


5

If you can add a column to the table, the following scheme would almost1 work: CREATE TABLE emails ( UserID integer NOT NULL, EmailAddress varchar(254) NOT NULL, IsActive boolean NOT NULL, -- New column ActiveAddress varchar(254) NOT NULL, -- Obvious PK CONSTRAINT PK_emails_UserID_EmailAddress PRIMARY KEY (UserID, ...


4

The only way to do either of these without schema changes is with a PL/PgSQL trigger. For the "exactly one" case, you can make the references mutual, with one being DEFERRABLE INITIALLY DEFERRED. So A.b_id (FK) references B.b_id (PK) and B.a_id (FK) references A.a_id (PK). Many ORMs etc cannot cope with deferrable constraints though. So in this case you'd ...


4

This can easily be done by using cascading keys that overlap. Here is an example using the Oracle Data Modeler (note there is a bug in this tool or a configuration issue as the Provider_Feature table should show each column as PF meaning both PK and FK): In this example, the PK to Provider Product includes the Product Number provided, and the PK to ...


8

The problem you're facing is that you are not actually re-enabling your constraint (as defined by the NOCHECK). The syntax for re-enabling a constraint is as follows: ALTER TABLE <tablename> WITH { CHECK | NOCHECK } CHECK CONSTRAINT <constraintname>; The WITH CHECK|NOCHECK tells SQL Server whether or not you want to check the existing data in ...


0

thank you for your answers. I am using SQLite3 local on my laptop for testing purpose, I am not sure what the "real" database will be. So for that reason and because it does not seem easy to restrict values in a certain range, I have solved my problem in the software itself. Using the "AND" condition to check if values are in the interval I want: "UPDATE ...


0

Just for a bit of background on pros/cons to not having FK's in your database feel free to review this post on StackOverflow. As you can see from that article there are some good reasons (performance penalty on insert/update more painful to the business than dirty data) to go without foreign keys and some bad reasons (laziness/ignorance). I think you ...


1

Similar to what @Eelke and @Kassandry suggested. But you don't need a counter or trigger. CREATE TABLE t1 ( user_id text NOT NULL -- should probably be integer , photo_nr int NOT NULL , photo_id text NOT NULL , date_created timestamptz NOT NULL DEFAULT now() , CONSTRAINT t1_pkey PRIMARY KEY (user_id, photo_nr) , CONSTRAINT max_30_photos CHECK (photo_nr ...


0

Right now, both your unique index and PK uniquely identify each row. This is redundant. The index can be remove and the cluster can be move into the PK. However it will fail if there are foreign keys on ItemId. First look for foreign key(s): Select OBJECT_NAME(constraint_object_id) From sys.foreign_key_columns where referenced_object_id = ...


2

Some notes first: Considering that Accounts has a unique constraint on (account_type, account_store), a Payment seem to be associated with only one Account. That suggests that the FK from Payments should reference Accounts (and not Stores). This change will also solve issue #2. Adding account_type to Payments (and modifying accordingly the foreign key to ...


1

You could do something like this: Example table: CREATE TABLE t1 (user_id text, photo_id text, photo_limit_count integer NOT NULL, date_created timestamptz); Add a check constraint: ALTER TABLE t1 ADD CONSTRAINT photo_limit_count_chk CHECK (photo_limit_count > 0 AND photo_limit_count <= 30); Create a unique index, to make sure that there can't ...


2

Your program could manually number the records for each user then you could put a unique constraint on the combination of the user_id and the recordnumber (or make it the primary key) and put a check constraint on recordnumber cheking it is between 1 and 30.



Top 50 recent answers are included