Default constraint question

Question

Post reply

Default constraint question

vdivakaruni

SSC Journeyman

Points: 76
More actions
February 18, 2005 at 12:13 pm

#362033

In my exiting table I am adding to add two new fields (Col1 & Col2) of data type 'uniqueidentifier' and both should NOT be NULL. I defined a default constraint on Col1 get a new GUID using newid() function, now I want the same value to be stored in Col2 as well (not a new GUID but same value as in Col1). I want achieve it by defining a constraint on Col2.

Can anyone suggest me how I can do that? I want to avoid creating a trigger on Col2.

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

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

1 - Why would you keep the same data in 2 different columns of the same data type?

2 - If you really want to do it you're gonna need a trigger (or a job that runs periodically to update the column).

John Jakob SSCommitted Points: 1720 More actions · Answer 2

Well,

OK I'm curious why you want to have two columns that contain exactly the same information -- seems unnecessary, redundant, and it violates basic normalization principles.

Must you have a constraint on Col2? If not, you might try using a computed column in your "CREATE TABLE" statement to give you "Col2"--for example:

-- Create Table

CREATE TABLE MyTable

(

Col1 UNIQUEIDENTIFIER NOT NULL DEFAULT(NEWID()),

Col2 AS Col1 -- Computed Column

)

GO

-- Populate

INSERT INTO MyTable(Col1)

SELECT NEWID()

-- Show Table Contents

SELECT * FROM MyTable

/**** Output:

Col1 Col2

------------------------------------ ------------------------------------

75434477-6A9A-4C1D-B7C3-9F367453847A 75434477-6A9A-4C1D-B7C3-9F367453847A

(1 row(s) affected)

****/

You might also consider creating the table in the regular fashion -- with just the first UNIQUEIDENTIFIER column ("Col1"), and then creating a VIEW around the table that contains both "Col1" and "Col2"...

HTH.

- john

vdivakaruni SSC Journeyman Points: 76 More actions · Answer 3

John,

Thanks for your help.

The table is already in the production and I am adding these two columns. Why I want to have the same data in both fields?

When I upgrade my database I want to start off with both the columns having the same value. However, going forward the client application (a specific functionality of it) is going to modify or provide the data for Col2 and that value (for Col2) could be the same or different from the value in Col1.

Can I still achieve this functionality if I create/alter the table the way you suggested?

Thanks

rhunt SSCertifiable Points: 5868 More actions · Answer 4

It might be easier to leave col2 as NULL until the APP updates the column. In fact, that would tell you even more about col2 since the app may set col2=col1 all on its own anyway.

Then you can have col2= IS Null, col1, or other.

John Jakob SSCommitted Points: 1720 More actions · Answer 5

No, but given your situation, I would just do what "rhunt" described above.

<quote>Can I still achieve this functionality if I create/alter the table the way you suggested?</quote>

vdivakaruni SSC Journeyman Points: 76 More actions · Answer 6

The reason behind all this is...

The table maintains the account information, and the Id in Col2 is used to represent the account uniquely whereas Col1 represents its parent account Id (which could be Col1 of another account if there is a parent). When this particular functionality is introduced for the first time, we want start off with all the accounts’ parent id (Col1) pointing to its own account Id (Col2). And going forward, the application is going to take care of the link. My concern is whatever ID in Col2 should also be automatically populated into Col1 during the database upgrade process. How can I do that?

Did I confuse you?

Thanks