Tag Info

New answers tagged

1

Aftee searching the Ideone FAQ, it seems that it uses Sqlite3 for the SQL language. Sqlite needs PRAGMA foreign_keys = ON; to enable foreign keys checks. As they say in their docs: SQLite Foreign Key Support : Assuming the library is compiled with foreign key constraints enabled, it must still be enabled by the application at runtime, using the PRAGMA ...


4

Since both PersonID and EmployeeID are candidate keys (and presumably will be implemented with uniqueness constraints) your proposed table design satisfies Boyce Codd Normal Form and therefore 3rd Normal Form as well. That doesn't necessarily make it a good idea. In principle, it's no bad thing to provide alternative identifiers for things but in this case I ...


-1

This approach does break a normalization rule in that employeeid and customerid are functionally dependent on personid. A database such as you describe could not be said to be in the Third normal form for this reason. Further expansion for discussion based on answer feedback: Let's assume that the tables under discussion take the following form: Person ...


0

You can use a before trigger on INSERT or UPDATE of ID to the b table. This would verify that the new value of b.id is of the correct type. This technique can also be used where the values in referenced tables can be logically disabled, but needs to be retained.


2

It's possible to add a_type to b, and have check constraint on b.a_type. Breaking normalization a bit allows enforcing storing details in proper table . In Oracle you can have 2 constrains supported by the same index, for instance PK on a.id, and UNIQUE on (a.id,a.type) , then some tables that require proper type may have FK to (a.id,a.type), and other ...


0

The schema name of a table is not stored in the column table_name, it's stored in the column table_schema, so you need to change the condition: and ccu.table_name in ('plugin') to and (ccu.table_schema, ccu.table_name) in (('a', 'plugin'))


5

If you insert multiple Order records (pick a new name that's not a keyword, by the way) then you need to use the OUTPUT clause. Otherwise SCOPE_IDENTITY() should work. Quick example using SCOPE_IDENTITY: DECLARE @OrderID int INSERT INTO Orders (<columns> VALUES (....) SET @OrderID = SCOPE_IDENTITY() INSERT INTO Properties (OrderID, ...


0

The only "bad idea" that I can imagine from doing this, is that you cannot grant the REFERENCES object privilege (the one needed to create a constraint that refers to a table) to a role. I has to be done schema/user by schema/user. Besides that, I dont see the point of your DBA.


0

More indexes --> slower INSERTs. (However, this may not be a critical issue.) More indexes --> slower UPDATEs when you modify an indexed column. Indexing a flag (or other low-cardinality field) --> almost never will the optimizer use that index, so it is a wasted index. (I'm guessing is_active is such.) However, a compound index that includes that flag ...


-1

If anyone encounters this problem in phpmyadmin, it could be due to the fact you have multiple identical constraints on a field. That can happen sometimes. Check with SHOW CREATE TABLE to obtain all constrains. In case you find duplicates, erase one of them. Then, in phpMyAdmin, you will probably need to setup all constraints from beginning for that ...



Top 50 recent answers are included