Check if all table constraints like Primary key, Foreign key, etc. are implemented correctly.
Language: Java
Framework: JUnit using JDBC
1import org.junit.*;2import java.sql.*;34public class DatabaseTest {5 private Connection connection;6 private Statement statement;78 @Before9 public void connectToDatabase() throws SQLException {10 // Assuming there is a local MySQL server running on port 3306 with a database named "mydb"11 String url = "jdbc:mysql://localhost:3306/mydb";12 String username = "root";13 String password = "password";14 connection = DriverManager.getConnection(url, username, password);15 statement = connection.createStatement();16 }1718 @Test19 // Assuming there is a table named "employees" with columns "id" (primary key), "name", and "department_id" (foreign key referencing a "departments" table)20 public void testTableConstraints() throws SQLException {21 ResultSet result = statement.executeQuery("SHOW CREATE TABLE employees");22 result.next();23 String createTableSql = result.getString(2);2425 // Assuming primary key constraint is defined as "PRIMARY KEY (`id`)"26 Assert.assertTrue(createTableSql.contains("PRIMARY KEY (`id`)"));2728 // Assuming foreign key constraint is defined as "CONSTRAINT `fk_dept` FOREIGN KEY (`department_id`) REFERENCES `departments` (`id`)"29 Assert.assertTrue(createTableSql.contains("CONSTRAINT `fk_dept` FOREIGN KEY (`department_id`) REFERENCES `departments` (`id`)"));30 }3132 @After33 public void disconnectFromDatabase() throws SQLException {34 statement.close();35 connection.close();36 }37} 3839// To connect to a remote client with desired capabilities, you can uncomment and modify the following code:4041/*42import org.openqa.selenium.remote.*;43import java.net.*;4445public class RemoteDriver {46 public static void main(String[] args) {47 DesiredCapabilities capabilities = new DesiredCapabilities();48 capabilities.setBrowserName("chrome");49 capabilities.setVersion("XXX"); // Replace XXX with the desired version number50 capabilities.setCapability("platform", Platform.WINDOWS); // Replace with the desired platform5152 // Assuming there is a remote WebDriver URL available at "http://localhost:4444/wd/hub"53 try {54 RemoteWebDriver driver = new RemoteWebDriver(new URL("http://localhost:4444/wd/hub"), capabilities);55 // Add your test code here56 driver.quit();57 } catch (MalformedURLException e) {58 e.printStackTrace();59 }60 }61}62*/
Language: Python
Framework: Pytest
1#Assumptions: 2#1. The database connection details are already provided. 3#2. The necessary database schema has already been created including Primary keys and Foreign keys.45import pytest6import psycopg278#Database connection details9db_conn = psycopg2.connect(10 host="localhost", 11 database="test_db", 12 user="test_user", 13 password="test_pass"14)1516#Function to execute SQL queries17def execute_query(query):18 cursor = db_conn.cursor()19 cursor.execute(query)20 db_conn.commit()2122#Test case to check if primary key constraint is implemented correctly23def test_primary_key_constraint():24 #Assuming 'users' table has a column 'id' set as primary key25 query = "SELECT COUNT(*) FROM users GROUP BY id HAVING COUNT(*) > 1;" 26 execute_query(query)27 assert cursor.fetchone() == None2829#Test case to check if foreign key constraint is implemented correctly30def test_foreign_key_constraint():31 #Assuming 'orders' table has a foreign key constraint on 'user_id' column referencing 'users' table's 'id' column32 query = "SELECT COUNT(*) FROM orders WHERE user_id NOT IN (SELECT id FROM users);" 33 execute_query(query)34 assert cursor.fetchone() == None3536#Closing database connection37db_conn.close()3839#Code to connect to remote client with desired capabilities40#Assuming the remote client is hosted on sauce labs41from selenium import webdriver42from selenium.webdriver.common.desired_capabilities import DesiredCapabilities4344desired_caps = DesiredCapabilities.CHROME.copy()45desired_caps['version'] = 'latest'46desired_caps['platform'] = 'Windows 10'47desired_caps['name'] = 'Database constraints test'4849driver = webdriver.Remote(50 command_executor='https://<SAUCE LABS USERNAME>:<SAUCE LABS ACCESS KEY>@ondemand.saucelabs.com/wd/hub',51 desired_capabilities=desired_caps52)53#Include the necessary test case code to automate the test scenario on the web application through the driver.
Disclaimer: Following code snippets and related information have been sourced from GitHub and/or generated using AI code generation tools. LambdaTest takes no responsibility in the accuracy of the code and is not liable for any damages.
Leverage LambdaTest’s cloud-based platform to execute your automation tests in parallel and trim down your test execution time significantly. Your first 100 automation testing minutes are on us.
Test Intelligently and ship faster. Deliver unparalleled digital experiences for real world enterprises.
Start Free Testing