CODEWITHSUNDEEP
postgresql
Joffrey Baratheon
Joffrey Baratheon

Reputation: 474

Adding General Constraints

Here is the relation that I am working with:

enter image description here

The stuff in bold are the primary keys. Part 2 asks me to:

enter image description here

I have done this via:

enter image description here

And then part 3 asks me to:

enter image description here

And I have done it via:

enter image description here

I am having trouble figuring out what to do for 3.3 #2 and I am not sure if 3.3 #3 is correct. For 3.3 #3, it is telling me I have a syntax error near ALTER, but I'm not sure. I'm pretty new to PostgreSQL so I did my best.

My attempt at 3.3 #2 would be:

ALTER TABLE mg_customers
ADD CONSTRAINT no_null_mgc
-- Not sure what to add here

Upvotes: 1

Views: 177

Answers (1)

Tom-db
Tom-db

Reputation: 6868

The solution for 3.3#2 could be:

ALTER TABLE mg_customers ALTER COLUMN address_id SET NOT NULL;
ALTER TABLE dv_address ALTER COLUMN address_id SET NOT NULL;

This commands will fail if some row of table mg_customers or of dv_address have a null address_id. In this case you must first edit your data and make sure that every row of mg_customers have a value in address_id that references to a value in dv_address. I.e, if in the table dv_address exists a record which address_id = 10, you can do:

UPDATE mg_customers SET address_id = 10 WHERE  address_id IS NULL;

Solution for 3.3#3

UPDATE dv_address SET address = 'NotKnown' WHERE address IS NULL;
ALTER TABLE dv_address ALTER COLUMN address SET NOT NULL;
ALTER TABLE dv_address ALTER COLUMN address SET DEFAULT 'NotKnown';

Repeat this for the column address2 if necessary

Upvotes: 1

Related Questions