Please let us know what does the below definition means on table creation.
CREATE TABLE A(w INT PRIMARY KEY);
CREATE TABLE B(x INT PRIMARY KEY REFERENCES A(w) ON SET NULL)
As per my understanding, there is a foreign key reference on attribute-B(x) with attribute-A(w) and B(x) will set to null when A(w) is deleted that reference to B(x). Will this not violate the primary key constraint on X, which cannot be NULL?
Your scenario makes no sense. You want something like
CREATE TABLE B(x INT REFERENCES A(w) ON DELETE (SET NULL | CASCADE | NO ACTION)
What you have is a 1-1 relationship between your tables, so x should really be in table A - you are trying to implement a parent-child relationship with only one possible child. Furthermore, you are trying to SET a primary key field to NULL, which is not possible. It is possible to have a UNIQUE KEY with multiple fields with NULL values for some of those fields. To answer your question directly, it will violate the PRIMARY KEY constraint and will throw an error at some point, either at table declaration or during testing/production.
