Member Avatar for pecet

I started to learn SQL recently. I downloaded some kind of assignment, where the goal is to design small database. I have two tables listed below:

create table SEA (
	SEA_NAME varchar(50)constraint sea_prj_key primary key,
	AREA numeric(18, 0),
);

and:

create table NATION (
	CTRY_NAME varchar(50)constraint sea_prj_key primary key,
	AREA numeric(18, 0),
);

Since more than one country can lie by one sea, and also one coutry can lie by more than one sea, I've created the third table:

create table SEA_BORDER (
	CTRY_NAME varchar(50)references NATION(CTRY_NAME),
	SEA_NAME varchar(50)references SEA(SEA_NAME),
	BORDER numeric(18, 0)
);

The problem is that there can be a Country that doesn't lie by the sea, but there can't be sea, that doesn't create a border of any country. I have completly no idea how to solve that problem. Dont know if I should create another column/table or create some constraint. So I need a guide where I should look for solution.

  • 2 Contributors
  • 4 Replies
  • 105 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by pecet

Dani AI

Generated

— was right that there is no single simple declarative FK or CHECK in MySQL that says “every parent must have at least one child.” Standard SQL assertions and deferrable constraints that could do this are not available in MySQL. That said, there are practical ways to enforce the invariant inside the database instead of only in application code. Two common patterns follow: a strict stored-procedure + trigger + privilege approach (strong DB-level guarantee), and a simpler trigger-based approach that auto-creates the parent when a child is inserted (lighter-weight).

Example: block direct INSERTs into SEA and force use of a stored procedure that creates SEA and its SEA_BORDER rows in one transaction. The trigger checks a session flag and rejects manual inserts; the procedure sets the flag, inserts SEA and SEA_BORDER, then clears the flag.

DELIMITER //

CREATE TRIGGER block_direct_sea_insert
BEFORE INSERT ON SEA
FOR EACH ROW
BEGIN
  IF @allow_sea_insert IS NULL OR @allow_sea_insert = 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insert SEA only via create_sea_with_border()';
  END IF;
END;
//

CREATE PROCEDURE create_sea_with_border(
  IN p_sea_name VARCHAR(50),
  IN p_area DECIMAL(18,0),
  IN p_ctry_name VARCHAR(50),
  IN p_border DECIMAL(18,0)
)
SQL SECURITY DEFINER
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    SET @allow_sea_insert = 0;
    ROLLBACK;
    RESIGNAL;
  END;

  START TRANSACTION;
  SET @allow_sea_insert = 1;
  INSERT INTO SEA (SEA_NAME, AREA) VALUES (p_sea_name, p_area);
  SET @allow_sea_insert = 0;
  INSERT INTO SEA_BORDER (CTRY_NAME, SEA_NAME, BORDER)
    VALUES (p_ctry_name, p_sea_name, p_border);
  COMMIT;
END;
//

DELIMITER ;

Alternative (simpler): create SEA rows automatically when a SEA_BORDER is inserted. Useful if you prefer the sea to be created only when a border is recorded. Note that you must handle the SEA attributes (AREA) somehow (defaults, later updates, or include them in SEA_BORDER).

DELIMITER //

CREATE TRIGGER seed_sea_from_border
AFTER INSERT ON SEA_BORDER
FOR EACH ROW
BEGIN
  IF (SELECT COUNT(*) FROM SEA WHERE SEA_NAME = NEW.SEA_NAME) = 0 THEN
    INSERT INTO SEA (SEA_NAME, AREA) VALUES (NEW.SEA_NAME, NULL);
  END IF;
END;
//

DELIMITER ;

Notes and cautions: triggers in MySQL are not deferrable; design must allow creating parent and child in the right order or use the stored-proc pattern. To prevent bypassing the procedure, revoke direct INSERT on SEA for application users and grant EXECUTE on the procedure (use a DEFINER account that has the needed privileges). For most projects the stored-proc + privilege approach gives the strongest guarantee; for smaller setups application checks or the auto-seed trigger are usually enough.

Recommended Answers

All 4 Replies

Member Avatar for padtes

I feel current structure is enough, if a country has no sea-border, there will be no record in SEA_BORDER for that country. As far as database is concerned your current structure is perfect.

When finding countries with/without border with sea etc, you need to write correct SQLs (selecs).

Member Avatar for pecet

The problem is not about country with no sea-border. I'm concerned about second condition - how can I be sure that there won't be sea, that doesn't create a border with any country? (every sea has to create a border with one country at least).

Member Avatar for padtes

Database cannot really force this (nothing simple or standard way); your programming logic should take care of this by not allowing add sea not possible unless there is country, edit sea record invalid if no country relation is given by user.
As far as database is concerned, this is good.

Member Avatar for pecet

Ok, thx a lot for the answer.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.