Tag Info

New answers tagged

2

To enforce unique email addresses, I would remove all competing email columns and store them in one central email table for all active emails. And another table for deleted emails: CREATE TABLE users ( user_id serial PRIMARY KEY , username text UNIQUE NOT NULL , email text UNIQUE -- FK added below -- can also be NOT NULL ); CREATE TABLE email ( ...


1

One way to think of this would be that you have two classes of user which have slightly different rules: deleted and not deleted. Deleted users e-mails may clash, not deleted users e-mails must be unique. Because these two classes have different rules (i.e. constraints), instead of using a flag to indicate whether or not a user is deleted, I would duplicate ...


2

In the table centresdevote you have a row where the value of objectid is 10. As you are trying to create a FOREIGN KEY CONSTRAINT there is a check that there is a matching value in the column objectid in the table prefectures for all the rows in centresdevote. In prefectures there is no row with objectid = 10. I suggest you read The PostgreSQL Documentation ...


2

When you have a unique constraint that you want to apply to only a subset of rows, you can enforce this using a unique, filtered index. The index that seemed to work for you in this case is: CREATE UNIQUE INDEX [UNQ_SampleTable_Code] ON dbo.[SampleTable]([Code]) WHERE ([Deleted] = 0); This ensures that only one distinct value of Code can exist for ...


2

The first one is a unique constraint. It can be added to an existing table with: ALTER TABLE ADD CONSTRAINT ... Details in the manual here. It is implemented using a unique index. Per documentation: Adding a unique constraint will automatically create a unique btree index on the column or group of columns used in the constraint. A uniqueness ...


2

You can use a CHECK constraint with a CASE expression. If check2 is enabled, make sure check1 is enabled too, otherwise you can default to a no-op ("checking" that check1 = check1). For simplicity I'm assuming these columns are not NULLable. CREATE TABLE dbo.foo ( check1 BIT NOT NULL DEFAULT 0, check2 BIT NOT NULL DEFAULT 0, CONSTRAINT ck_both CHECK ...



Top 50 recent answers are included