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 ...


1

Have two columns in tblSchoolTerm one called TermSchoolID and the other YearSchoolID. Add a check constraint that enforces they are equal. From the information given, however, it would seem to me that your model is incorrect. I would have thought a Term would relate to a Year, then School would have many-to-many with Term. You know your business rules best, ...


1

If you are looking just for referenced tables name, this code will help SELECT o.name FROM sysobjects o WHERE EXISTS ( SELECT 1 FROM sysconstraints C JOIN sysreferences R ON R.constrid=C.constrid WHERE EXISTS ( SELECT 1 FROM sysobjects o2 WHERE o2.id=C.tableid AND o2.name ='tableName' ) AND R.reftabid=o.id )



Top 50 recent answers are included