Hot answers tagged foreign-key
34
Yes it's a terrible idea.
Instead of going:
SELECT Deal.Name, DealCategory.Name
FROM Deal
INNER JOIN
DealCategories ON Deal.DealID = DealCategories.DealID
INNER JOIN
DealCategory ON DealCategories.DealCategoryID = DealCategory.DealCategoryID
WHERE Deal.DealID = 1234
You now have to go:
SELECT Deal.ID, Deal.Name, DealCategories
FROM Deal
...
29
Put the foreign keys on the database. Even if you validate the data in the application before you save it the FK's are a good piece QA backup. For a first approximation, applications always have data issues. Leaving controls like this out of the system just invites failure modes where data gets corrupted silently.
There's nothing like working in data ...
11
Referential Integrity should be handled on the lowest possible level, which would be the underlying database. Relational Database Management Systems are optimized to handle this. It doesn't make sense to reinvent the proverbial wheel.
It is acceptable to define domain logic in the application code to prevent the DML statement to even cause an RI exception, ...
10
Every result from a SQL operation is functionally a new table, whether or not it is stored on disk or in memory
The function of a join is to "Join" two tables together into a synthetic third table that (usually) only exists in memory during the time it is output to the application.
The reason to use a join is to reduce Data Anomalies, by insuring that data ...
10
I'm going to go out on a limb here fully expecting this to get down-voted since this is a DBA-focused group.
I agree that using strict foreign keys is the best decision in most scenarios. However, there are some cases where foreign keys cause more problems than they solve.
When you are dealing with very highly concurrent environment such as a high ...
9
Create a unique index or unique constraint on UserName then you can reference it in a FK constraint fine.
Your statement that
Sql Server doesn't allow me to create a relationship on a non primary
key column
is incorrect. SQL Server only cares that the column(s) participating in the FK relationship have a unique index defined.
9
I'm only familiar with SQL Server:
Each operation is atomic. If you run a delete, and it cascades to other tables, those records are gone, too, as soon as the statement is over. They don't magically come back into existence unless the transaction is rolled back.
If you're relying on the ID values and don't want to cascade the related tables, consider ...
9
To answer your main question directly, the sorts are there to present rows to update operators (performing deletions in this case) in index key order. The principle at work here is that sorting on the keys will promote sequential access to the index.
This can be a good optimization, though the details depend on your hardware, how likely the affected pages ...
7
First of all, if this is homework, please tag it as such.
Secondly if it's not homework and you're doing this in a professional environment, get a professional to do it (or at least to thoroughly scrutinize your final design). Schema design underpins your application design, and flows on from clarity in business requirements and how well you understand ...
7
After some "reverse-engineering" on the queries made by the Navicat tool when opening the design table window for a table (queries retrieving info about foreign keys show up in the history window), here is a solution:
SELECT
CONS.CONSTRAINT_NAME,
CONS.TABLE_NAME,
COLS.COLUMN_NAME,
CONS.R_CONSTRAINT_NAME,
CONS_R.TABLE_NAME R_TABLE_NAME,
...
7
In addition to JOINs another benefit of indexing Foreign Key columns is that it can speed up enforcement of the Foreign Key constraint for some DML operations.
If you delete a row from an Orders table then the RDBMS would need to ensure that this would not leave an orphaned row in OrderDetails. Obviously this is easier if it can be verified with the use of ...
6
Solution 1:
Script all of your foreign keys out and create the tables on the new server without foreign keys. Load the data, then rerun the scripts to create the foreign keys.
Solution 2:
Backup the database, the restore it to the new server.
Solution 3:
Run a query similar to this and figure out the dependencies for yourself.
Pick the one that sounds ...
6
A View is a logical table that is based on one or more physical tables. If there are foreign key relationships in the underlying tables, then they will be manifested in the view. Views are entirely dependent on the tables they are derived from, so trying to add foreign keys to them is not possible.
6
It sounds like you have a "One True Lookup Table" (OTLT) anti-pattern and you are mixing entities in this table. You've found why it isn't a good idea:
can't have filtered foriegn keys
can't FK to constants
can't have multiple parents
Your sample code above is confusing (you have multiple parents for the same Code column) so I'll give you what I ...
6
Whether set null is useful or not depends on what you have chosen null to mean in the particular context - with all the confusion and opinion around null IMO the sensible approach is for the DBA to
Choose (and document) what it means for each nullable field
Make sure it means one thing only
With those rules, consider the following use case:
You have a ...
6
This will be non-specific to oracle, but in general foreign key indexes are most useful because they speed up JOINs.
If you have a foreign key field, it is extremely likely you will use it as a JOIN key as well - that's the whole point of an FK, to make a direct association between one field in tablea to another field in tableb.
Having this field indexed ...
6
It is good practice to use foreign key in the database. It helps-
to keep data integrity by removing the possibility of unwanted data
to increase performance. In systems which auto index fields, foreign key references can give a performance boost
to write less code by the programmer. like, using ON DELETE CASCADE
6
Assuming a company can have more than one shipping address, the key would be a column added on Shipping Address table (aptly named 'customer_id' and the foreign key definition on the Shipping Address table. If you wanna avoid same address more than once, also add a unique key on shipping address table that can include (customer_id, contact_name)
On an ...
6
You can't implement this as a single constraint; you'll need to create two:
ALTER TABLE dbo.B ADD CONSTRAINT FK_HomeTeam
FOREIGN KEY (homeTeam) REFERENCES dbo.A(teamName);
ALTER TABLE dbo.B ADD CONSTRAINT FK_AwayTeam
FOREIGN KEY (awayTeam) REFERENCES dbo.A(teamName);
As I alluded to in my comment, it would be much more efficient to store TeamID in ...
6
Your query got locked waiting for something - my bet is that it was waiting for some other transaction to finish.
When you're doing the create table, just issue (in another psql session):
select * from pg_locks where pid = XXX and not granted;
where xxx is pid of backend doing the create table.
This will show you what is the lock that the create table ...
6
No this isn't possible.
I'd probably create a separate table categoriesWithArticles that holds the relevant categoryids (instead of having the flag) and have the FK reference that table instead.
Another more convoluted way to enforce it and keep the column would be
CREATE TABLE categories
(
categoryId int primary key,
hasarticles bit not null,
...
6
Most database management systems have a hard limit on either the number of columns you're allowed to use, the number of bytes in a row, or both. So your single table won't work in the general case, because you'll eventually either end up with either too many columns or too many bytes in a row.
To find out what the limitations are for your platform, Google ...
6
T1 SPID 93 processec49b8: UPDATE kid SET activityByID=@P0, activityDate=@P1 WHERE kidID=@P2 triggers INSERT INTO ZAT_KID with (PAGLOCK)
T2 SPID 64 processedb6d8: insert into Image ... triggers INSERT INTO ZAT_Image with (PAGLOCK)
T1 wants X page 295182 (ZAT_KID) and has X page 295211 (ZAT_Image). T2 wants X page 295211 (ZAT_Image) and has X on 295182 ...
6
FWIW ISBN is terrible as a primary key. For one, what happens if you get a book you want to put up for pre-order, but the ISBN hasn't been assigned yet? What happens when the ISBN changes (yes, this happens!)? What happens when they change the ISBN format yet again? I would say make that a candidate key but use a surrogate for the PK.
Adding to that the ...
5
I think you're looking for
SELECT username, cities.city FROM Users JOIN Cities ON (Cities.Id = Users.City)
which would give you
| username | city |
| John | Singapore |
| Toby | London |
| Eric | Paris |
but don't take my word for it: try it out on your database and see what you get!
5
You want to have something like this:
This allows you to have any number of work periods per staff member and client and gives you the details of who worked for who and how long (not to mention when - which is also very important!)
5
You can do this (assuming I'm understanding you correctly) via having foreign keys in your child tables referring to the parent table.
CREATE TABLE Parent (
ParentID INTEGER PRIMARY KEY)
CREATE TABLE ChildOne (
ChildOneID INTEGER PRIMARY KEY,
ParentID INTEGER,
FOREIGN KEY (ParentID) REFERENCES Parent(ParentID) )
CREATE ...
5
In MySQL, RESTRICT and NO ACTION are synonyms:
In MySQL, foreign key constraints are checked immediately, so NO ACTION is the same as RESTRICT. [src]
Now, you are asking how this affects a DELETE FROM column1 WHERE first_id='XX' if the table is defined like so:
CREATE TABLE `column2` (
`second_id` int(11) NOT NULL AUTO_INCREMENT,
`first_id` int(11) ...
5
No, you can't depend on this. SQL is declarative, not procedural, so within a statement you can't guarantee the order of execution. Since the entire INSERT ALL statement is considered a single statement (doc), you can't guarantee that one INSERT will be before another.
By definition an INSERT FIRST must execute the first INTO passing the evaluated ...
5
Possible options for the ON DELETE and ON UPDATE clauses are explained in the CREATE TABLE page of the manual.
The default is NO ACTION, so if you have any foreign keys defined without explicitly specifying what action to be performed on updates of the referenced primary or unique key, it is the same as if ON UPDATE NO ACTION was specified:
NO ACTION
...
Only top voted, non community-wiki answers of a minimum length are eligible
