Best way to add constraint?

Question

Post reply

Best way to add constraint?

Lenny Kramer

Grasshopper

Points: 17
More actions
February 3, 2014 at 5:01 am

#303976

I'm adding a new column GenderID to a table Student.
I am also adding a new table dbo.Gender with the the columns:
GenderID
GenderDescription
Values in table Gender are:
GenderID GenderDescription
1 Male
2 Female
3 Unknown
There is a relationship between the GenderID in the Student table and the GenderID in the Gender table. I want to add a foreign key constraint, so that every row in Student table has a valid reference to Gender table.
The Student table currently table has a few hundred rows.
Now I want to do this all at once in a deployment script.
Normally I would write my script as this.
---------------------
--(Code to deploy table Gender and add data to it)
ALTER TABLE dbo.FactFxForward ADD GenderID int NOT NULL
GO
ALTER TABLE dbo.FactFxForward ADD CONSTRAINT FK_dbo_Student_GenderID FOREIGN KEY (GenderID) REFERENCES dbo.Gender(GenderID)
GO
--------------------------
This will fail since I already have rows in student table.
Then I could add a default value of 3(=unknown)
------------------------------------------------------------
--(Code to deploy table Gender and add data to it)
ALTER TABLE Student ADD GenderID int NOT NULL DEFAULT(3)
GO
ALTER TABLE Student ADD CONSTRAINT FK_dbo_Student_GenderID FOREIGN KEY (GenderID) REFERENCES dbo.Gender(GenderID)
GO
------------------------------------------------------------
This will fail on the second statement because the Default statement that will create a constraint. That constraint will conflict with the one I am creating.
I suppose this will work, but it seems like a lot of code?
---------------------------------------------------------------------
--(Code to deploy table Gender and add data to it)
ALTER TABLE Student ADD GenderID int
GO
UPDATE Student SET GenderID=3
GO
ALTER TABLE Student ALTER COLUMN GenderID int NOT NULL
GO
ALTER TABLE dbo.FactFxForward ADD CONSTRAINT FK_dbo_Student_GenderID FOREIGN KEY (GenderID) REFERENCES dbo.Gender(GenderID)
GO
----------------------------------------------------------------------
What does your beautiful solution look like?

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Igor Micev SSC-Dedicated Points: 33110 More actions · Answer 1

Hi,

This will fail since I already have rows in student table.

Why should it fail? It seems you have data that could not be referenced, but not because of the fact you have rows in Student table.

You should find out the rows in Student that have non-existing values in Gender table.

Regards,

IgorMi

Igor Micev,My blog: www.igormicev.com

Lenny Kramer Grasshopper Points: 17 More actions · Answer 2

Hello this is the message I get.

ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column GenderID cannot be added to non-empty table Student because it does not satisfy these conditions.

Igor Micev SSC-Dedicated Points: 33110 More actions · Answer 3

Lenny Kramer (2/3/2014)
Hello this is the message I get.
ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column GenderID cannot be added to non-empty table Student because it does not satisfy these conditions.

Oh, OK, it's because of the fact you could not add a non-nullable column.

Does the code you're proposing works?

It should be, and it's fine.

Igor Micev,My blog: www.igormicev.com

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 4

Why are you explicitly adding an unknown value, rather than allowing NULL to indicate unknown?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ed Wagner SSC Guru Points: 287026 More actions · Answer 5

Maybe I'm missing something here, but your second approach worked fine for me. I did notice that in some statements you're ALTERing the student table and in others you're ALTERing the FactFxForward table. If this is what you're actually running, then that could explain it. Other than that, I see no reason why your second approach would have any problem.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Lenny Kramer Grasshopper Points: 17 More actions · Answer 6

Yes, seems like I messed up, the second solution works. Thanks all.

Jeff Moden SSC Guru Points: 1004645 More actions · Answer 7

My recommendation would be to NOT use numerics here. M, F, U, etc adds clarity and avoids confusion. If you really want to do it right, lookup the ISO standard for these abbreviations because there are some odd ones that you've not considered.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)