Tag Info

New answers tagged

1

I would suggest NOT to use Designer in creating key constraints or doing any DDL / DML operations. Best is to use T-SQL - much flexible, more options, more powerful and you can automate many things with it. There is a learning curve, but its worth learning TSQL rather than keep using GUI. Also, make sure you are using the latest version of SSMS - sql ...


0

What you describe is frequently called a "joining table" - a many to many connection. Athough the word "associative" is readily understandable in the context, I would use the term "joining" table. You can put either a PRIMARY KEY or a UNIQUE KEY on the fields (case_id, tag_id) - no need for a PRIMARY KEY with id - it's superfluous.


1

For that many-to-many 'relation' table, don't bother to have an id. Simply have PRIMARY KEY(case_id, tag_id), INDEX(tag_id, case_id) Note that one of those works perfectly for one of your SELECTs, the other works perfectly for the other. (Use ENGINE=InnoDB.) To get (tag-1, tag-2, tag-3), use GROUP_CONCAT(). FOREIGN KEYs are optional; I would not ...


2

A semantically appropriate way of expressing this would be: select ... from a where not exists ( select null from b where b.x = a.x) NOT EXISTS uses an anti-semi-join which allows the optimiser to be very responsive to the table and join cardinalities.


0

session_replication_role I found an alternative way of disabling foreign keys -- http://stackoverflow.com/a/18709987 set session_replication_role = replica; And re-enabling them with set session_replication_role = default; This works on RDS but still requires unusual privileges (i.e. not granted by default). dropping and recreating FKs Alternative ...


1

Given the lack of sample tables I have to make this up.... select * from parent_table where parent_table.foreign_key_id in (select parent_table.foreign_key_id from parent_table minus select child_table.id from child_table) where the parent_table.foreign_key_id might match to child_table.id


2

I usually use this statement. with fk_list as ( select parent_table.table_name parent, parent_cons.constraint_name as pk_constraint, child_table.table_name child, child_cons.constraint_name as fk_constraint from user_tables parent_table join user_constraints parent_cons on parent_table.table_name = parent_cons.table_name ...


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



Top 50 recent answers are included