![]() See the fiddle here for Oracle's behaviour under these circumstances the key name will vary from fiddle to fiddle, but is some arbitrary string beginning with SYS_. Being told that key "SYS_C00308108" is being violated is not very helpful. You can experiment yourself with the fiddle to see what happens when you leave out the NOT NULL on either (and both) field(s) - the behaviour isn't always intuitively obvious!ĪLWAYS give your foreign keys meaningful names. Unless I have a really compelling reason, I always declare my columns as NOT NULL - it helps the optimiser and reduces the potential for confusion/error. The fiddle has NOT NULL constraints on x (a) and on y (c). ON UPDATE CASCADE ON DELETE CASCADE - or other Referential Integrity ActionĪ couple of points to note (see the fiddle here) - an attempt to insert a value into y (c) which is not in x (a) fails and the constraint name is given in the error message. ![]() This is because NULLs are not equal to each other, nor are they equal to anything else - each NULL is considered UNIQUE in its own right! CREATE TABLE x(a INT PRIMARY KEY) - could also use UNIQUE NOT NULL) ĬONSTRAINT y_x_fk_c REFERENCES x (a) - if x (a) doens't exist, this will fail! Note that the parent column has to have a UNIQUE constraint (or be the PRIMARY KEY) but it doesn't have to be NOT NULL. I did the following (from here and the documentation). ![]() The FOREIGN KEY (aka parent) column has to already exist in order to make it an FK. ![]() You just have to add another step - in fact PostgreSQL is already telling you that: column "sender" referenced in foreign key constraint does not exist. ![]()
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |