SQL – Query on table creation, referential integrity

database-design

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?

Best Answer

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.

Related Solutions

Fact table foreign keys null

You can leave the FK to some dimension tables as NULL if those dimensions are not known or not applicable. You just have to remember to use outer joins when you do your reporting query.

Alternatively, some people create a "none" and/or an "n/a" dimension record for data mart dimensions and then populate fact table FKs to point at these rather than using NULLs. People who do this like this approach because they have an aversion to outer joins.

People who use NULL FKs in fact tables usually have an aversion to people who have an version to outer joins. ;) (in other words, this is a stylistic issue which can generate religious wars)

I say do whichever you prefer, but pick one approach and stick to it fervently.

Sql-server – With MS SQL Server are the generated constraint names predictable

No, the constraint name is completely unpredictable. If you want your names to be consistent, you can name them correctly by applying a predictable / repeatable name manually. I have no idea how you would do this in the code you have, but in T-SQL instead of:

CREATE TABLE dbo.foo(bar INT PRIMARY KEY);

CREATE TABLE dbo.blat(bar INT FOREIGN KEY REFERENCES dbo.foo(bar));

(The above end up with constraints having names like PK__foo__DE90ECFF6CF25EF6 and FK__blat__bar__1B1EE1BE.)

You would say:

CREATE TABLE dbo.foo(bar INT, CONSTRAINT PK_foo PRIMARY KEY (bar));

CREATE TABLE dbo.blat(bar INT, CONSTRAINT fk_foobar FOREIGN KEY(bar) 
  REFERENCES dbo.foo(bar));

Best Answer

Related Solutions

Fact table foreign keys null

Sql-server – With MS SQL Server are the generated constraint names predictable

Related Question